Hi! It depends on the isolation level whether locking reads set next-key locks (= lock the record AND the gap before it), or only lock the record itself.
http://www.innodb.com/ibman.php#Set.transaction: " READ UNCOMMITTED This is also called dirty read: non-locking SELECTs are performed so that we do not look at a possible earlier version of a record; thus they are not consistent reads under this isolation level; otherwise this level works like READ COMMITTED. READ COMMITTED Somewhat Oracle-like isolation level. All SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE statements only lock the index records, not the gaps before them, and thus allow free inserting of new records next to locked records. UPDATE and DELETE which use a unique index with a unique search condition, only lock the index record found, not the gap before it. But still in range type UPDATE and DELETE InnoDB must set next-key or gap locks and block insertions by other users to the gaps covered by the range. This is necessary since phantom rows have to be blocked for MySQL replication and recovery to work. Consistent reads behave like in Oracle: each consistent read, even within the same transaction, sets and reads its own fresh snapshot. REPEATABLE READ This is the default isolation level of InnoDB. SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, and DELETE, which use a unique index with a unique search condition, only lock the index record found, not the gap before it. Otherwise these operations employ next-key locking, locking the index range scanned with next-key or gap locks, and block new insertions by other users. In consistent reads there is an important difference from the previous isolation level: in this level all consistent reads within the same transaction read the same snapshot established by the first read. This convention means that if you issue several plain SELECTs within the same transaction, these SELECTs are consistent also with respect to each other. SERIALIZABLE This level is like the previous one, but all plain SELECTs are implicitly converted to SELECT ... LOCK IN SHARE MODE. " http://www.innodb.com/ibman.php#Locks.set.by.statements: " SELECT ... FROM ... LOCK IN SHARE MODE sets shared locks on all index records the read encounters. SELECT ... FROM ... FOR UPDATE sets exclusive locks on all index records the read encounters. " Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL support from http://www.mysql.com/support/index.html .................... List:MySQL General Discussion« Previous MessageNext Message » From:Ingolf HenriciDate:January 30 2004 12:52pm Subject:Key locks on InnoDB Hi, got a problem with the key locks, using InnoDB: The mysql.com Manual reads: SELECT ... FROM ... LOCK IN SHARE MODE: sets shared next-key locks on all index records the read encounters. SELECT ... FROM ... FOR UPDATE: sets exclusive next-key locks on all index records the read encounters. The Innodb-Manual reads: SELECT ... FROM ... LOCK IN SHARE MODE sets shared locks on all index records the read encounters. SELECT ... FROM ... FOR UPDATE sets exclusive locks on all index records the read encounters. What locks are used for these statements?? shared locks or shared next-key-locks thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
