nber1994



InnoDB各类语句的加锁方式与应用

October 20, 2017

胡乱探讨了mysql的InnoDB的简单的加锁机制与使用场景,同时也有加锁等级

加锁机制

锁定读、UPDATE、DELETE通常在处理SQL语句的过程中在扫描到的每个索引记录上加锁,不关心WHERE条件中可能排除行的非索引条件。比如,A表有两列i和j,i列有索引,j列没索引,当前存在(1,1),(1,2),(1,3),(1,4),(2,1),(2,2),(2,3),(2,4)……等记录,语句SELECT * FROM A WHERE i=1 AND j=3;会在所有i=1的索引记录上加锁,而不考虑j=3这个条件。如果查询中使用了辅助索引,InnoDB除了给扫描到的辅助索引加锁,还会查找到对应的聚集索引并在其上加锁。若语句用不到合适的索引,则MySQL会扫描整个表,每个表行都会被加锁,会阻塞其他用户的插入操作。

innoDB对不同的语句加不同的锁

加锁方式

innoDB预设的事务隔离机制为REPEATABLE READ,在SELECT 的读取锁定主要分为两种方式:

应用场景

假设商品表单products 内有一个存放商品数量的quantity ,在订单成立之前必须先确定quantity 商品数量是否足够(quantity>0) ,然后才把数量更新为1。

不安全的做法:

SELECT quantity FROM products WHERE id=3; 
UPDATE products SET quantity = 1 WHERE id=3;

会出现的问题

解决方案

SET AUTOCOMMIT=0; BEGIN WORK; 
SELECT quantity FROM products WHERE id=3 FOR UPDATE;  

此时products 数据中id=3 的数据被锁住(注3),其它事务必须等待此次事务 提交后才能执行 SELECT * FROM products WHERE id=3 FOR UPDATE 如此可以确保quantity 在别的事务读到的数字是正确的

UPDATE products SET quantity = '1' WHERE id=3 ; 
COMMIT WORK;

Row Lock 与 Table Lock

锁定(Lock)的数据是判别就得要注意一下了。由于InnoDB 预设是Row-Level Lock,所以只有「明确」的指定主键,MySQL 才会执行Row lock (只锁住被选取的数据) ,否则MySQL 将会执行Table Lock (将整个数据表单给锁住)

例子

明确指名主键,并且有此数据,row lock

SELECT * FROM products WHERE id='3' FOR UPDATE;

明确指明主键,但无数据,无lock

SELECT * FROM products WHERE id='-1' FOR UPDATE;

无主键table lock

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

主键不明确,table lock

SELECT * FROM products WHERE id<>'3' FOR UPDATE;
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;