Mysql--innodb Lock

MySQL's locking mechanism is relatively simple, the most notable feature is that different storage engines support different locking mechanisms. InnoDB supports row locks and sometimes upgrades to table locks; MyISAM only supports table locks.

Table lock is characterized by small overhead, fast locking, no deadlock, lock granularity, the probability of lock conflict is high, the concurrency is relatively low.

The characteristic of the row lock is that the cost is big, the locking is slow, there will be deadlock, the lock granularity is small, the probability of lock conflict is low, and the concurrency is relatively high.

1, the InnoDB lock type:

There are mainly: Read lock (Shared lock), write lock (exclusive lock), intent Lock and MDL lock.

①: Read Lock:

Read lock, abbreviation S lock, a transaction acquires a read lock of a data row, other transactions can obtain a line corresponding to the read lock, but cannot obtain a write lock, that is, when a transaction reads a data row, other transactions can also be read, but the data row cannot be changed row or delete operation.

There are two types of read lock applications, the first is the automatic submission mode of the SELECT query statement, do not need to add any locks, directly return the query results, which is consistent non-lock read. The second is to use Select...lock in share mode to add a read lock on the row or row record range that is being read, so that other transactions can be read, but if you want to apply a write lock, it will be blocked.

②: Write Lock:

Write locks, called X-Locks, a transaction acquires a write lock on a data row, other transactions can no longer get another lock on the row, the highest write lock priority.

Write-lock applications are simple, and some DML statements write locks on Row Records.

The Special one is the select for update, which adds a write lock to the read row record, so no other transaction can add any locks to the locked row, or it will be blocked.

③:MDL Lock

mysql5.5 introduced meta data lock, or MDL Lock, to guarantee the information of the metadata in the table. In session a, after the table has opened a query transaction, an MDL lock is automatically obtained and session B is not able to perform any DDL statement operations.

Operations that add fields to a table cannot be performed, and DML locks are used to ensure consistency between the data.

④: Intent Lock

In the MySQL InnoDB storage engine, intent locks are table-level locks. And there are two types of intent locks, namely, intent-sharing and intent-exclusive locks.

Intent shared Lock (IS) refers to the is lock that must be obtained before a data row is shared with a lock.

Intent exclusive Lock (ix) means that the IX lock of the table must be obtained before a data row is added to an exclusive lock.

In fact, the action of intent locks is similar to MDL, which is to prevent inconsistencies in the execution of DDL statements during transactions.

2, InnoDB line lock type:

The InnoDB lock is implemented by locking the index entry (that is, by locking the index page by means of a bitmap. , which means that InnoDB uses row locks only when retrieving data through index criteria, otherwise table locks are used. That is, if a batch update, if the criteria field is not indexed, the table will be locked!!!!! If there is an index only a row lock will appear!

InnoDB The default transaction isolation level is RR, and the parameter innodb_locks_unsafe_for_binlog=0 mode, there are three kinds of row locks.

①: The lock of a single row record (record lock); Note: Both the primary key and the unique index are the lock modes of the row record. At the RC isolation level, only record lock records the lock mode;

②: Gap Lock

③: The combination of record lock and Gap lock is called Next-key lock. (when InnoDB scans Index records, a record lock is first added to the selected index record, and a gap lock is added to the gap on both sides of the index record)

Attention:

The InnoDB row lock is actually loaded on top of the index entry.!!!!!!!

Under the RC isolation level, a phantom read behavior is allowed.!!!!!!

3. Lock wait and deadlock:

Lock wait: Refers to a lock generated during a transaction, and other transactions need to wait for the previous transaction to release its lock before it can occupy the resource. If the transaction has not been released, it will need to wait continuously until the lock wait time is exceeded, and a wait timeout error is reported. MySQL is controlled by the Innodb_lock_wait_timeout parameter, in seconds.

Show variables like '%innodb_lock_wait% '; ---View lock wait time-out

Deadlock: Refers to two or two processes in the execution process, because of the contention for resources caused by a mutual waiting phenomenon, is called the lock resource request produced a loop phenomenon, that is, the death cycle. Common error when deadlock found when trying to get Lock;try restarting transaction.

The InnoDB storage engine can automatically detect deadlocks and roll back the transaction.

Ways to avoid deadlocks:

①: If a different program accesses multiple tables concurrently, or when multiple rows of records are involved, it is possible to significantly reduce the chance of deadlocks by agreeing to access the tables in the same order.

②: As far as possible in the business of small transactions, avoid the use of large transactions, to timely commit or rollback transactions, can reduce the probability of deadlock.

③: In the same transaction, as much as possible to lock all the resources needed to reduce the probability of deadlock.

④: For business parts that are very prone to deadlocks, try using the upgrade lock granularity to reduce the probability of deadlocks by table locking.

4, Lock monitoring method:

You can use the: Show full processlist or show engine InnoDB status command to determine the condition of the lock problem in the transaction.

You can also view 3 sheets: information_schema: Innodb_trx, Innodb_locks, innodb_lock_waits

Mysql--innodb Lock