Mysql optimistic lock, pessimistic lock, shared lock, exclusive lock, row lock, table lock concept understanding

Remember when I was in college, I often heard the teacher talking about the vocabulary of shared lock and exclusive lock in class. I only heard it once before, but I didn’t care about it and didn’t conduct in-depth research

Recently, in a variety of groups, and saw what optimistic lock, pessimistic lock what ghost feeling is very advanced vocabulary, so today to study these concepts, unveil its mysterious veil, continuously record my thoughts on these concepts

Experimental environment:

mysql5.6

Storage engine: innoDB

We may have data inconsistencies (data conflicts) due to concurrency problems when we operate the database.

Optimistic locking

Optimistic locking is not database inherent, we need to achieve our own. Optimistic locking refers to the operation of the database (update operation), the idea is optimistic, the operation will not cause conflict, the operation of the data, without any other special processing (that is, no lock), and after the update, to determine whether there is a conflict.

The usual implementation is to add a version field to the table as the data in the table is manipulated (updated), and increment the version number of that record by 1 with each operation. That is, query the record first and obtain the version field. If you want to operate (update) the record, check whether the version value at the moment is equal to the version value just queried. If the value is equal, it indicates that there is no other program to operate on it during this period, so you can perform the update. Increment the value of the Version field by 1; If the version value is not the same as the version value just obtained during the update, it indicates that another program has been operating on the version during the update period. In this case, the update operation is not performed.

For example:

Ordering operation includes 3 steps:

1. Query the product information

select (status,status,version) from t_goods where id=#{id}

2. Generate orders based on product information

3. Change the status of the product to 2

update t_goods 

set status=2,version=version+1

where id=#{id} and version=#{version};

In addition to implementing the optimistic lock manually, there are many frameworks on the web that have wrapped the implementation of the optimistic lock, such as Hibernate. If you need to, you may search for “Hiberate optimistic lock” to try.

Pessimistic locking

The opposite of an optimistic lock is a pessimistic lock. Pessimistic locking refers to the operation of data, which is considered to have data conflict, so the operation of the same data can only be carried out by obtaining the lock during each operation. This is similar to Synchronized in Java, so pessimistic locking takes more time. In addition to the optimistic lock corresponding, pessimistic lock is achieved by the database itself, to use, we directly call the relevant statements of the database can be.

With that said, two other lock concepts related to pessimistic locks come out: shared locks and exclusive locks. Shared lock and exclusive lock are different implementations of pessimistic lock, both of which belong to the category of pessimistic lock.

A Shared lock

CREATE TABLE `city` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
Copy the code

[SQL]update  city set name=”666″ where id =”1″;

[Err] 1205 – Lock wait timeout exceeded; try restarting transaction



















[Err] 1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘lock in share mode’ at line 1

Exclusive lock

Row locks

A row lock, literally, is the locking of a row, that is, a record.

Such as the shared lock statement shown earlier

SELECT * from city where id = “1”  lock in share mode; 

Since the id field is the primary key in the city table, it is also equivalent to the index. When the lock is performed, the record id with index 1 is locked, so the lock is a row lock.

Table locks

Table locks, as opposed to row locks, lock this table.