InnoDB table level lock

Row locks should be used in most cases, because transactions and row locks are often the reasons for choosing InnoDB, but table-level locks are also used in individual cases;
1) Transactions need to update most or all of the data, the table is relatively large, if the default row lock is used, not only the execution efficiency of this transaction is low, but also may cause other transactions to wait for a long time and lock conflicts;
2) The transaction involves multiple tables, which is more complicated and may cause deathlock, causing a large number of transactions to be rolled back.
Such as:
LOCK TABLE user READ; read lock table
LOCK TABLE user WRITE; write lock table
transaction execution...
COMMIT/ROLLBACK; transaction commit or rollback
UNLOCK TABLES; its own commit transaction, release all table locks occupied by threads

Intent Shared Lock & Intent Exclusive Lock

When using table locks, design an efficiency problem:

To acquire a shared lock S or an exclusive lock X of a table, at least make sure that this table has not been acquired by another transaction X lock.

Intentional lock solves the above problem: when you want to obtain the X lock of the table, you don't need to check which row locks (X or S) in the table are occupied, just check IS locks and IX locks!

Intentional shared lock (IS lock): The transaction plans to add a row shared lock to a record. Before a transaction adds a shared lock to a row of records, it must first obtain the IS lock of the table.

Intentional exclusive lock (IX lock): The transaction plans to add a row exclusive lock to a record. Before a transaction adds an exclusive lock to a row of records, it must first obtain the IX lock of the table.

1. The intent lock is acquired by the InnoDB storage engine before it acquires the row lock.
2. The intent locks are compatible and will not cause conflicts.
3. The meaning of the intent lock is to improve theEfficiently acquire table locks (X and S in the table refer to table locks, not row locks!!!)
4. Intentional locks are table-level locks, coordinating the coexistence relationship between table locks and row locks.The main purpose is to show that a transaction is locking a row or trying to lock a row.

Deadlock

MyISAM table locks are deadlock free because MyISAM always acquires all the locks it needs at once, either all or waiting, so there is no deadlock.But in InnoDB, except for transactions composed of a single SQL, locks are acquired gradually, that is, the granularity of locks is relatively small, which determines that deadlocks are possible in InnoDB.

mysql> select * from test_dead_lock where id=1 for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting
transaction

The deadlock problem is generally caused by our own application. Similar to the deadlock situation of multi-threaded programming, most of them are caused by the different order in which multiple threads acquire multiple lock resources.deadlock problem.Therefore, when we update multiple tables in the database, different code segments should update these tables in the same order to prevent lock conflicts from causing deadlock problems.

Optimization suggestions for locks

1. Try to use a lower isolation level
2. Design a reasonable index and try to use the index to access data to make locking more accurate, reduce the chance of lock conflict and improve concurrency
3. Choose a reasonableThe size of the transaction, the probability of lock conflicts in small transactions is small.
4. When different programs access a set of tables, they should try to agree to access each table in the same order. For a table, try to access in a fixed order as much as possible.row in the table.This can greatly reduce the chance of deadlock
5. Try to access data with equal conditions, so as to avoid the impact of gap locks on concurrent insertion
6. Do not apply for more lock levels than actually required
7. Unless necessary, do not display lock when querying