I'm trying to get rid of write skew and tried to use serializable isolation level, but I'm getting deadlock instead. I found out that serializable isolation level can cause deadlock because of this:
This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE
So, I tried to use REPEATABLE READ like this (there is no row with id "some_id"):
-- connection 1:
START TRANSACTION;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
select * from some_table where id="some_id" for update;
-- connection 2:
START TRANSACTION;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
select * from some_table where id="some_id" for update;
insert into some_table values("some_id");
As a result I get this message in connection 2: Lock wait timeout exceeded; try restarting transaction.
The FOR UPDATE clause of SELECT applies only when autocommit is set to 0 or the SELECT is enclosed in a transaction. A lock is acquired on the rows, and other transactions are prevented from writing the rows, acquire locks, and from reading them (unless their isolation level is READ UNCOMMITTED).
But looks like it doesn't prevent other transactions from acquiring locks or reading rows.
What am I doing wrong?
This post answers my question: How do I lock on an InnoDB row that doesn't exist yet?
While the answer above is true in that a SELECT ... FOR UPDATE will prevent concurrent sessions / transactions from inserting the same record, that is not the full truth. I am currently fighting with the same problem and have come to the conclusion that the SELECT ... FOR UPDATE is nearly useless in that situation for the following reason:
A concurrent transaction / session can also do a SELECT ... FOR UPDATE on the very same record / index value, and MySQL will happily accept that immediately (non-blocking) and without throwing errors. Of course, as soon as the other session has done that, your session as well can't insert the record any more. Nor your nor the other session / transaction get any information about the situation and think they can safely insert the record until they actually try to do so. Trying to insert then either leads to a deadlock or to a duplicate key error, depending on circumstances.
In other words, SELECT ... FOR UPDATE prevents other sessions from inserting the respective record(s), BUT even if you do a SELECT ... FOR UPDATE and the respective record is not found, chances are that you can't actually insert that record. IMHO, that renders the "first query, then insert" method useless.
The cause of the problem is that MySQL does not offer any method to really lock non-existent records. Two concurrent sessions / transactions can lock non-existent records "FOR UPDATE" at the same time, a thing which really should not be possible and which makes development significantly more difficult.
The only way to work around this seems to be using semaphore tables or locking the whole table when inserting. Please refer to the MySQL documentation for further reference on locking whole tables or using semaphore tables.
Just my 2 cents ...
I didn't find the question first, so I won't delete this question (I know, in fact, it's a duplicate) to make searching easier for others.
As a result I created unique index, and I used it with repeatable read isolation level (without "FOR UPDATE"). It allowed me to detect concurrent inserts and handle this situation in my code (I decided to return an error in this case).
Related resources I've found while trying to answer this question:
https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlock-example.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html
https://mariadb.com/kb/en/set-transaction/
https://mariadb.com/kb/en/lock-in-share-mode/
https://mariadb.com/kb/en/for-update/
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks.html
Deadlock in transaction with isolation level serializable
NHibernate lock database table to avoid insert "duplicates"
How to avoid MySQL 'Deadlock found when trying to get lock; try restarting transaction'