mysql锁探究和实验

　　如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

表锁和行锁

mysql最显著的特点是不同的存储引擎支持不同的锁机制。比如，

MyISAM和MEMORY存储引擎采用的是表级锁。
InnoDB存储引擎既支持行级锁也支持表级锁，但默认情况下是采用行级锁。

读锁和写锁

读锁（共享锁）：当一个表或一行数据被加上读锁，则其他申请读锁操作将被允许，但其他申请写锁操作将被阻塞，直到锁被释放。
写锁（独占锁）：当一个表或一行数据被加上写锁，则其他申请读锁操作和申请写锁操作都将被阻塞，直到锁被释放。

一、MyISAM：

首先我们创建两个测试表及表数据：

create table tb_myISAM1(
    id int auto_increment primary key,
    value varchar(50) null
) engine=MyISAM;

create table tb_myISAM2(
    id int auto_increment primary key,
    value varchar(50) null
) engine=MyISAM;

insert into tb_myISAM1(value) values (uuid());
insert into tb_myISAM1(value) values (uuid());

insert into tb_myISAM2(value) values (uuid());
insert into tb_myISAM2(value) values (uuid());

在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预。所以我们采用通过sql脚本显式加锁的方式进行测试。

（一）表读锁

先说结论：如果某个session通过脚本显式的给表增加了读锁，那么

当前session：可以针对该表执行读操作；不可以针对该表执行写操作（会异常）；不可以针对其他表执行读操作（会异常）；不可以针对其他表执行写操作（会异常）。
其他session：可以针对该表执行读操作；不可以针对该表执行写操作（会阻塞）；可以针对其他表执行读操作；可以针对其他表执行写操作。

验证脚本：

lock tables tb_myISAM1 read;  -- 1
select * from tb_myISAM1;  -- 2
insert into tb_myISAM1 (value) values (uuid()); -- 3
select * from tb_myISAM2;  -- 4
insert into tb_myISAM2 (value) values (uuid()); -- 5
unlock tables; -- 6

我们为上面的脚本的每一行增加了序号以表述方便：

建立session1，执行1后：执行2通过，执行3异常，执行4异常，执行5异常；不要关闭session1再建立session2：执行2通过，执行3阻塞，执行4通过，执行5通过。

（二）表写锁

先说结论：如果某个session通过脚本显式的给表增加了写锁，那么

当前session：可以针对该表执行读操作；可以针对该表执行写操作；不可以针对其他表执行读操作（会异常）；不可以针对其他表执行写操作（会异常）。
其他session：不可以针对该表执行读操作（会阻塞）；不可以针对该表执行写操作（会阻塞）；可以针对其他表执行读操作；可以针对其他表执行写操作。

验证脚本：

lock tables tb_myISAM1 write;  -- 1
select * from tb_myISAM1;  -- 2
insert into tb_myISAM1 (value) values (uuid()); -- 3
select * from tb_myISAM2;  -- 4
insert into tb_myISAM2 (value) values (uuid()); -- 5
unlock tables; -- 6

我们为上面的脚本的每一行增加了序号以表述方便：

建立session1，执行1后：执行2通过，执行3通过，执行4异常，执行5异常；不要关闭session1再建立session2：执行2阻塞，执行3阻塞，执行4通过，执行5通过。

（三）锁争抢

如果有两个session同时申请同一个表的不同锁，session1申请读锁，session2申请写锁，那么MyISAM会先执行写锁，哪怕是申请读锁操作先到。这是因为MyISAM认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。

二、innoDB

innoDB与MyISAM的最大不同有两点：一是支持事务；二是采用了行级锁。在mysql的事务中，对表或行的加锁解锁操作时自动执行的，一般不需要用户干预。但是用户可以在start transaction开启事务后，可通过脚本显式的进行加锁和解锁，事务commit或rollback后锁会自动释放。

-- 行读锁例子
select * from dt_table1 where id = 1 lock in share mode;
-- 行写锁例子
select * from dt_table1 where id = 1 for update;

首先我们创建两个测试表及表数据

create table dt_table1(
    id int auto_increment primary key,
    value varchar(50) null
) ;insert into dt_table1(value) values (uuid());
insert into dt_table1(value) values (uuid());

（1）行读锁

先说结论：如果某个session为某一行增加了读锁，那么：

当前session：可以重新针对该行加写锁；可以针对该行执行读操作；可以针对该行执行写操作。
其他session：可以针对该行加读锁；不可以针对该行加写锁（会阻塞），可以针对该行执行读操作；不可以针对该行执行写操作（会阻塞）。

验证脚本：

start transaction; -- 1
select * from dt_table1 where id = 1 lock in share mode; -- 2
select * from dt_table1 where id = 1 for update ; -- 3
select * from dt_table1 where id = 1; -- 4
update dt_table1 set value = '00000000' where id = 1; -- 5
commit; -- 6

我们为上面的脚本的每一行增加了序号以表述方便：

建立session1执行1、2后：执行3通过，执行4通过，执行5通过；不要关闭session1再建立session2并执行1后：执行2通过，执行3阻塞，执行4通过，执行5阻塞。

（2）行写锁

先说结论：如果某个session为某一行增加了写锁，那么：

当前session：可以重新针对该行加读锁；可以针对该行执行读操作；可以针对该行执行写操作。
其他session：不可以针对该行加读锁（会阻塞）；不可以针对该行加写锁（会阻塞）；不可以针对该行执行读操作（会阻塞）；不可以针对该行执行写操作（会阻塞）。

验证脚本：

start transaction; -- 1
select * from dt_table1 where id = 1 lock in share mode; -- 2
select * from dt_table1 where id = 1 for update ; -- 3
select * from dt_table1 where id = 1; -- 4
update dt_table1 set value = '00000000' where id = 1; -- 5
commit; -- 6

我们为上面的脚本的每一行增加了序号以表述方便：

建立session1执行1、3后：执行2通过，执行4通过，执行5通过；不要关闭session1再建立session2并执行1后：执行2阻塞，执行3阻塞，执行4阻塞，执行5阻塞。

（3）行锁机制

InnoDB行锁是通过给索引上的索引项加锁来实现的，所以在上例中我们创建表dt_table1的时候，id是作为主键出现的。这一点和oracle不同，oracle是通过在数据块中对相应数据行加锁来实现的。所以InnoDB这种行锁实现特点意味着：

只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。
如果是使用相同的索引键，是会出现锁冲突的。

我们来验证第一条：

创建测试及表数据：

create table dt_table2(
  id int,
  value varchar(50) null
) ;

insert into dt_table2(id, value) values (1, uuid());
insert into dt_table2(id, value) values (2, uuid());

建立session1，执行

start transaction;
select * from dt_table2 where id = 1 for update;

建立session2，执行

start transaction;
update dt_table2 set value = '00000000' where id = 2;

会发现session2中执行的update操作被阻塞了，尽管session1中加写锁的行和session2中修改的行并不是同一行。这就是因为id字段没有索引，在session1中给id=1加写锁的时候，实际上是加的表写锁，而不是行写锁，所以才导致session2中的update操作阻塞。

我们来验证第二条：

创建测试及表数据：

create table dt_table3(
  id1 int,
  id2 int,
  value varchar(50) null
) ;

create index idx_dt_table3 ON dt_table3(id1);

insert into dt_table3(id1, id2, value) values (1, 1, uuid());
insert into dt_table3(id1, id2, value) values (1, 2, uuid());
insert into dt_table3(id1, id2, value) values (2, 1, uuid());
insert into dt_table3(id1, id2, value) values (2, 2, uuid());

建立session1，执行

start transaction;
select * from dt_table3 where id1 = 1 and id2 = 1 for update;

建立session2，执行如下脚本，update操作会被阻塞

start transaction;
update dt_table3 set value = '00000000' where id1 = 1 and id2 = 2;

建立session3，执行如下脚本，执行成功

start transaction;
update dt_table3 set value = '00000000' where id1 = 2 and id2 = 1;