Row locks

select amount from tbl_account where customer_id = ‘1’ for update;

Page locks

select amount from tbl_account where customer_id in (‘1′,’2’) for update;

Table locks

select amount from tbl_account for update;

summary

Are essentially write locks (pessimistic locks)

Read lock/shared lock

The database creates a new query to represent the first thread

start transaction; Select amount from tbl_accountwhere customer_id = '1' lock in share mode;

Copy the code

The database creates another query to represent the second thread

Select amount from tBL_account where tBL_account is set to amount to tBL_accountwhere customer_id = '1';
select amount from tbl_account  where customer_id = '1' lock inshare mode; Select amount from tbl_accountwhere customer_id = '1' for update;
Copy the code

Once the read lock is released in one thread, the second thread can add the write lock

commit;
Copy the code

Write lock/pessimistic lock

The database creates a new query to represent the first thread

start transaction; Select amount from tbl_accountwhere customer_id = '1' for update;

Copy the code

The database creates another query to represent the second thread

Select amount from tBL_accountwhere customer_id = '1'; Select amount from tbl_accountwhere customer_id = '1' lock in share mode;
select amount from tbl_account  where customer_id = '1' for update;
Copy the code

Must wait until the first thread write lock is released

commit;
Copy the code

summary

The same thread add write lock, read lock are not limited, can add countless.

The above locks are pessimistic locks and rely on the database mechanism. Especially in distributed mode, lock invalidation in Java. Write more and read less to ensure data security.

Optimistic locking

Update goods set amount = amount-#{buy},version=version+1 WHERE name = #{name} and version= #{version}

Update goods set amount = amount-#{buy},version=version+1 WHERE name = #{name} and amount-#{buy} >=0

The above two are suitable for panic buying.

Mechanism of the CAS

Use the Watch directive to provide CAS capabilities in redis transactions.

Gets and CAS implementations of memcached version 1.2.4

Random sleep millimeter, in order to stagger peak, prevent stack overflow