MySQL事务锁表

概述

MySQL中的事务，默认是自动提交的，即autocommit = 1；但是这样的话，在某些情形中就会出现问题：比如：如果你想一次性插入了1000条数据，mysql会commit1000次的，这时候会很影响性能。

这时候我们就需要把autocommit关闭掉[autocommit = 0]，其实也可以通过程序来控制，只要一次commit就可以了,这样也才能更好的体现事务的特点！

一锁二判三更新

对于需要操作数值，比如金额，个数等等！

记住一个原则：一锁二判三更新

在MySQL的InnoDB中，预设的Tansaction isolation level 为REPEATABLE READ（可重读）

在ｓｅｌｅｃｔ 的读取锁定主要分为两种方式：

ｓｅｌｅｃｔ ... LOCK IN SHARE MODEｓｅｌｅｃｔ ... FOR ｕｐｄａｔｅ

这两种方式在事务(Transaction) 进行当中ｓｅｌｅｃｔ 到同一个数据表时，都必须等待其它事务数据被提交(Commit)后才会执行。

而主要的不同在于LOCK IN SHARE MODE 在有一方事务要ｕｐｄａｔｅ 同一个表单时很容易造成死锁。

简单的说，如果ｓｅｌｅｃｔ 后面若要ｕｐｄａｔｅ 同一个表单，最好使用ｓｅｌｅｃｔ ... ｕｐｄａｔｅ。

实例

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

sql如下:

ｓｅｌｅｃｔ quantity FROM products WHERE id=3; ｕｐｄａｔｅ products SET quantity = 1 WHERE id=3;

这里其实是不安全的。

为什么不安全呢?

少量的状况下或许不会有问题，但是大量的数据存取「铁定」会出问题。如果我们需要在quantity>0 的情况下才能扣库存，假设程序在第一行ｓｅｌｅｃｔ 读到的quantity 是2 ，看起来数字没有错，但 是当MySQL 正准备要ｕｐｄａｔｅ 的时候，可能已经有人把库存扣成0 了，但是程序却浑然不知，将错就错的ｕｐｄａｔｅ 下去了。因此必须透过的事务机制来确保读取及提交的数据都是正确的。

修改sql如下:

SET AUTOCOMMIT=0; BEGIN WORK; ｓｅｌｅｃｔ quantity FROM products WHERE id=3 FOR ｕｐｄａｔｅ;

此时products 数据中id=3 的数据被锁住，其它事务必须等待此次事务 提交后才能执行ｓｅｌｅｃｔ * FROM products WHERE id=3 FOR ｕｐｄａｔｅ

如此可以确保quantity 在别的事务读到的数字是正确的。

ｕｐｄａｔｅ products SET quantity = ‘1‘ WHERE id=3 ; COMMIT WORK;

提交(Commit)写入数据库，products 解锁。

说明：

注1: BEGIN/COMMIT 为事务的起始及结束点，可使用二个以上的MySQL Command 视窗来交互观察锁定的状况。注2: 在事务进行当中，只有ｓｅｌｅｃｔ ... FOR ｕｐｄａｔｅ 或LOCK IN SHARE MODE 同一笔数据时会等待其它事务结束后才执行，一般ｓｅｌｅｃｔ ... 则不受此影响。注3: 由于InnoDB 预设为Row-level Lock，数据列的锁定可参考这篇。注4: InnoDB 表单尽量不要使用LOCK TABLES 指令，若情非得已要使用，请先看官方对于InnoDB 使用LOCK TABLES 的说明，以免造成系统经常发生死锁。MySQL ｓｅｌｅｃｔ ... FOR ｕｐｄａｔｅ 的Row Lock 与Table Lock

前面主要介绍ｓｅｌｅｃｔ ... FOR ｕｐｄａｔｅ 的用法，不过锁定(Lock)的数据是判别就得要注意一下了。由于InnoDB 预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。

MySQL大致可归纳为以下3种锁：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

实例:

假设有个表单products ，里面有id 跟name 二个栏位，id 是主键。

例1: (明确指定主键，并且有此数据，row lock)

ｓｅｌｅｃｔ * FROM products WHERE id=‘3‘ FOR ｕｐｄａｔｅ;

例2: (无主键，table lock)

ｓｅｌｅｃｔ * FROM products WHERE name=‘Mouse‘ FOR ｕｐｄａｔｅ;

例3: (主键不明确，table lock)

ｓｅｌｅｃｔ * FROM products WHERE id‘3‘ FOR ｕｐｄａｔｅ;

例4: (主键不明确，table lock)

ｓｅｌｅｃｔ * FROM products WHERE id LIKE ‘3‘ FOR ｕｐｄａｔｅ;

PS：Myisam 只支持表级锁，InnerDB支持行级锁 添加了(行级锁/表级锁)锁的数据不能被其它事务再锁定，也不被其它事务修改

（修改、删除） 。是表级锁时，不管是否查询到记录，都会锁定表。

乐观所和悲观锁策略

悲观锁：在读取数据时锁住那几行，其他对这几行的更新需要等到悲观锁结束时才能继续 。

乐观锁：读取数据时不锁，更新时检查是否数据已经被更新过，如果是则取消当前更新，一般在悲观锁的等待时间过长而不能接受时我们才会选择乐观锁。

总结

今天主要分享mysql事务ｓｅｌｅｃｔ for ｕｐｄａｔｅ及数据的一致性处理，然后介绍了行锁和表锁及悲观锁、乐观锁，后面再单独整理下mysql锁方面的内容和区别，感兴趣的朋友可以关注下！