Mysql InnoDB 行锁和表锁介绍

2019独角兽企业重金招聘Python工程师标准>>>

 

mysql 的 InnoDB引擎支持行锁，与Oracle不同，mysql的行锁是通过索引加载的，即是行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引，则会全表扫描，行锁则无法实现，取而代之的是表锁

 

先介绍几个名词

表锁：不会出现死锁，发生锁冲突几率高，并发低

行锁：会出现死锁，发生锁冲突几率低，并发高

锁冲突：例如说事务A将某几行上锁后，事务B又对其上锁，锁不能共存否则会出现锁冲突。（但是共享锁可以共存，共享锁和排它锁不能共存，排它锁和排他锁也不可以）

死锁：例如说两个事务，事务A锁住了1~5行，同时事务B锁住了6~10行，此时事务A请求锁住6~10行，就会阻塞直到事务B施放6~10行的锁，而随后事务B又请求锁住1~5行，事务B也阻塞直到事务A释放1~5行的锁。死锁发生时，会产生Deadlock错误。锁是对表操作的，所以自然锁住全表的表锁就不会出现死锁。

 

行锁的类型

共享锁又称：读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。

排它锁又称：写锁。当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁。

共享锁的写法：lock in share mode

select  * from  test where math>60 lock in share mode；

排它锁的写法：for update

select * from test where math >60 for update；

 

行锁的实现

注意几点

1.行锁必须有索引才能实现，否则会自动锁全表

2.两个事务不能锁同一个索引

事务A先执行：
select * from test where math>60 for update;
 
事务B再执行：
select * from test where math<60 for update；

这样的话，事务B是会阻塞的。如果事务B把 math索引换成其他索引就不会阻塞，
但注意，换成其他索引锁住的行不能和math索引锁住的行有重复。

 

行锁示例

由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，否则MySQL将会执行Table Lock (将整个资料表单给锁住)。
举个例子:
假设有个表单products ，里面有id跟name二个栏位，id是主键。
例1: (明确指定主键，并且查询有数据，row lock)
SELECT * FROM products WHERE id='3' FOR UPDATE;
 
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;
 
 
 
例2: (明确指定主键，若查询无数据，无lock)
 
SELECT * FROM products WHERE id='-1' FOR UPDATE;
 
 
 
例2: (无主键，table lock)
 
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;
 
 
 
例3: (主键不明确，table lock)
 
SELECT * FROM products WHERE id<>'3' FOR UPDATE;
 
 
 
例4: (主键不明确，table lock)
 
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;
 
 
 
注1: FOR UPDATE仅适用于InnoDB，且必须在(BEGIN/COMMIT)中才能生效

 

转载于:https://my.oschina.net/u/3387320/blog/3053662