InnoDB pessimistic lock, optimistic lock

Turn http://www.cnblogs.com/chenwenbiao/archive/2012/06/06/2537508.html

CREATE TABLE ' products ' (' ID ' )int( One) not NULL auto_increment, ' name ' varchar ( the) not NULL, ' quantity 'intNot NULL, ' cityid ' varchar ( -DEFAULT NULL, PRIMARY key (' ID '), key ' Idx_of_cityid ' (' Cityid ', ' id ')) ENGINE=Innodb;insert into Products (Name,quantity,cityid) VALUES (' Toothbrush',Ten,1) insert into products (Name,quantity,cityid) VALUES (' Rice',Ten,2) insert into products (Name,quantity,cityid) VALUES (' Beans',Ten,3) insert into products (Name,quantity,cityid) VALUES (' Apple',Ten,4);

Unsafe practices:

SELECT Quantity from Products WHERE id=3;

UPDATE products SET quantity = Quantity-1 WHERE id=3;

First visitor a second visitor

A) SELECT quantity from the products WHERE id=3;

C) SELECT quantity from the products WHERE id=3;

B) UPDATE Products SET quantity = Quantity-1 WHERE id=3;

D) UPDATE Products SET quantity = Quantity-1 WHERE id=3;

Suppose the number of quantity before two accesses is 10

When the first visitor executes a select, it gets quantit7=10,

The second visitor arrives, due to CPU time slice allocation, gives control to the second visitor, through Select, gets quantity=10

Again time slices round, the first visitor executes B-SQL, quantity=9

After the second visitor executes D-SQL, quantity is still 9 because the second accessor is unaware of the presence of the first visitor, which is a problem

Workaround

The simplest is to add pessimistic lock, disadvantage: If the lock is too long, other users can not access, affect concurrency, lock, will increase the extra cost

or application layer using optimistic lock, concurrency is better, avoid locking

First visitor a second visitor

A) SELECT quantity from Products WHERE id=3 for update;

C) SELECT quantity from Products WHERE id=3 for update;

B) UPDATE Products SET quantity = Quantity-1 WHERE id=3;

D) UPDATE Products SET quantity = Quantity-1 WHERE id=3;

SQL executes as follows

A window

At this point, in the b window, execute the Select again

Locked, for update pessimistic lock, also known as exclusive lock, does not allow others to read/write

A window, execution commit

At this point the b window

You can see the records of Id=1.

Also, when a select ... where condition in the where for update is not a primary key, even if it is a different index, the table is locked.

A window

b window

When the a window, commit, the b window returns the data, although the Cityid is indexed, a table lock also occurs

Locks are only done when the primary key

A window, not commit

b window fetch data, return data immediately

Two. Optimistic lock

Add a column version to the table, add 1 after update

Assume version=10 prior to access

First visit to second visit

A) Select quantity, version from products;

C) Select quantity, version from products;

B) Update products set version=version+1,quantity=quantity-1 where version=10 and id=1

D) Update products set version=version+1,quantity=quantity-1 where version=10 and id=1

When the first visitor executes B, version has been changed from 10 to 11

When the second visitor executes D, the version=10 record is not found, the affected record is 0, and several retries are required

　　　　　　　　　　

InnoDB pessimistic lock, optimistic lock