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
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
