If id is not indexed or has a nonunique index, the statement does lock the preceding gap. (This does not include the case that the search condition includes only some columns of a multiple-column unique index in that case, gap locking does occur.) For example, if the idcolumn has a unique index, the following statement uses only an index-record lock for the row having id value 100 and it does not matter whether other sessions insert rows in the preceding gap: SELECT * FROM child WHERE id = 100 Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. Gap locks are part of the tradeoff between performance and concurrency, and are used in some transaction isolation levels and not others. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.Ī gap might span a single index value, multiple index values, or even be empty. For more information about this locking mechanismĪ gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. The two-phase process allows the lock requests to be resolved in order, without blocking locks and corresponding operations that are compatible. Conversely, the first transaction to acquire an intention shared (IS) lock on a table prevents other transactions from acquiring any X locks on the table. Different transactions can acquire different kinds of intention locks on the same table, but the first transaction to acquire an intention exclusive (IX) lock on a table prevents other transactions from acquiring any S or X locks on the table.

exclusive ( X) lockĪ kind of lock that applies to the table, used to indicate the kind of lock the transaction intends to acquire on rows in the table. The default InnoDB isolation level, REPEATABLE READ, enables higher concurrency by allowing transactions to read rows that have exclusive locks, a technique known as consistent read. Depending on the transaction isolation level, this kind of lock might block other transactions from writing to the same row, or might also block other transactions from reading the same row. shared ( S) lockĪ kind of lock that prevents any other transaction from locking the same row. A kind of lock that allows other transactions to read the locked object, and to also acquire other shared locks on it, but not to write to it.

Select for update mysql deadlock