MySQL锁

写在前面

对于MySQL来说，有三种锁的级别：表级、页级、行级。

表级的典型代表引擎为MyISAM，MEMORY，ISAM。对整个表加锁影响标准的所有记录。通常用在DDL语句中，如DELETE TABLE,ALTER TABLE等。

页级的典型代表引擎为BDB。 

行级的典型代表引擎为INNODB。对一行记录加锁，只影响一条记录。通常用在DML语句中，如INSERT, UPDATE, DELETE等。

==============================================================

下面列举 SELECT ... FOR UPDATE 进行行级锁，来说说众所周知的商城系统。
假设“iphone7 32G  金色”这件商品（product）的 product_id 为1，当下订单的时候必须先确定商品库存（num）是否足够（num>0）

不安全的做法？
A用户执行，SELECT num FROM product WHERE product_id=1;得到库存为1
A用户执行，UPDATE product SET num = num-1 WHERE product_id=1;得到库存为0

为什么不安全呢？商城是高并发的系统，如果同时N个人对这件商品进行下单，就肯定出现问题了。
假设商品库存只有1
A用户执行，SELECT num FROM product WHERE product_id=1;得到库存为1
B用户执行，SELECT num FROM product WHERE product_id=1;得到库存为1
A用户执行，UPDATE product SET num = num-1 WHERE product_id=1;得到库存为0
B用户执行，UPDATE product SET num = num-1 WHERE product_id=1;得到库存为-1
库存为-1。

怎么解决问题？加入行锁标识。
A用户执行，SET AUTOCOMMIT=0; BEGIN WORK; SELECT num FROM product WHERE id=1 FOR UPDATE; 得到库存为1
B用户执行，SET AUTOCOMMIT=0; BEGIN WORK; SELECT num FROM product WHERE id=1 FOR UPDATE; 等待......
当前 product 表 id=1 行的数据被A用户锁住，B用户必须等待此次事务 提交后才能执行
A用户执行，UPDATE product SET num = num-1 WHERE id=1 ; COMMIT WORK; A提交(Commit)写入数据库，得到库存为0，products 解锁，A用户订单成功。
B用户进行，SET AUTOCOMMIT=0; BEGIN WORK; SELECT num FROM product WHERE id=1 FOR UPDATE; 得到结果库存为0，不进行下单

造成死锁？下列情况会造成死锁
A用户准备购买商品1，商品2
B用户准备购买商品2，商品1
如果A用户，锁定商品1，等待商品2
如果B用户，锁定商品2，等待商品1
多用户的情况更为复杂，按实际情况而定。

怎么减少死锁？
由于 Innodb 的行级锁定和事务性，所以肯定会产生死锁，下面是一些比较常用的减少死锁产生概率的的小建议，读者朋友可以根据各自的业务特点针对性的尝试：
a) 类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；
b) 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
c) 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁

由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，否则MySQL将会执行Table Lock (将整个资料表单给锁住)。
注1: FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。
注2: 要测试锁定的状况，可以利用MySQL的Command Mode ，开二个视窗来做测试。

死锁了怎么办？
show status like 'table%';
● Table_locks_immediate：产生表级锁定的次数；
● Table_locks_waited：出现表级锁定争用而发生等待的次数
show status like 'innodb_row_lock%';
● Innodb_row_lock_current_waits：当前正在等待锁定的数量；
● Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
● Innodb_row_lock_time_avg：每次等待所花平均时间；
● Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
● Innodb_row_lock_waits：系统启动后到现在总共等待的次数
show OPEN TABLES where In_use > 0; 查看那些表锁到了
show processlist; 查看进程号
kill 1085850; 删除进程