This article will take you through the locks in Mysql, understand shared locks, exclusive locks, pessimistic locks, optimistic locks, and talk about usage scenarios. I hope it will be helpful to everyone!
show variables like '%storage_engine%';
#MyISAM uses table locks to operate data. When you update a record, you must lock the entire table. , resulting in lower performance and low concurrency. Of course, there will be no deadlock problem at the same time.
The biggest differences between InnoDB and MyISAM are two points: first, InnoDB supports transactions; second, InnoDB uses row-level locks.
In Mysql, row-level locks do not directly lock records, but lock indexes. Indexes are divided into primary key indexes and non-primary key indexes. If a SQL statement operates on the primary key index, MySQL will lock the primary key index; if a statement operates on a non-primary key index, MySQL will first lock the non-primary key index and then lock it. Related primary key index.
InnoDB row locks are implemented by locking index entries. If there is no index, InnoDB will lock records through a hidden clustered index. In other words: If you do not retrieve data through index conditions, InnoDB will lock all data in the table, and the actual effect is the same as table locking. Because there is no index, you have to scan the entire table to find a certain record. To scan the entire table, you have to lock the table.
The addition, deletion and modification operations of the database will add exclusive locks by default, and the query No locks will be added.
Shared lock: Add a shared lock to a certain resource, you can read the resource, and others can also read the resource (you can also continue to add shared locks , that is, multiple shared locks can coexist), but cannot be modified. If you want to modify it, you must wait until all shared locks are released.
Exclusive lock: Add an exclusive lock to a certain resource. You can add, delete, modify, and check yourself, but others cannot perform any operations.
//共享锁 select * from 表名 lock in share mode //排他锁 select * from 表名 for update
T1: select * from 表名 lock in share mode //假设还未返回结果 T2: update 表名 set name='autofelix'
T1: select * from table lock in share mode T2: select * from table lock in share mode
T1: 开启事务,执行查询更新两个操作 select * from table lock in share mode update table set column1='hello' T2: 开启事务,执行查询更新两个操作 select * from table lock in share mode update table set column1='world'
T1: begin update table set content='hello' where id=10 T2: begin update table set content='world' where id=20
T1: begin select * from table for update update table set content='hello' T2: begin select * from table for update update table set content='world'
T1: begin select * from table [加更新锁操作] update table set content='hello' T2: begin select * from table [加更新锁操作] update table set content='world'
update table set num=num-1 where id=10 and version=12
【相关推荐:mysql视频教程】
The above is the detailed content of Learn more about locks in Mysql and talk about usage scenarios!. For more information, please follow other related articles on the PHP Chinese website!