php - Understanding pdo mysql transactions

Question

The PHP Documentation says:

If you've never encountered transactions before, they offer 4 major features: Atomicity, Consistency, Isolation and Durability (ACID). In layman's terms, any work carried out in a transaction, even if it is carried out in stages, is guaranteed to be applied to the database safely, and without interference from other connections, when it is committed.

QUESTION:

Does this mean that I can have two separate php scripts running transactions simultaneously without them interfering with one another?

---

ELABORATING ON WHAT I MEAN BY "INTERFERING":

Imagine we have the following employees table:

 __________________________
|  id  |  name  |  salary  |
|------+--------+----------|
|  1   |  ana   |   10000  |
|------+--------+----------|

If I have two scripts with similar/same code and they run at the exact same time:

script1.php and script2.php (both have the same code):

$conn->beginTransaction();

$stmt = $conn->prepare("SELECT * FROM employees WHERE name = ?");
$stmt->execute(['ana']);
$row = $stmt->fetch(PDO::FETCH_ASSOC);

$salary = $row['salary'];
$salary = $salary + 1000;//increasing salary

$stmt = $conn->prepare("UPDATE employees SET salary = {$salary} WHERE name = ?");
$stmt->execute(['ana']);

$conn->commit(); 

and assuming the sequence of events is as follows:

• script1.php selects data

• script2.php selects data

• script1.php updates data

• script2.php updates data

• script1.php commit() happens

• script2.php commit() happens

What would the resulting salary of ana be in this case?

• Would it be 11000? And would this then mean that 1 transaction will overlap the other because the information was obtained before either commit happened?

• Would it be 12000? And would this then mean that regardless of the order in which data was updated and selected, the commit() function forced these to happen individually?

Please feel free to elaborate as much as you want on how transactions and separate scripts can interfere (or don't interfere) with one another.

Answer 1

You are not going to find the answer in php documentation because this has nothing to do with php or pdo.

Innodb table engine in mysql offers 4 so-called isolation levels in line with the sql standard. The isolation levels in conjunction with blocking / non-blocking reads will determine the result of the above example. You need to understand the implications of the various isolation levels and choose the appropriate one for your needs.

To sum up: if you use serialisable isolation level with autocommit turned off, then the result will be 12000. In all other isolation levels and serialisable with autocommit enabled the result will be 11000. If you start using locking reads, then the result could be 12000 under all isolation levels.