How to know if an uncommitted transaction tries to insert a specific unique key into SQL

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.

Thanks!

Answer

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.

First window:

mysql> START TRANSACTION;
mysql> INSERT INTO mytable SET c = 42;

Then don’t commit yet.

Second window:

mysql> INSERT INTO mytable SET c = 42;

Notice that it hangs at this point, waiting for the lock.

First window:

mysql> commit;

Second window finally returns:

ERROR 1062 (23000): Duplicate entry '42' for key 'PRIMARY'