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

Lab Environment:

mysql5.6

Storage Engine: InnoDB

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, it is understood that by adding the lock in share mode behind the execution statement, a shared lock is added to 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:

2. CREATE TABLE ' city ' (
4. ' id ' bigint (a) 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= DEFAULT Charset=utf8;

Begin
SELECT * FROM city where id = ' 1 ' lock in share mode; Then, in another query window, update the data with the ID 1 to the updated city set name= "666" Where id = "1"; at this point, the operation 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 so prove that for id=1 record lock successfully, before the previous record has not been commit, the record of this id=1 is locked, only after the last transaction released the lock to operate, Or use a shared lock to manipulate this data. 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-use near ' lock in Share mode ' at Li NE 1

Plus the shared lock, also prompted the error message, through the query data to know, for the Update,insert,delete statement will automatically add the reason for its lock so, 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, add a for update after the statement that needs to be executed

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.

There's a myisam in the engine, temporarily.

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