Row-level locks, table-level locks, page-level locks in MySQL

Understanding of MySQL optimistic lock, pessimistic lock, shared lock, exclusive lock, row lock, table lock concept

When we operate the database, there may be inconsistencies in data due to concurrency problems (data Conflicts)

Optimistic lock

Optimistic lock is not the database comes with, we need to implement it ourselves. Optimistic locking refers to the operation of the database (update operation), the idea is optimistic, that this operation will not lead to conflict, in the operation of data, no other special processing (that is, no lock), and after the update, then to determine whether there is a conflict.

The usual implementation is this: when the data in the table is manipulated (updated), add a version field to the data table, and each action, add the version number of that record to 1. That is to query the record first, get the version field, if you want to work on that record (update), you first determine whether the value of version at this moment is the same as the value of the version you just queried, if it is equal, there is no other program to manipulate it, You can perform an update that adds 1 to the value of the Version field, and if the update finds that the version value at the moment is not equal to the value of the version you just obtained, then there are other programs that have manipulated it during this period, and the update operation is not performed.

Example:

The order operation consists of 3 steps:

1. Check out the product information

Select (Status,status,version) from T_goods where Id=#{id}

2. Generate orders based on product information

3. Change the item status to 2

Update T_goods

Set status=2,version=version+1

Where Id=#{id} and version=#{version};

In addition to the manual implementation of the optimistic lock, many online frameworks are now packaged with optimistic lock implementation, such as Hibernate, you may need to search for "hiberate optimistic lock" try.

Pessimistic lock

The optimistic lock corresponds to the pessimistic lock. Pessimistic locking is the operation of the data, it is believed that the operation will be a data conflict, so in each operation by acquiring a lock to do the same data operation, which is similar to the Java synchronized, so pessimistic lock takes more time. In addition to the optimistic lock corresponding to the pessimistic lock is implemented by the database itself, to use, we directly call the database of related statements can be.

Here, the two other lock concepts involved in the pessimistic lock come out, and they are shared locks and exclusive locks. Shared and exclusive locks are different implementations of pessimistic locks, both of which fall into the category of pessimistic locks.

A shared lock sharing lock refers to sharing a single lock on the same resource for multiple different transactions. Equivalent to the same door, it has more than one key. Like this, your home has a door, the key of the gate has several, you have a, your girlfriend has a, you can all through this key into your home, go in the PA, and then understand Ha, yes, this is the so-called shared lock. Just said, for pessimistic lock, the general database has been implemented, shared lock is also a pessimistic lock, then the shared lock in MySQL is what command to invoke it. By querying the data, we learned that by adding the following statement after the executionlock in Share modeRepresents a shared lock on some resources. For example, I am here to open two query editors through MySQL, in which a transaction is opened, and the city table DDL is not executed as follows: [Plain]View PlainCopy

2. CREATE TABLE   ' City '   (  
4.    ' id '  bigint ( NOT NULL AUTO_INCREMENT,  )
6.    ' name '  varchar (255)  default null,  
8.    ' state '  varchar (255)  default null,  
10.   primary key  (' id ')   
12. ) Engine=innodb auto_increment=18 DEFAULT Charset=utf8;

Begin
SELECT * from the city where id = ' 1 ' lock in share mode, and then in another query window, update the data with ID 1 with the updated city set name= "666" Where id = "1"; , the operator interface enters the stutter state, after a few seconds, also prompts the error message[Sql]update City set Name= "666" Where id = "1";
[ERR] 1205-lock wait timeout exceeded; Try restarting transaction It proves, then, that the record lock for Id=1 is successful, and the record of this id=1 is locked before the previous record has been commit, and it can only be manipulated after the last transaction has released the lock, or the data can be manipulated with a shared lock. Experiment again: Update city set name= "666" Where id = "1" lock in share mode;[ERR] 1064-you has an error in your SQL syntax; check the manual, corresponds to your MySQL server version for The right syntax to use near ' lock in Share mode ' on line 1

Add the shared lock, also prompted the error message, through the query data to know, for Update,insert,deletethe reason that the statement automatically adds its lockSo I tried again. Select * from the city where id = "1" lock in share mode;
This is a success.
Exclusive lock and lock it corresponds to a shared lock, which means that there can be only one lock on the same resource for a number of different transactions. With the shared lock type, after the statement that needs to be executed, addFor updateYou can do it.

Row lock

A row lock, understood by the literal meaning, is to add a lock to a row, which is a record plus a lock.

For example, shared lock statements previously demonstrated

SELECT * FROM city where id = ' 1 ' lock in share mode;

Because the ID field is the primary key for the city table, it is also equivalent to an index. When executing locking, a record with the ID of 1 is added to the lock, and the lock is a row lock.

Table lock

Table lock, which corresponds to the row lock, adds a lock to the table.

As described in the lock mechanism of the database, the database locks can be divided into row-level locks (InnoDB engines), table-level locks (MyISAM engines), and page-level locks (BDB engines) in the DBMS according to the granularity of the locks.

Row-level Locks

A row-level lock is the most granular lock in MySQL that locks only on the row of the current operation. Row-level locks can greatly reduce the conflict of database operations. The lock granularity is minimal, but the cost of locking is the largest. Row-level locks are divided into 共享锁 and 排他锁 .

Characteristics

The cost is big, locking is slow; there is a deadlock, the lock granularity is minimal, the probability of lock conflict is the lowest, and the concurrency is the highest.

Table-Level Locks

Table-level lock is the largest lock in MySQL, which indicates that the whole table of the current operation is locked, it is simple, the resource consumption is low, and is supported by most MySQL engines. The most commonly used MyISAM and INNODB support table-level locking. Table-level locking is divided into 表共享读锁 (shared lock) and 表独占写锁 (exclusive).

Characteristics

The cost is small, lock fast, no deadlock, lock granularity is high, the probability of lock conflict is highest, and the concurrency is the lowest.

Page-level Locks

Page-level locks are a lock in MySQL that has a lock granularity between row-level and table-level locks. Table-level lock speed is fast, but conflicts are many, row-level conflict is small, but slow. So we took a compromised page level and locked a contiguous set of records at once. BDB support page-level locks

Characteristics

Overhead and lock times are bounded between table and row locks, deadlock occurs, lock granularity bounds between table and row locks, and concurrency is common

The lock mechanism of MySQL common storage engine

MyISAM and memory with table-level lock (Table-level locking)

BDB with page lock (page-level locking) or table-level lock, default to page lock

InnoDB supports row-level locks (row-level locking) and table-level locks, which are row-level locks by default

Row locks and table locks in InnoDB

As mentioned earlier, both row and table locks are supported in the InnoDB engine, so when will the entire table be locked, or only one line locked?

InnoDB row locks are implemented by locking the index entries on the index, which is different from Oracle, which is achieved by locking the corresponding data rows in the data block. InnoDB This type of row lock implementation is characterized by the fact that InnoDB uses row-level locks only if the data is retrieved by index criteria, otherwise INNODB will use a table lock!

In practice, it is important to pay special attention to this feature of the InnoDB row lock, otherwise, it may lead to a lot of lock conflicts, which can affect the concurrency performance.

Row-level locks are index-based, and table-level locks are used if an SQL statement does not use a row-level lock for the index. The disadvantage of row-level locks is that they require a large number of lock resources to be requested, so they are slow and memory consuming.

Row-level locks and deadlocks

There is no deadlock in MyISAM because MyISAM always gets all the locks needed, either all or all. In the InnoDB, the lock is gradually obtained, resulting in the possibility of deadlock.

In MySQL, a row-level lock is not a direct lock record, but a lock index. Indexes are primary key index and non-primary key index Two, if a SQL statement operation primary Key index, MySQL will lock the primary key index, if a statement operation non-primary key index, MySQL will first lock the non-primary key index, and then lock the relevant primary key index. In the update, delete operation, MySQL not only locks all index records scanned by the Where condition, but also locks adjacent key values, known as Next-key locking.

When two transactions are executed simultaneously, one locks the primary key index and waits for other related indexes. Another lock has a non-primary key index, waiting for the primary key index. This will cause a deadlock to occur.

After a deadlock occurs, InnoDB can generally detect and cause one transaction to release the lock fallback, and the other to acquire the lock to complete the transaction.

There are several ways to avoid deadlocks, and here are just a few of the three common

1, if different programs will access multiple tables concurrently, as far as possible to agree to access the table in the same order, can greatly reduce the deadlock opportunity.

2, in the same transaction, as far as possible to lock all the resources needed to reduce the deadlock generation probability;

3, for very easy to generate deadlock in the business part, you can try to use the upgrade lock granularity, through table-level locking to reduce the probability of deadlock;

Resources

20.3.4 InnoDB Line Lock Implementation method

Row-level locks, table-level locks, page-level locks in MySQL