Two transactions execute the code in database

01/30/2023 11:02 TonyFinch09#1

As a database developer, I'm having trouble with a problem of concurrency control while updating a [Only registered and activated users can see links. Click Here To Register...] in a relational database.
Consider the following code where I try to update the salary of an employee with id 123:

Code:

BEGIN TRANSACTION;
UPDATE employees SET salary = salary + 1000 WHERE id = 123;
COMMIT;

However, this code can result in a race condition, where two transactions execute the same code at the same time, leading to lost updates. For example, if two transactions execute the code simultaneously, the final salary of the employee may not be what I expect. Here's an example of what can happen:
Transaction 1:
BEGIN TRANSACTION;

Code:

UPDATE employees SET salary = salary + 1000 WHERE id = 123;
//At this point, Transaction 1 has not yet committed.

Transaction 2:

Code:

BEGIN TRANSACTION;
UPDATE employees SET salary = salary + 1000 WHERE id = 123;
COMMIT;

Transaction 1:

Code:

COMMIT;

In this scenario, the final salary of the employee will only be updated by Transaction 2 and the update made by Transaction 1 will be lost. This can lead to inconsistencies in the data and incorrect results in any reports or queries that rely on this data. To avoid this problem, I need to implement a proper concurrency control mechanism, such as pessimistic or optimistic locking, to ensure that only one transaction can update the tuple at a time.

01/30/2023 13:12 n�sty.#2

Quote:
Originally Posted by TonyFinch09
As a database developer, I'm having trouble with a problem of concurrency control while updating a [Only registered and activated users can see links. Click Here To Register...] in a relational database.
Consider the following code where I try to update the salary of an employee with id 123:
Code:
BEGIN TRANSACTION;
UPDATE employees SET salary = salary + 1000 WHERE id = 123;
COMMIT;
However, this code can result in a race condition, where two transactions execute the same code at the same time, leading to lost updates. For example, if two transactions execute the code simultaneously, the final salary of the employee may not be what I expect. Here's an example of what can happen:
Transaction 1:
BEGIN TRANSACTION;
Code:
UPDATE employees SET salary = salary + 1000 WHERE id = 123;
//At this point, Transaction 1 has not yet committed.
Transaction 2:
Code:
BEGIN TRANSACTION;
UPDATE employees SET salary = salary + 1000 WHERE id = 123;
COMMIT;
Transaction 1:
Code:
COMMIT;
In this scenario, the final salary of the employee will only be updated by Transaction 2 and the update made by Transaction 1 will be lost. This can lead to inconsistencies in the data and incorrect results in any reports or queries that rely on this data. To avoid this problem, I need to implement a proper concurrency control mechanism, such as pessimistic or optimistic locking, to ensure that only one transaction can update the tuple at a time.

Maybe im Dumb. Try boolean ?

var something = (function() {
var executed = false;
return function() {
if (!executed) {
executed = true;
// do something
}
};
})();

or make a counter, transaction++ and when you ending proof it = wrong dont execute

or collect the data and then enter them one by one at a specific point in time

Maybe this inspires you

Carrier Sense Multiple Access/Collision Detection

02/03/2023 12:36 kissein#3

- create cronjob to check for new transactions
- create seperate db table for an transactions pool
- create table for transaction pool cronjob
- check if cronjob is still active from previous check
- if active, exit process
- if inactive, get transactions that are not handled yet, process transactions
- remove processed transaction profils from pool
- if cronjob process is finished update cronjob table

something like this

Code:

$con = new mysqli($dbHost, $dbUser, $dbPass, $dbName);

try{
    //check if cronjob is running
    $cronResult = $con->query("SELECT `active` FROM transaction_pool_cronjob LIMIT 1");
    $active = $cronResult->fetch_object();
    if($active->active == 1){
        $con->close();
        die();
    }
    $con->query("UPDATE transaction_pool_cronjob SET `active` = 1");
    

    //query unhandled transactions and process them
    $sql = "SELECT `id`, `data`, `source` FROM transaction_pool WHERE `status` = 0";
    $dbResult = $con->query($sql);
    while($obj = $dbResult->fetch_object()){        
        $con->query("UPDATE transaction_pool SET `status` = 1 WHERE `id` = $obj->id");
        //do something here with data
        ...

        //remove processed transactions from pool
        $con->query("DELETE FROM transaction_pool WHERE id = $obj->id");
    }

}catch(\Exception $e){
    // do some error handling
} finally {
    //update cronjob table
    $con->query("UPDATE transaction_pool_cronjob SET `active` = 0");
    $con->close();
}

02/08/2023 09:52 MrBadGuy90#4

Why so complicated. You can use additional attribute "LastUpdated" to identify when the tuple was updated. Before updating the tuple you can check this attribute.

02/08/2023 17:45 kissein#5

scalability and reducation of complexity
sure if you have only 1 or 2 queries this will produce unnecessary overhead. if you have 1k+ queries per second its gets handy to have such a db structure.