How do I lock on an InnoDB row that doesn’t exist yet?

by

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 …

More Related Contents:

  1. MyISAM versus InnoDB [closed]
  2. How do I see all foreign keys to a table or column?
  3. How do I quickly rename a MySQL database (change schema name)?
  4. Is there a REAL performance difference between INT and VARCHAR primary keys?
  5. MySQL AUTO_INCREMENT does not ROLLBACK
  6. 1114 (HY000): The table is full
  7. TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes
  8. Howto: Clean a mysql InnoDB storage engine?
  9. Restore table structure from frm and ibd files
  10. MySQL InnoDB not releasing disk space after deleting data rows from table
  11. What’s the difference between MyISAM and InnoDB? [duplicate]
  12. Why is MySQL InnoDB insert so slow?
  13. MySql: MyISAM vs. Inno DB! [closed]
  14. Prevent auto increment on MySQL duplicate insert
  15. UUID performance in MySQL?
  16. Bogus foreign key constraint fail
  17. Detecting locked tables (locked by LOCK TABLE)
  18. Is there any performance gain in indexing a boolean field?
  19. Why does MySQL autoincrement increase on failed inserts?
  20. MySQL InnoDB: autoincrement non-primary key
  21. How do I rename a MySQL database (change schema name)?
  22. MySQL Integer vs DateTime index
  23. Database corruption with MariaDB : Table doesn’t exist in engine
  24. Defining Composite Key with Auto Increment in MySQL
  25. MySQL DROP all tables, ignoring foreign keys
  26. “Unknown column in ‘field list'”, but column does exist
  27. MySQL ‘select for update’ behaviour
  28. Should I always prefer MySQL InnoDB over MyISAM?
  29. How can I rebuild indexes and update stats in MySQL innoDB?
  30. Optimistic locking in MySQL

Leave a Comment