关于MySQL锁的一些试验

一、锁的种类

（1）、共享锁，也叫读锁，当前事务可以进行读写操作，而其他事务只能进行读操作，不能写操作，禁止其他事务对同样的数据集加排他锁，但允许加共享锁。

如：SELECT * FROM USER WHERE email = '[email protected]' LOCK IN SHARE MODE   

（2）、排他锁，只允许当前事务进行读、写操作，在此事务结束之前，其他事务只能进行读操作，写操作阻塞，等待锁的释放，且不能再加任何锁。

如：SELECT * FROM USER WHERE email = '[email protected]' for update

二、测试记录

开启两个sqlyog窗口，即两个session，分别模拟两个事务。新建user表，采用innoDB引擎，

CREATE TABLE USER (
	id VARCHAR(50) NOT NULL,
	phone VARCHAR(12) NOT NULL DEFAULT '',
	email VARCHAR(30),
	PASSWORD VARCHAR(32) NOT NULL,
	sex SMALLINT(1) NOT NULL DEFAULT 1,
	birth DATE,
	num VARCHAR(50) NOT NULL,
	PRIMARY KEY(id)
)ENGINE=INNODB CHARSET=utf8;

（1）、查询条件只有索引列

ALTER TABLE USER ADD INDEX (email)     -->对email字段添加普通索引

session1：

SET autocommit = 0  -->避免自动提交，开启事务
SELECT * FROM  USER WHERE email = '[email protected]' IN SHARE MODE

此时innoDB对指定的数据集加上共享锁，是行锁，在共享锁事务内是强烈不建议做写操作，如果非要写，必须能保证其他事务不对相关的数据集做写操作，否则死锁。。。

UPDATE USER SET num = '116119' WHERE email = '[email protected]' -->实施更新操作，发生在session2的更新操作之前

session2：

SET autocommit = 0  -->避免自动提交，开启事务
SELECT * FROM USER WHERE email = '[email protected]' -->普通查询，不阻塞，通过MVCC（多版本并发控制）实现
UPDATE USER SET num = '116117' WHERE email = '[email protected]' -->对加了共享锁的数据集进行更新操作，需要加排他锁，会遇到阻塞
UPDATE USER SET PASSWORD = '555555' WHERE email = '[email protected]' -->如果更新没有加共享锁的数据集，排他锁不受阻塞

在session2执行完更新操作事务之后，session1再次读取email = '[email protected]'的记录，还是之前的记录，并没有出现“不可重复读”，如果是删除操作也是如此。
上面的写操作中，查询语句用到了email，email是索引列，MySQL的行级锁是基于索引列的，不管是主键索引、唯一索引还是普通索引都可以，如果session2的更新操作中，查询语句不包含索引列的话，就会全表扫描，采用的表级锁，这时写操作就会出现锁阻塞，比如：

UPDATE USER SET num = '116118' WHERE num = '100000008'  -->num字段没有添加索引

最后session1执行commit，session2阻塞释放，更新操作得以执行，session1的更新操作被session2覆盖，这是session1的更新操作在session2之前的情况，如果反过来的话，就会出现死锁，所以尽量不要在共享锁的事务内执行写操作，除非可以保证其他事务不会对相同的数据集执行写操作；如果非要执行写操作，就要加上排他锁 for update

（2）、查询条件没有索引列

ALTER TABLE USER DROP INDEX email  -->删除email字段的普通索引

session1：

SET autocommit = 0 -->避免自动提交，开启事务
SELECT * FROM  USER WHERE email = '[email protected]' LOCK IN SHARE MODE   -->加上共享锁，此时innoDB对整个表加上共享锁，是表锁

session2：

UPDATE USER SET PASSWORD = '555555' WHERE email = '[email protected]'  -->此时所有数据集都会阻塞，即便不是被session1加上共享锁的数据集

（3）、查询条件既有索引列，又有非索引列

ALTER TABLE USER ADD INDEX (email)     -->再次对email字段添加普通索引

session1：

SET autocommit = 0 
SELECT * FROM  USER WHERE email = '[email protected]' AND num='www88811' FOR UPDATE

session2分为以下几种情况：

session2：

SET autocommit = 0 
SELECT * FROM  USER WHERE email = '[email protected]' AND num='8' FOR UPDATE 

虽然session1和session2查询的是不同的记录，但是session1与session2用的是同样的索引键email=‘[email protected]’，session2同样阻塞

------------------------------

session2：

SET autocommit = 0 
SELECT * FROM  USER WHERE email = '[email protected]' AND num='9' FOR UPDATE 

相比于上一种情况，数据集不同、且索引键不同，不阻塞

------------------------------

session2：

ALTER TABLE USER ADD UNIQUE(phone)  -->给phone字段加唯一索引
UPDATE USER SET num = 'www888' WHERE phone = '13677552093'

由于phone是唯一索引列，加指定数据集加排他锁，是行锁，由于锁的数据集不同，不阻塞，当然，如果数据集相同当然阻塞

------------------------------

session2：

SET autocommit = 0 
SELECT * FROM  USER WHERE phone = '1377877098' AND city='100000002' FOR UPDATE 

与上一种情况相比，多了一个非索引的查询项，数据集不同、且索引键不同，不阻塞

------------------------------

session2：

SET autocommit = 0 
SELECT * FROM  USER WHERE city='100000002' FOR UPDATE

由于查询条件没有索引项，锁住全表，必然阻塞

三、总结

1、innoDB的锁依赖于索引，如果查询条件有索引列，就是行级锁，若没有索引列，就会是表级锁，这是对分析锁的冲突很有帮助
2、在共享锁事务内，不要执行写操作，否则大概率死锁