1. Mysql lock base

Table level lock: lock a table = “myISam, InnoDB page level lock: lock a page of data row level lock: lock a row =” InnoDB

Select * from user where id =1 for update; Select * from user where id =1 lock in share mode; Lock in share mode: adds an exclusive lock and a shared lock to the statement to be checked

autocommit = 0; — Transaction autocommit 1 is autocommit 0 is uncommit

  1. Exclusive locks and shared locks

Exclusive locks: Exclusive and exclusive locks cannot be used together. Exclusive and shared locks cannot be used together. A shared lock can be used together with a shared lock. A common query is not affected by the lock. It can still obtain the data after the lock. Select * from user where id =1; Update user set username = ‘111’ where id =1; commit

SQL > select snapshot (); SQL > select snapshot ()

Select * from product where id = 1 for update; Select * from product where id = 1 for update;

Use our business to force into serialization

  1. Optimistic lock in pessimistic lock explanation

MVCC select * from product where id = 1 select * from product where id = 1

MVCC – Multi-version Concurrent control Primary key Username Age Data Version number data For the first time Version Default 1 ID username age Version 1 name 1 1

select * from user where id =1; update user set username = ‘111’,version = version+1 where id =1;

Optimistic lock solves oversold problems

  1. Deadlocks arise from processing

  2. Gap and row locks are upgraded to table locks

Transaction 1 Transaction 2

update product set product_stock = product_stock-1,version = version+1 where id =2 and version=1; commit;

Which of the following is not the MySQL locking mechanism? Exclusive lock Shared lock table level lock page level lock Row level lock Pessimistic lock Optimistic lock

Deadlock = is a phenomenon

ERROR 1213 (40001): Deadlock found when trying to get lock; Transaction 1 is waiting for transaction 2, and transaction 2 is waiting for transaction 1. Lock wait time wait_timeout=120 — set the lock wait time. When the lock wait time is up, there will be a failure and a success. Mysql’s selection depends on the transaction size

Indicates that a deadlock occurs between transactions

Gap locking: Harm when we use a range query instead of an equality query and request or exclusive locks: If we use a range query in a query, the lock will lock the range of all indexes. Even if that data doesn’t exist

SQL > alter table lock id =1; SQL > alter table lock id =1; SQL > alter table lock id =1

If a table lock is performed on an unindexed field (except for the primary key and unique index), it will be upgraded to a table lock. If an index fails, the table lock will be upgraded.

  1. If possible, index fields are locked
  2. Use equality rather than scoped queries whenever possible.

Not only exclusive locks, but also shared locks