For reprint, please indicate the source: http: blogcsdnnetguoyjoearticledetails48805847 ********************************** MySQL lock management mechanism **********************************
MySQL lock management mechanism
**********************************
MySQL server-level locks
? Table-level locking)
? Page-level locking)
? Row-level locking)
--------------------------------------------------------------------
I. table-level lock: the entire table is locked directly. during the lock period, other processes cannot perform write operations on the table. If you use a write lock, other processes cannot read the lock.
Table-level locking for MyISAM tables
MyISAM table lock
? Read lock: lock table GYJ_T1 READ. it is READ-only and cannot be written. other threads are still readable and cannot be written. Read lock can be submitted by multiple threads.
? Write lock: lock table GYJ_T1 [LOW_PRIORITY] WRITE, which can be read and written by itself. other threads cannot read or WRITE at all.
? Release Lock, UNLOCK TABLES
? SELECT auto-read lock
? Other DML and DDL automatic write locks
Innodb row-level Lock upgrades table-level locks.
1. Innodb auto-inc lock
When InnoDB processes tables with the auto increment field, it uses a special table lock-AUTO-INC.
Simply put, innodb will save a counter in the memory to record the auto_increment value. when inserting data, it will use a table lock to lock the counter,
Until the insertion ends. One insert statement is not a problem, but if it is highly concurrent, it will cause SQL blocking.
There are two solutions:
A) you do not need the auto increment field to maintain the primary key generation. In this method, it is very important to select a primary key generation policy. The problem of simplicity and efficiency should be considered comprehensively. Assume that uuid is used,
Although simple, the efficiency of the primary key of the table is very low (the primary key of innodb is a special index, and other indexes will reference the primary key. for details, see the mysql documentation)
B) modify innodb_autoinc_lock_mode.
Innodb_autoinc_lock_mode = 0 ("traditional" lock mode: all use table locks)
Innodb_autoinc_lock_mode = 1 ("consecutive" lock mode: a new method is used to predict the number of rows, and a table lock is used when the table lock is unavailable)
Innodb_autoinc_lock_mode = 2 ("interleaved" lock mode: all new methods are used, which is not secure and not suitable for replication)
2. Innodb full table update and full index update
3. Innodb uses the SR transaction isolation level
2. page-level locks: table-level locks are fast, but have many conflicts and have fewer row-level conflicts, but are slow. Therefore, the compromised page level is used to lock a group of adjacent records at a time.
Page-level locking of BDB tables. BDB does not exist now. it is a very old database and only available at. it is now deleted from the database.
3. row-level lock: only lock the specified record, so that other processes can still operate on other records in the same table.
Row-level locking for InnoDB tables
Innodb row lock method
1. record lock (row/record lock)
2. gap lock)
3. next-key lock (record lock + gap lock)
InnoDB locks indexes to implement row locks.
InnoDB has several locks:
? S-LOCKING allows a transaction to read a row to prevent other transactions from obtaining exclusive locks for the same dataset.
? Exclusive locks (X-LOCKING) allow transactions that obtain exclusive locks to update data, and prevent other transactions from obtaining shared read locks and exclusive locks for the same dataset.
InnoDB also has two unique locks:
? Intention to share the lock (IS). The transaction intends to add a row Share Lock to the data row. The transaction must first obtain the IS lock of the table before adding a Share Lock to the data row.
? Intention exclusive lock (IX): The transaction intends to apply a row exclusive lock to the data row. The transaction must first obtain the IX lock of the table before applying an exclusive lock to the data row.
Note:
(1) when the index condition query is not passed, InnoDB uses table locks (by default, all rows in the table are locked, which is equivalent to table-level locks,
The exception is RC + innodb_locks_unsafe_for_binlog combination option), rather than fine-grained row locks.
(2) because MySQL row locks apply to indexes rather than to records, although they access records of different rows, if the same index key is used,
Yes, there will be lock conflicts.
Shared lock: SELECT * FROM xx WHERE... LOCK IN SHARE MODE
Apply exclusive lock: SELECT * FROM xx WHERE... FOR UPDATE
Before 5.1, you can only view the lock STATUS using commands such as show full processlist and show engine inoodb status.
After 5.1: (after InnoDB plugin is used)
INFORMATION_SCHEMA:
INNODB_TRX
INNODB_LOCKS
InnoDB_LOCK_WAITS
Show engine innodb mutex; # latch lock
Show engine innodb status \ G; # lock
INNODB_TRX
Select * from information_schema.innodb_trx \ G;
INNODB_LOCKS
Select * from information_schema.innodb_locks \ G; |
INNODB_LOCK_WAITS
Select * from information_schema.innodb_lock_waits \ G;
Innodb_trx:
Let's take a look at some of the most common fields in the innodb_trx table:
Trx_id: unique transaction ID in the InnoDB storage engine
Trx_state: the status of the current transaction
Trx_started: The start time of the transaction.
Trx_wait_started: The start time of the transaction.
Trx_mysql_thread_id: the thread ID in Mysql and the result displayed by show processlist.
Trx_query: SQL statement run by a transaction.
Innodb_locks
Let's take a look at some of the most common fields in the innodb_locks table:
Lock_id: the ID of the lock.
Lock_trx_id: The transaction ID.
Lock_mode: Lock mode.
Lock_type: the lock type, table lock or row lock.
Lock_table: the table to be locked.
Lock_index: the index of the lock.
Lock_space: ID of the tablespace of The InnoDB storage engine.
Lock_page: Number of locked pages. If it is a table lock, the value is NULL.
Lock_rec: Number of locked rows. If it is a table lock, the value is NULL.
Lock_data: the primary key value of the locked row. When it is a table lock, the value is NULL.
Innodb_lock_waits
Let's take a look at some of the most common fields in the innodb_lock_waits table:
Requesting_trx_id: The transaction ID of the resource to be locked.
Requesting_lock_id: ID of the applied lock.
Blocking_trx_id: the ID of the blocked lock.
**************************************** **************************************** *******************************
Experiment 1: observe INNODB_TRX, INNODB_LOCKS, InnoDB_LOCK_WAITS, processlist, status
**************************************** **************************************** ******************************
Create table gyj_t1 (id int primairy key, name varchar (10 ));
Insert into gyj_t1 values (1, 'aaaaa ');
Mysql> show variables like '% autocommit % ';
Mysql> select @ tx_isolation;
Mysql> show variables like 'innodb _ lock_wait_timeout ';
Mysql> set global innodb_lock_wait_timeout = 600;
Mysql> set innodb_lock_wait_timeout = 600;
Session 1
Mysql> begin;
Mysql> update gyj_t1 set name = 'bbbbbb' where id = 1;
Session 2
Mysql> begin;
Mysql> update gyj_t1 set name = 'bbbbbb' where id = 1;
Session 3
Mysql> select * from information_schema.innodb_trx \ G;
Mysql> select * from information_schema.innodb_locks \ G;
Mysql> select * from information_schema.innodb_lock_waits \ G;
Mysql> show processlist;
Mysql> show engine innodb status \ G;
**************************************** *****
Experiment 2: lock Case 1: Lock on clustered indexes
**************************************** ******
1. default RR isolation level
2. automatic submission
3. create a table
Create table student
(
Id int unsigned not null auto_increment,
Xh int unsigned not null,
Name varchar (10) not null,
Bjmc varchar (20) not null,
Primary key (id ),
Key xh (xh)
) Engine = InnoDB;
3. Insert two Records
Insert into student values (1, 1, 'guoyj', 'jsj01 '), (2, 2, 'jfedu', 'jsj01 ');
4. scenario 1
Set autocommit = 0;
(1) session 1
Select * from student where id = 1 for update;
(2) session 2
Select * from student where id = 1; # is consistent non-locked read blocked at this time? (No)
Select * from student where id = 1 lock in share mode; # will this block? (Yes)
(3) session 1
Commit; or rollback;
Conclusion: consistency non-lock read test (no lock is generated, so no Lock wait)
Intention exclusive lock, intention shared lock mutex test (Lock wait will occur)
5. Scenario 2
Set autocommit = 0;
(1) session 1
Select * from student where name = 'guoyj' for update;
(2) session 2
Select * from student where name = 'Ju U' for update; # will this block? (Yes)
(3) session 1
Commit; or rollback;
Conclusion: there is no index for the name column in the table structure. all records at the RR isolation level will be locked and exclusive locks will be applied.
6. Scenario 3
Set autocommit = 0;
(1) session 1
Select * from student where xh = 1 and name = 'guoyj' for update;
(2) session 2
Select * from student where xh = 1 and name = 'Ju U' for update; # will this block? (Yes)
(3) session 1
Commit; or rollback;
Summary: xh is indexed, xh = 1, Session 1 session 2 is the same row record, the same index will be locked, conflict occurs, and so on (name has no index, the range will be expanded !)
7. scenario 4
If I change the SQL statement of session 1 to select * from student where xh = 2 and name = 'jfedu' for update; will the lock wait occur in Session 2?
Set autocommit = 0;
(1) session 1
Select * from student where xh = 1 and name = 'guoyj' for update;
(2) session 2
Select * from student where xh = 2 and name = 'Ju U' for update; # will this block? (No)
Summary:
Session 2: xh is indexed. xh = 2 session 1 session 2 is a different row record. it is not the same index and will not wait!
MySQL's row lock is the lock applied to the index, rather than the record lock!
Because MySQL row locks apply to indexes rather than to records, although they access records of different rows, if the same index key is used,
Yes, there will be lock conflicts. Pay attention to this when designing applications.
