Understanding Transaction Isolation in PDO MySQL

The PHP documentation highlights the benefits of transactions, emphasizing their ability to ensure atomic, consistent, isolated, and durable operations within a database.

Question:

Does this imply that separate PHP scripts running transactions concurrently can operate without interference?

Elaboration on Interference:

Consider the table employees with the following data:

Two scripts with similar code execute simultaneously:

script1.php and script2.php:

<code class="php">$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();</code>

The question arises regarding the resulting salary of 'ana' under different execution scenarios.

Answer:

The outcome depends on the isolation level configured for the InnoDB table engine in MySQL. Four isolation levels exist according to the SQL standard:

• READ UNCOMMITTED: Allows reading uncommitted data. Concurrent transactions can interfere with each other.
• READ COMMITTED: Allows reading only data committed by other transactions. Concurrent transactions typically don't interfere.
• REPEATABLE READ: Prevents other transactions from modifying data read by the current transaction. Concurrent transactions can still interfere if they attempt to modify the same data.
• SERIALIZABLE: Ensures that transactions execute in a serial manner, preventing any interference between concurrent transactions.

In the given scenario, using the SERIALIZABLE isolation level and disabling autocommit will result in a salary of 12000 because each transaction is isolated and executed sequentially.

However, with any other isolation level or SERIALIZABLE with autocommit enabled, the result will be 11000. This is because transactions can overlap in these cases, allowing for potential interference. Using locking reads can also affect the outcome, potentially leading to a salary of 12000 under all isolation levels.

Therefore, the specific isolation level and configuration settings employed will determine the interference or lack thereof between concurrent transactions in PHP scripts.

The above is the detailed content of Can Concurrent PHP Transactions in PDO MySQL Interfere with Each other?. For more information, please follow other related articles on the PHP Chinese website!