Dowemo
0 0 0 0

There are many articles on the lock mechanism, there are many articles that aren't understood or problems, recently studied a long time, combined with. If there's no right place, please leave a message at any time.

The type of lock that's in progress

1. Table lock ( mysql provided with the storage engine ).

2. Row locks ( hsm storage engine implementation ).

The type of lock implemented within the innodb.

1. Record lock.

A row lock, which records the lock lock, isn't a specific data record.

2. Clearance locks.

Lock the lock of the index record clearance to ensure that the clearance of the index records is unchanged, and the gap locks are Transaction isolation level is a or above level of.

For example: create table t1 ( id int, v1 int, v2 int, primary key ( id ), key _ idx_v1 _ hello _ ) engine = hsm;

Lines of data lines:

idV1V2
110
231
342
553
774
1095

Clearance locks are generally used for indexes.

The index interval of v1 in the above data table

( -∞, 1 )

( 1, 3 )

( 3, 4 )

( 4, 5 )

( 5, 7 )

( 7, 9 )

( 9, ∞ )

If the data row of v1 = 5 is updated, then the idx is added at the index record, which locks the interval prior to 5, the locked interval is ( 4, 5 ), and ( 5, HSPA ), and the primary key of the data row in the,, and locks the row to lock the record on the record.

3. lock.

The combination of recording lock and clearance locks, when updating a non unique index record, will add a lock. If the update record is empty, you cannot add a record lock, leaving only the clearance lock. Multiple update statements may result in duplicate index intervals in different transactions, causing insertion failures.

Examples ( tra & action isolation levels are repeatable, and the primary look at the lock is the combination of lock and clearance locks ).

Transaction 1Transaction 2
A begin;A begin;
Update t1 set v2 = 1 where v1 = 6; ( the sql lock is added to the sql, but the record for v1 = 6 doesn't exist; the lock is a record lock and a clearance lock, which will lock the index interval ( 5, 7 ) ).Update t1 set v2 = 2 where v1 = 7; ( sql is also a lock, the record for v1 = 7 exists, and the lock is composed of a record lock and an interval lock, which is a primary key, which is a id = 7 record row, and a gap lock is used, which locks the _ 9 index interval ( 5 ).
Insert into t1 set id = 8, v1 = 6, v2 = 2; ( v1 = 6 is locked in its own idx ( 5, fso );Insert into t1 set id = 9, v1 = 8, v2 = 2; ( v1 = 8 is locked in its own idx ( 5, fso );
Lock waitInsert success
Insert successRollback;
Rollback;

As you can see, an index interval can be locked by multiple clearance locks, which will cause deadlock when updating incorrectly.

In transaction 2, if" Insert into t1 set id = 9, v1 = 8, v2 = 2;" This statement is replaced" Insert into t1 set id = 9, v1 = 6, v2 = 2; 'will cause a deadlock because two clearance locks are locked ( 5, 7 ) this interval.

At the same time in transaction 2, a record lock has been added to the primary key index of id = 7, any rows that attempt to update id = 7 in other tra actio, such as transaction 1, are suspended until the transaction 2 commits or rolls back.

selection

Execute an update class statement, like select. For update, update, delete statement.

1. If the update condition doesn't have an index, such as executing"select * from t1 where v2 = 2 for update", then the update operation uses table locks. Multiple update sql statements execute concurrently in different transactions; transactions that take table locks will suspend other transactions until the current tra.

Reason for using table locks:

Because the updated data isn't indexed, mysql can only do a sweep operation, and to prevent any other update operations, it'll rise to a table lock.

2. If the update condition is an index field but not a unique index ( including the primary key ), for example, execute"select * from t1 where v1 = 1 for update", then the update uses the lock.

The cause of the lock:

A ) to ensure that an exclusive record lock is added to the records of the qualified records. The primary key index for the current index and the records that satisfy these criteria are locked B ) also ensure that the updated index record interval cannot be inserted into new data.

3. Use record locks if the update condition field is unique.

Hsm finds the primary key index of the record according to the unique index. Primary key index and unique index Plus a record lock.

Description: innodb index structure

Terracotta supports clustered indexes, but clustered indexes can only be primary key indexes, and each table can have only one clustered index, called cluster index. The primary key index is clustered index, where the leaf node of the primary key index holds the physical address of the record, according to the key. indexes are stored on the leaf node of the b tree index not the physical address of the record, but the physical address of the primary key index.

A lock is used when a index is added to a lock ( for example, updating a record that corresponds to a key index ). A record lock is fi & t added to the primary key index of a satisfied index and a index that meets the criteria A index is then added to a index, and the index interval corresponding to the current non unique index is added to a gap lock, which prevents any insert operations in that interval.

lock demo

Note: when a lock is added, the records at both ends aren't locked, then the records at both ends can be updated, but when the record is updated, the

Transaction 3Transaction 4
A begin;A begin;
Select * from t1 where v1 = 5 for update; ( plus a clearance lock, the index interval of the v1 is locked ( 4, 7 ), and row locks are added to the primary key index of the required records.

Update class operation

Update t1 set v2 = 2 where v1 = 4; ( ok, not suspended, clearance lock lock lock clearance, and this update sql doesn't affect the range of clearance lock control in the idx ( 4, 7 )

Update t1 set v2 = 2 where v1 = 7; ( ok, not suspended )

Update t1 set v2 = 2 where v1 = 6; ( ok, not suspended )

Update t1 set v1 = 1 where v1 = 4; ( ok, not suspended, updated index )

Update t1 set v1 = 5 where v1 = 4; ( due to 4, 7 interval is blocked, this operation will be suspended )

Update t1 set v1 = 8 where v1 = 7; ( ok, not suspended, updated index )

Update t1 set v1 = 1 where v1 = 9; ( ok, not suspended, because the value of the condition and destination index isn't in the blocked interval )

Update t1 set v1 = 5 where v1 = 7; ( Because 4, 7 intervals are blocked, this operation will be suspended. )

Update t1 set v1 = 2 where v1 = 7 ( this operation will be suspended )

By the above two groups of sql, the gap locks are 4, 7, and when you update the records at both ends, if you don't change the value of the interval, the update operation won't be suspended by the clearance lock. If the index value at both ends of the clearance lock interval is updated and the interval after the update index contains the currently locked interval. If the index is updated, the update operation will be suspended if it cannot contain locked intervals.

Insert class operation

Insert into t1 set id = 11, v1 = 5, v2 = 5; ( pending, transaction 3 idx mb gap is ( 4, fso ), I & ert v1 = 5 must be suspended

Insert into t1 set id = 11, v1 = 8, v2 = 8 ( ok, v1 isn't blocking interval );

Mainly look at the insertion at both ends of the blocking interval.

Insert into t1 set id = 4, v1 = 4, v2 = 2; ( v1 = 4 is the left value of blocking interval, which will be suspended )

Insert into t1 set id = 0, v1 = 4, v2 = 2; ( this operation ok can be executed )

Insert into t1 set id = 6, v1 = 7, v2 = 2; ( v1 = 7 is the right value for blocking interval, which will be suspended )

Insert into t1 set id = 8, v1 = 7, v2 = 2; ( this operation ok can be executed )

When inserting values at the end of the bounding area, you need to see whether the primary key of the value you want to insert is the scope of the primary key for the

Explanation:

idV1V2
342
774

When the left boundary value is inserted, the value of the primary key id requires that the value of the primary key needs to be less than id = 3, when there are multiple records in the = 4 left value in the database, the inserted id is less than the largest id.

When you insert the right bound value, I & ert v1 = 7 requires that the primary key id value greater than the range of id = 7, and when the = 7 right value is multiple records in the database, the I & erted id is greater than the smallest id in which it's.

Rollback;

Rollback;

Locking read ( Select. For update. or Select. Lock in share mode. ), UPDATEAnd as well as DELETEA statement usually locks on all the indexes he scanned, ignoring that part of the where statement that doesn't use the index.
For example:

CREATE TABLE `test`(`id`int(11) NOT NULL DEFAULT '0',`name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8select*from test where id >3and name <'A'for update;

This sql statement will break all. Id> 3 The record is locked, not. Id> 3 and name <& a ' Lock, because name doesn't have an index.

If an sql scans through a secondary index and a lock is set on the secondary index, innodb will also add a lock on the corresponding cluster index record.

If an sql statement cannot be indexed Locking read, UPDATE, DELETEThen mysql scans the entire table, each row in the table will be locked ( at the rc level ). Semi-consistent read It's possible to release records ahead of time, in the rr level. Innodb_locks_unsafe_for_binlog For 1 to open Semi-consistent read In some scenarios, the lock won't be released immediately. For example, unionThe query generates a temporary table that causes the row record of the temporary table and the row record of the original table to be lost, and can only wait.

Sql analysis

1. Select. From. Is a snapshot read by reading a snapshot of the database, no locks are added, unless the isolation level is set to SERIALIZABLEIn. SERIALIZABLEUnder isolation level, if the index isn't unique, then a share is added to the corresponding record. next keyLock. If it's unique, only need to be placed on the corresponding record. index record lock.

2. Select. From. Lock in share mode. Statements are shared on index records of all index scan ranges. next keyLock. If it's unique, only need to be placed on the corresponding record. index record lock.

3. Select. From. For update. Statement with an exclusive next key lock on index records for all index scans. If it's a unique index, just add it to the corresponding record index record lockThis will block other sessions. Select. From. Lock in share mode. Read the same record, but the snapshot reads the lock on the record.

4. Update. Where. Statement with an exclusive entry on index records for all index scans next keyLock. If it's unique, only need to be placed on the corresponding record. index record lock.

When when UPDATEAn implicit lock is added to the corresponding secondary index when the primary key record is modified. When duplicate key detection is performed, a shared lock is added on its secondary index before the new secondary index record is inserted.

5. Delete from. Where. Statement with an exclusive entry on index records for all index scans next keyLock. If it's unique, only need to be placed on the corresponding record. index record lock.

6. INSERTThe statement will attach an exclusive lock to the inserted record, which is a Index-record lock It isn't. Next-key Lock, so don't. gapLock, he won't prevent other sessions from being recorded before. gapInsert records.

Before I insert a record, it'll be called insert intention gapIt's. gapLock. This. insert intention gapI mean, he's the intent. index gapInsert records, if other sessions are in this index gapThe insertion location isn't the same, so no wait is required. Assuming that there's an index record 4 and 7, session a is inserted into the record 5, session b to insert the record 6, each session needs to lock the 4 gapBut they don't block each other because the insertion position is different.

If duplicate key errors occur, a shared lock is added to duplicate keys. If the session 1 I & erts a record, it isn't committed, and he's an exclusive lock, session 2, and session 3 trying to insert the duplicate record, and then they'll be blocked, the session 2, and the session 3 attempt to attach a shared lock to the record. A deadlock occurs if the session 1 is rolled back.

Example:
Table structure:
Create table t1 ( I int, primary key ( I ) ) engine = hsm;

Session 1:

START TRANSACTION;INSERT INTO t1 VALUES(1);

Session 2:

START TRANSACTION;INSERT INTO t1 VALUES(1);

Session 3:

START TRANSACTION;INSERT INTO t1 VALUES(1);

Session 1:

ROLLBACK;

Why do you've a deadlock. When the session 1 is rolled back, the rows on the record are released, and the session 2 and session 3 have shared locks. Then the session 2 and session 3 want an exclusive lock, and a deadlock occurs.

And a similar example.

Session 1:

START TRANSACTION;DELETE FROM t1 WHERE i =1;

Session 2:

START TRANSACTION;INSERT INTO t1 VALUES(1);

Session 3:

START TRANSACTION;INSERT INTO t1 VALUES(1);

Session 1:

COMMIT;

Session 1 has an exclusive lock on this record, session 2, and session 3 have a duplicate record, so the shared lock is applied. After the session 1 is committed, session 1 releases a row lock, and the following session 2 session 3 gets a shared lock, which has a deadlock, because the session 2.

7. Insert. On duplicate key update. And normal. INSERTDifferent. If you encounter duplicate key values. Insert. On duplicate key update. Put him on the record. Next-key Lock.

8. REPLACEAnd when there's no duplicate key value, INSERTIt's the same, if you hit a duplicate key, you'll be on the record. Next-key Lock.

9. Insert into t select. From s where. Statements are added to each record in the insert t table index record lockIf the isolation level is READ COMMITTEDOr enabled. Innodb_locks_unsafe_for_binlog And the transaction isolation level isn't SERIALIZABLE will read table s ( no locks ) through snapshots. Otherwise, bochs will share the records on s records. Next-key Lock.

Create table. Select. And and Insert into t select. From s where. Similarly, share it on s. Next-key Lock or snapshot read ( no locks )

10. Replace into t select. From s where. And and Update t. Where in ( select. From s. ). In the selectSection will be shared on table s. Next-key Lock.

11. When you encounter a table, has a row lock on the end of the index. Auto-inc table lock . Auto-inc table lock Is released after the statement execution is completed, not the end of the transaction. If if Auto-inc table lock By a session, the other session won't be able to insert data in the table. Hsm can the amount of increments that sql requires, without needing to apply to the lock, for more settings. Innodb_autoinc_lock_mode .

12. If a foreign key co & TRAI not for a table is enabled, any I & ert, update, and delete on that table will require sharing. Record-level locks To check whether co traints are met. If the constraint check fails, hsm also adds shared. Record-level locks .

13. lock tablesIt's used to add locks, but the server layer of mysql adds this lock. When when Innodb_table_locks = 1 ( the default ) And as well as Autocommit = 0 Innodb can perceive table locks at the same time, while the server layer knows innodb. Row-level locks Otherwise, hsm won't be able to detect a deadlock, while server cannot determine whether there are locks, resulting in a table lock when other sessions occupy locks.

Lock test point.

1. When using. beginWhen a transaction is opened, subsequent queries aren't captured. beginThe time point snapshot of the command, and beginsnapshot of the first query after the command.

CREATE TABLE T2 (id INT,name varchar(10),PRIMARY KEY (id)) ENGINE =InnoDB;INSERT INTO T2 VALUES(1,'zhangsan'),(2,'lisi');SESS1: SESS2BEGIN;BEGIN; INSERT INTO T2 values(3,'wangwu'); COMMIT;SELECT * FROM T2;+----+----------+| id | name |+----+----------+|1| zhangsan ||2| lisi ||3| wangwu |+----+----------+

In the case of repeatable reads, why will this happen.

Because sess1 doesn't open a query after begin, it causes sess1. Select * from t2 A snapshot of the query is a snapshot to execute the sql BEGINA snapshot of that time point, and at this point sess2 has been committed.

2. Let's look at an example:

CREATE TABLE T3 (id INT,name varchar(10),PRIMARY KEY (id)) ENGINE =InnoDB;INSERT INTO T3 VALUES(1,'a'),(2,'b'),(3,'c');SESS1: SESS2BEGIN;SELECT * FROM T3;+----+------+| id | name |+----+------+|1| a ||2| b ||3| c |+----+------+BEGIN; INSERT INTO T3 values(4,'a'); COMMIT;SELECT * FROM T3;+----+------+| id | name |+----+------+|1| a ||2| b ||3| c |+----+------+UPDATE T3 SET NAME='aa'where name ='a';Query OK,2 rows affected (0.00 sec)Rows matched:2Changed:2Warnings:0SELECT * FROM T3;+----+------+| id | name |+----+------+|1| aa ||2| b ||3| c ||4| aa |+----+------+ SELECT * FROM T3; 
 +----+------+| id | name |+----+------+|1| a ||2| b ||3| c ||4| a |+----+------+

In the case of repeatable read, why the sess1 first opens the transaction, and then updates the data submitted by the session 2 later? then the subsequent select? Let's see the official explanation.

A means that uses multi-versioning to.

Innodb uses MVCC for snapshot reading by default. The query can only view data that's committed to the transaction changes previously submitted, and the data submitted, or the transaction data that isn't committed, isn't seen. But there's an exception here. The same transaction can see changes in the same transaction. So when sess1 is done, UPDATEAt the time, the current read is to read all the data that has been committed, equivalent to reading. Select * from t3 where where name = 'a 'for update; Result set, and then proceed. UPDATEFollowed by Select * from t3 where where name = 'a ' What you see is current. UPDATEAfter that.





Copyright © 2011 Dowemo All rights reserved.    Creative Commons   AboutUs