Introduction to mysql database lock mechanism

This article brings you an introduction to the MySQL database lock mechanism. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Concurrency control

• The task of concurrency control in the database management system is to ensure that multiple transactions do not access the same data in the database at the same time. Destroy the isolation and unity of transactions and the unity of the database.

• Blocking, timestamp, optimistic concurrency control and pessimistic concurrency control are the main technical means used for concurrency control

Lock

When concurrent transactions access a resource at the same time, it may lead to data inconsistency, so a mechanism is needed to sequence data access to ensure the consistency of database data. Lock is one of the mechanisms (recommended tutorial: MySQL tutorial)

Classification of locks

• Divided according to operations, it can be divided intoDML lock, DDL lock

• Divided according to lock granularity, it can be divided into table-level lock, row-level lock, page-level lock (mysql)

• Divided according to the lock level, it can be divided into Shared lock and exclusive lock

• Divided according to the locking method, it can be divided into It is automatic lock, display lock

• is divided according to the usage method, and can be divided into optimistic lock and pessimistic lock

DML locks are used to protect the integrity of data, including row-level locks (TX locks) and table-level locks (TM locks). DDL locks are used to protect the structure of database objects, such as the structure definition of tables, indexes, etc., including exclusive DDL locks, shared DDL locks, and interruptible parsing locks

Row-level locks

• Row-level lock is the most detailed lock in Mysql, which means that only the row currently operated is locked. Row-level locks can greatly reduce conflicts in database operations. Its locking granularity is the smallest, but the locking overhead is also the largest. Row-level locks are divided into shared locks and exclusive locks

• Features: high overhead, slow locking; deadlocks may occur; locking granularity The smallest, the lowest probability of lock conflict, and the highest degree of concurrency

Table-level lock

• Table-level lock is the largest locking granularity in Mysql A kind of lock means locking the entire table of the current operation. It is simple to implement, consumes less resources, and is supported by most Mysql engines. The most commonly used MYISAM and INNODB support table-level locking. Table-level locking is divided into table shared read lock (shared lock) and table exclusive write lock (exclusive lock)

• Features: low overhead , locking is fast; no deadlock will occur; the locking granularity is large, the probability of issuing lock conflicts is the highest, and the concurrency is the lowest

Page-level lock

• Page-level lock is a kind of lock in Mysql whose locking granularity is between row-level lock and table-level lock. Table-level locks are fast but have many conflicts. Row-level locks have few conflicts but are slow. Page-level locking compromises to lock an adjacent group of records. BDB supports page-level locks

Mysql common storage engine locking mechanism

• MyISAM and MEMORY use table-level locks

• BDB uses page-level locks or table-level locks. The default is page-level locks.

• InnoDB supports row-level locks and table-level locks. The default is row-level locks

InnoDB row locks and table locks

The InnoDB engine supports both row locks and table locks. So when will the entire table be locked, and when will a row be locked? ?

• InnoDB row locking is achieved by locking the index entries on the index. This is different from Mysql and Oracle, which locks the corresponding data rows in the data block. realized. This row lock implementation feature of InnoDB means: Only when data is retrieved through index conditions, InnoDB uses row-level locks, otherwise InnoDb will use table locks

• Practical application , we should pay attention to this feature of InnoDB row lock, otherwise it will easily lead to a large number of lock conflicts, thus affecting the concurrency performance

• When querying without index conditions, InnoDB uses It's a table lock, not a row lock

• Since MySQL's row lock is a lock for the index, not a lock for the record, although records of different rows are accessed, if Using keys of the same index will cause lock conflicts.

• When a table has multiple indexes, different transactions can use different indexes to lock different rows. In addition, whether a primary key index, a unique index or a normal index is used, InnoDB will use Row lock to lock the data

• Even if the index field is used in the condition, whether to use the index to retrieve the data is determined by Mysql by judging the cost of different row plans. If MySQL thinks that a full table scan is more efficient, such as for some very small tables, it will not use indexes. In this case, InnoDB will use table locks instead of row locks. Therefore, when analyzing lock conflicts, don’t forget to check the SQL execution plan

Row-level locks and deadlocks

• MyISAM will not produce deadlocks, because MyISAM always gets everything it needs at once Lock, either all satisfied or all waiting. In InnoDB, locks are acquired gradually, resulting in the possibility of deadlock

• In MySQL, row-level locks do not directly lock records, but lock indexes. Indexes are divided into primary key indexes and non-primary key indexes. If a SQL statement operates on the primary key index, MySQL will lock the primary key index; if a statement operates on a non-primary key index, MySQL will first lock the non-primary key index. Then lock the relevant primary key index. During Update and delete operations, MySQL not only locks all index records scanned by the where condition, but also locks adjacent key values, which is the so-called next-key locking

• Deadlock: When two transactions are executed at the same time, one locks the primary key index and is waiting for other related indexes. The other locks the non-primary key index and is waiting for the primary key index. A deadlock occurs.

• After a deadlock occurs, InnoDB can generally detect it and make one transaction release the lock and roll back, and another acquire the lock to complete the transaction

Avoid deadlock

• If different programs access multiple tables concurrently, try to agree to access the tables in the same order, which can greatly reduce the chance of deadlock

• In the same transaction, try to lock all the resources required at once to reduce the probability of deadlock

• For business parts that are very prone to deadlock, you can Try to use upgraded locking granularity to reduce deadlocks through table-level locking

Shared locks and exclusive locks

• Row-level locks are in MySQL The finest lock granularity, row-level locks can greatly reduce conflicts in database operations. Row-level locks are divided into shared locks and exclusive locks

1. Shared lock

• Shared lock is also calledread lock , is the lock created by the read operation. Other users can read the data concurrently, but no transaction can modify the data until all shared locks have been released.

• If transaction T adds a shared lock to data A, other transactions can only add shared locks to A and cannot add exclusive locks. Transactions that are granted shared locks can only read data and cannot modify data

• If transaction T adds a shared lock to data A and then modifies the data, other transactions will not be able to obtain the share Lock; similarly, if multiple transactions acquire shared locks on the same data, no transaction can modify the data

• Usage: SSELECT ... LOCK IN SHARE MODE

  Add LOCK IN SHARE MODE after the query statement. Mysql will add a shared lock to each row in the query result. When no other threads focus on the query result When any row uses an exclusive lock, it can successfully apply for a shared lock, otherwise it will be blocked. Other threads can also read tables using shared locks, and these threads read the same version of data

2. Exclusive lock

• Exclusive lock is also called write lock. If transaction T adds an exclusive lock to data A, other transactions cannot add any type of blockade to A. A transaction that acquires an exclusive lock can both read and modify data

• Usage: SELECT ... FOR UPDATE. Add FOR UPDATE after the query statement, and MySQL will add an exclusive lock to each row in the query result. When no other thread uses an exclusive lock on any row of the query result set, the exclusive lock can be successfully applied for. Otherwise it will be blocked

3. Intention lock

• Intention lock is a table-level lock, which is designed to reveal the following in a transaction The type of lock that will be requested for a row. Two table locks in InnoDB:

• Intention Shared Lock (IS): Indicates that the transaction is preparing to add a shared lock to the data row, that is to say, a shared lock is added to a data row The IS lock of the table must be obtained before

• Intentional exclusive lock (IX): Indicates that the transaction is preparing to add an exclusive lock to the data row, indicating that the transaction must first add an exclusive lock to a data row. The IX lock of the table is removed

• The intention lock is automatically added by InnoDB and does not require user intervention

Summary

For insert, update, delete, InnoDB will automatically add exclusive locks to the data involved; For general Select statements, InnoDB will not add any locks, and transactions can be explicitly added through the following statements Shared lock or exclusive lock

• Shared lock: select ... LOCK IN SHARE MODE

• Exclusive lock: SELECT ... FOR UPDATE

The above is the detailed content of Introduction to mysql database lock mechanism. For more information, please follow other related articles on the PHP Chinese website!