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 secur...@mariadb.org _______________________________________________ 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
