I have one table in InnoDB engine looks like that:
CREATE TABLE `sample` (
`id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
`unique_str` varchar(255) NOT NULL
);
ALTER TABLE `sample`
ADD UNIQUE `unique_str_index` (`unique_str`);
As you can see this table has a unique index on column "unique_str".
For example, I want to run two scripts with transactions one-by-one.
//First script, i use pdo for connection
$pdo->beginTransaction();
$pdo->exec("INSERT INTO sample (unique_str) VALUES('first')");
$pdo->exec("INSERT INTO sample (unique_str) VALUES('violate_str')");
sleep(50); //I'll start second script here
$pdo->commit();
I'll start the second script when the first script is sleeping within 50 seconds.
//Second script
$pdo->beginTransaction();
$pdo->exec("INSERT INTO sample (unique_str) VALUES('third')");
$pdo->exec("INSERT INTO sample (unique_str) VALUES('violate_str')");
$pdo->commit();
In second script PHP will be stuck on the very first string with "insert" command and wait while the transaction in the first script would be committed. When the first script waked up from sleep and committed the transaction, two new strings would be created in the table ('first' and 'violate_str') and the second script would start work again: the second script would go through two inserts, but on the second insert ('violate_str') would be generated an exception.
My question is "why PHP is waiting until the first transaction is completed and isn't working this time? Is MySQL blocking the second transaction? Why?". I think I have some misunderstanding of principles transactions work. I thought that transactions would be created one-by-one and the second transaction wouldn't be blocked.
Your second script is waiting for the second insert, not the first insert.
If I test your code with echo statements:
//First script, i use pdo for connection
$pdo->beginTransaction();
echo "begin done\n";
$pdo->exec("INSERT INTO sample (unique_str) VALUES('first')");
echo "insert('first') done\n";
$pdo->exec("INSERT INTO sample (unique_str) VALUES('violate_str')");
echo "insert('violate_str') done\n";
sleep(50); //I'll start second script here
echo "sleep done\n";
$pdo->commit();
echo "commit done\n";
//Second script
$pdo->beginTransaction();
echo "begin done\n";
$pdo->exec("INSERT INTO sample (unique_str) VALUES('third')");
echo "insert('third') done\n";
$pdo->exec("INSERT INTO sample (unique_str) VALUES('violate_str')");
echo "insert('violate_str') done\n";
$pdo->commit();
echo "commit done\n";
I see the output from the first script:
begin done
insert('first') done
insert('violate_str') done
Now it has started its sleep, so I run the second script and see this output immediately:
begin done
insert('third') done
It waits at this point. So we know it completed one insert without waiting. It is waiting on the second insert, the one that is in conflict with the insert of 'violate_str'. That's because the first script has already created that entry in the unique index, even though it hasn't committed it. It still holds a lock on that value in the index, so the second script has to wait for that lock.
Once the sleep ends, the first script commits, and outputs:
sleep done
commit done
Then the second script can acquire the lock, and attempt to insert the row. But unfortunately, the first script already inserted that value and committed it, so we get a duplicate key violation.
Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation:
1062 Duplicate entry 'violate_str' for key 'unique_str'
If the first script were to do rollback() instead of commit() then it releases its lock and undoes the insert. This allows the second script to finish and insert the contentious value freely.