Home >Database >Mysql Tutorial >Can Concurrent PHP Transactions in PDO MySQL Interfere with Each other?
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:
id | name | salary |
---|---|---|
1 | ana | 10000 |
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:
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!