I’m writing a programm which inserts data to a MariaDB-Server and can be used by different people on the same time. The transactions take some time, so the following problem might occur: Person A starts a transaction with primary key “c” and while the transaction is still uncommitted, Person B wants to insert data with the same primary key “c”. How can I prevent that B can start its transaction with a primary key that A already uses in its uncommitted transaction?
I use MariaDB as database and InnoDB as Engine.
I’ve checked the Isolation-Levels but couldn’t figure how to use them to solve my Problem.
It has nothing to do with transaction isolation levels. It’s about locking.
Any insert/update/delete to a specific entry in an index locks that entry. Locks are granted first-come, first-serve. The next session that tries to do an insert/update/delete to the same index entry will be blocked.
You can demo this yourself. Open two MySQL client windows side by side.
mysql> START TRANSACTION; mysql> INSERT INTO mytable SET c = 42;
Then don’t commit yet.
mysql> INSERT INTO mytable SET c = 42;
Notice that it hangs at this point, waiting for the lock.
Second window finally returns:
ERROR 1062 (23000): Duplicate entry '42' for key 'PRIMARY'