InnoDB的共享锁和独占锁机制

MySQL的锁机制比较简单，其最 显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁；InnoDB存储引擎既支持行级锁，也支持表级锁，但默认情况下是采用行级锁。
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般 

本文主要针对InnoDB的读锁与写锁比较

场景

多个查询在同一时刻修改数据，都会产生并发控制问题。在处理并发读或写的时候，可以通过共享锁，排他锁解决

共享锁

共享锁又称读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问的数据，但是只能读不能修改

排他锁

排他锁又称写锁，简称X锁，排他锁不能与其他锁并存，如果一个事务获取了一行数据的排他锁，其他事务就不能获取改行的其他锁，包括共享锁和排他锁。获取到排他锁的事务可以对数据进行读取和修改。

注意

MySQL InnoDB引擎默认的update，delete，insert语句会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁。加排他锁可以使用select ...for update语句，加共享锁可以使用select ... lock in share mode语句。所以加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。

排他锁实际操作

第一种情况

新建一张test表

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  

插入2条数据

我们在第一个客户端A执行如下sql：

BEGIN;-- 开启事务
SELECT * FROM `test` WHERE id = 1 FOR UPDATE;

上面语句我并没有提交或者回滚事务，所以一直把锁加着

然后我们在客户端B执行：

SELECT * FROM `test` WHERE id = 1 FOR UPDATE; -- 加排他锁

结果如下

发现等待超时，因为客户端A没有释放锁，所以客户端B不能对这行加锁，但是对id=2加排他锁以及普通select还是可以查的。如果此时客户端A立马commit或rallback，客户端B立即能获取到锁。

第二种情况

我们接着来看一种特殊情况，假如新建另外一张表test_noid

CREATE TABLE `test_noid` (
  `name` varchar(10) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  

这张表只有一个字段，也没有主键id。也没有索引，所以这个时候InnoDB只能使用表锁

表数据：

我们先在客户端A执行如下语句：

BEGIN;
SELECT * FROM test_noid WHERE `name`='lisi' FOR UPDATE;

然后我们在客户端B执行：

SELECT * FROM test_noid WHERE `name`='zhao' FOR UPDATE;

可以看到虽然我客户端B查的是另外一行的数据，但是现在是表锁，锁的是整张表，所以客户端B也是等待的。但是普通的select还是可以的：

如果现在给name字段加索引，那么这个时候InnoDB就是行级锁。其结果和第一种情况一样。

共享锁实际操作

我们现在客户端A执行如下：

BEGIN;
SELECT * FROM `test` WHERE id=1 LOCK IN SHARE MODE;

对id=1数据加共享锁

现在我们在客户端B也对id=1加共享锁：

SELECT * FROM `test` WHERE id=1 LOCK IN SHARE MODE;

发现客户端B也能加共享锁，验证了共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据。

然后我们在客户端C对这条数据修改：

UPDATE `test` SET `name`='yif' WHERE id = 1;

这个时候也是会超时的。因为共享锁只能看不能修改。

所以对id=1加了共享锁，其他进程加共享锁也能访问，但是如果对id=1的数据进行update也是不行的，对其他行update是可以的

参考：blog.****.net/luzhensmart…