[Maria-discuss] Exclusive lock not working
I have a transaction that checks if a row exists with a particular value and if not, generates the data for that row and inserts it: -- schema CREATE TABLE test ( col INT, data TEXT, KEY (col) ); -- transaction START TRANSACTION; SELECT * FROM test WHERE col = 4 FOR UPDATE; -- If no results, generate data and insert INSERT INTO test SET col = 4, data = 'generated data goes here'; COMMIT; The problem I have is that if two copies of this transaction run concurrently (with the queries interleaved), they both pass the SELECT and deadlock on the INSERT. I would expect that the SELECT would acquire an exclusive lock on "col = 4" so only one transaction will proceed to insert, but the "FOR UPDATE" seems to behave as a shared lock instead. What is the correct way to write this transaction so only one copy proceeds to generate the "data" string and insert the row? (I originally posted this on Stack Overflow here https://stackoverflow.com/questions/42547629/insert-row-if-not-exists-withou... .) Thanks
Hi, Jesse! On Mar 17, Jesse Schalken wrote:
I have a transaction that checks if a row exists with a particular value and if not, generates the data for that row and inserts it:
-- schema
CREATE TABLE test ( col INT, data TEXT, KEY (col) );
-- transaction
START TRANSACTION; SELECT * FROM test WHERE col = 4 FOR UPDATE; -- If no results, generate data and insert INSERT INTO test SET col = 4, data = 'generated data goes here'; COMMIT;
The problem I have is that if two copies of this transaction run concurrently (with the queries interleaved), they both pass the SELECT and deadlock on the INSERT. I would expect that the SELECT would acquire an exclusive lock on "col = 4" so only one transaction will proceed to insert, but the "FOR UPDATE" seems to behave as a shared lock instead.
The statement SELECT ... FOR UPDATE works for you exactly as it says. It *selects* and locks it *for update*. Because the row doesn't exist, SELECT actually selects nothing. So it locks nothing for update. When the row exists, the first transaction returns a row and the second waits on SELECT.
What is the correct way to write this transaction so only one copy proceeds to generate the "data" string and insert the row?
Your goal is to "check if a row exists with a particular value and if not, generate the data for that row and insert it". Normally uniqueness checks are done inside a database. Make your KEY (col) a UNIQUE key, then when you insert the database will check whether a row exists and will insert the value if not. If you insist on doing it in the application, you can try user-level locks, GET_LOCK/RELEASE_LOCK functions. Like SELECT GET_LOCK(4, 10); -- If the result is 1 SELECT * FROM test WHERE col = 4; -- If no results, generate data and insert INSERT INTO test SET col = 4, data = 'generated data goes here'; SELECT RELEASE_LOCK(4); Regards, Sergei Chief Architect MariaDB and security@mariadb.org
Hi Sergei The statement SELECT ... FOR UPDATE works for you exactly as it says. It
*selects* and locks it *for update*. Because the row doesn't exist, SELECT actually selects nothing. So it locks nothing for update.
When the row exists, the first transaction returns a row and the second waits on SELECT.
I understand it's not acquiring an exclusive lock on a row because the row doesn't exist. However, if the SELECT didn't acquire any locks both transactions would insert the same row and finish. So the SELECT is obviously locking *something* to cause the INSERT to deadlock. My question is, why is that lock (whatever it is - a gap lock?) behaving like a shared lock rather than an exclusive lock as requested? I did find this line in the MySQL manual (here https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html): Gap locks in InnoDB are “purely inhibitive”, which means they only stop
other transactions from inserting to the gap. They do not prevent different transactions from taking gap locks on the same gap. Thus, a gap X-lock has the same effect as a gap S-lock.
Is this the reason for the behaviour?
Your goal is to "check if a row exists with a particular value and if not, generate the data for that row and insert it". Normally uniqueness checks are done inside a database. Make your KEY (col) a UNIQUE key, then when you insert the database will check whether a row exists and will insert the value if not.
I'm not particularly interested in the unique key solution because my real table can actually have multiple rows for that key. The uniqueness constraint only applies to this particular transaction/process, not the data itself. I don't want this process to insert multiple rows for the same `col` value, but others may. If you insist on doing it in the application, you can try user-level
locks, GET_LOCK/RELEASE_LOCK functions. Like
SELECT GET_LOCK(4, 10); -- If the result is 1 SELECT * FROM test WHERE col = 4; -- If no results, generate data and insert INSERT INTO test SET col = 4, data = 'generated data goes here'; SELECT RELEASE_LOCK(4);
This sounds like the best solution to me, if I can't otherwise acquire an exclusive lock on a particular value in a column. Thanks
participants (2)
-
Jesse Schalken -
Sergei Golubchik