MySql锁机制

1.MySQL锁的基本介绍

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的 计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。

  MySQL的锁机制比较简单，其最 显著的特点是不同的存储引擎支持不同的锁机制。
  比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；
  InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。 

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。 ​ 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

在这里,我写的是关于锁在整体Innodb和myisam两种存储引擎下的体现,因为在Innodb中,因为隔离级别的不同,以及查询条件where指定的列有无索引,什么类型的索引,演化出众多的类型的行级别锁,例如: 临建锁,间隙锁,记录锁等众多的复杂锁

2.Myisam中的锁

1.读写锁

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

以下操作都是在两个client并发下产生的情况
​
lock table 表名 read       显示的加读锁
表共享读锁: 当client1获得读锁以后,client1只能读不能写,client2只能读不能写
           client1当前持有表1的读锁,client1现在想查询表2的信息,此时是不成功的,因为myisam中查询自动加读锁,已经持有一个不能再           接着持有一个
           
lock table 表名  write     显示的加写锁        
表独占写锁: 当client1获得写锁以后,client1能读能写,client2不能读不能写

注意:

MyISAM在执行查询语句之前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要使用命令来显式加锁

2.MyISAM的并发插入问题

在自动添加的读写锁以外,MyISAM还为我们提供了显示的本地读锁

使用方法:  lock table 表名 read local 显示的添加本地读锁

以这种方式创建的读锁,client1创建的锁:
client1只能读,不能写,client1读取不到client2插入的数据,但是client2可读可写,并且可以读到client2写入的数据
​
MyISAM表的读和写是串行的，这是就总体而言的，但是在使用本地读锁的方式，MyISAM也支持了查询和插入操作的并发执行

3.Innodb的锁

Innodb有行锁和表锁两种

1.Innodb的行锁

行锁锁的是索引

例:
select * from test where id=2 for update   id有索引,锁id=2的列
select * from test where id=2 and name='wang' for update   id有索引,name没有索引,锁id=2的所有行,并不是所有条件都匹配完再锁指定的id=2 and name='wang'都符合的行

1.锁生效的语句

1. update,delete,insert都会自动给涉及到的数据加上排他锁
2. select …from…                 语句默认不会加任何锁类型
   select …for update语句         加排他锁
   select … lock in share mode    加共享锁

2.共享锁

以下都是没有任何索引,表锁的形式

共享锁: 又称读锁,就是多个事务对于同一数据可以共享同一把锁,都能访问到数据库,但是只能读不能修改,如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。


共享: 所谓的共享指的是多个client可以共享这一把锁
​
client1共享读锁添加后,client2能进行普通的读操作select …from…   以及再次加select … lock in share mode共享锁
client2不能进行加排他锁操作 select …for update,  update,delete,insert  都不行

1.只有一个客户端 共享锁可以被排他锁覆盖
2.要是有两个client,client1加了共享锁,client2不可以再加排他锁
3.要是有两个client,client1加了共享锁,client2还可以再添加共享锁,并且持有的是同一把锁
    此时client1和client2都尝试加排他锁,会报  Deadlock found when trying to get lock; try restarting transaction当试图获得锁时发现死锁; 试着重新启动事务 

3.排他锁

以下都是没有任何索引表锁的形式

排他锁: 排他锁不能和其他的锁并存,如果一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的锁,只有当前获取了排他锁的事务可以对数据进行读取和修改
但其他客户端可以直接通过select …from…查询数据，因为普通查询没有任何锁机制

排他: 加锁的client,排除掉其他客户端所有想要获取锁的类型
1. client1加排他锁,client1加共享锁,现在client持有的还是排他锁,无法覆盖
2. client1加排他锁,client2加共享锁,失败
3. client1加排他锁,client2加排他锁,失败

4.行级的体现

以上这些操作是建立在锁整个表的基础上,我们可以细化到某一行
select * from test where id=1 for update
select * from test where id=1 lock in share mode
通过这样的方式加锁,精确到行级别,相同的行才有共享锁排他锁等上述情况
​
client1:   select * from test where id=1 for update   
client2:   select * from test where id=2 for update
这两个语句,锁住行是互不干扰的,上述共享锁排他锁结论就不会生效,都可以添加成功

2.Inodb的表锁

1）意向共享锁（IS锁）：事务在请求S锁前，要先获得IS锁

2）意向排他锁（IX锁）：事务在请求X锁前，要先获得IX锁

意向锁是Innodb数据操作之前自动加的,不需要用户干预

Innodb的表锁是自动加的,那么什么时候使用行锁?
只有查询条件包含索引列时,才使用行锁,如果没有默认锁表
例如:
client1:   select * from test where id=1 for update   
client2:   select * from test where id=2 for update
如果id没有索引,client2执行阻塞
如果id有索引,都执行成功,只锁自己where的id行
​
select * from test where id=2 and name for update

4.自增锁

针对自增列自增长的一个特殊的表级别锁
show variables like 'innodb_autoinc_lock_mode';
--默认值1   代表连续   事务未提交则id永久丢失

5.总结

单从上面就可以看出innodb的行锁能成功被使用是有条件的,而且我在最上面说的,行锁的类型非常复杂,下面举个例子:

REPEATABLE-READ(可重复读)隔离级别+表有显式主键和索引

此情况可以分为以下几种：

1、表有显式主键和普通索引

创建如下表：

create table t5(id int not null,name char(20) default null,primary key(id),key idx_name(name));
insert into t5 values(10,'10'),(20,'20'),(30,'30');

(1)不带where条件

begin;
select * from t5 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对id添加临键锁，对name索引列添加临键锁，对主键索引添加X记录锁

(2)where条件是普通索引字段

begin;
select * from t5 where name='10' for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对name添加临键锁，对主键索引列添加X记录锁，为了防止幻读，对name的（10，20）添加间隙锁

(3)where条件是主键字段

begin;
select * from t5 where id=10 for update;
show engine innodb status\G

通过上述信息可以看到，对表添加了意向锁，对主键添加了记录锁。

(4)where条件同时包含普通索引字段和主键索引字段

begin;
select * from t5 where id=10 and name='10' for update;
show engine innodb status\G

此处大家需要注意，如果在执行过程中使用的是主键索引，那么跟使用主键字段是一致的，如果使用的是普通索引，那么跟普通字段是类似的，其实本质点就在于加锁的字段不同而已。

2、表有显式主键和唯一索引

创建如下表：

create table t6(id int not null,name char(20) default null,primary key(id),unique key idx_name(name));
insert into t6 values(10,'10'),(20,'20'),(30,'30');

(1)不带where条件

begin;
select * from t6 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对supremum添加临键锁，对name索引列添加临键锁，对主键索引添加X记录锁

(2)where条件是唯一索引字段

begin;
select * from t6 where name='10' for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对name和主键添加行锁

(3)where条件是主键字段

begin;
select * from t6 where id=10 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后主键添加行锁

(4)where条件是唯一索引字段和主键字段

begin;
select * from t6 where id=10 and name='10' for update;
show engine innodb status\G

此处大家需要注意，如果在执行过程中使用的是主键索引，那么跟使用主键字段是一致的，如果使用的是唯一索引，那么跟唯一索引字段是一样的，其实本质点就在于加锁的字段不同而已。

由此可见,innodb的锁的类型有非常多的演变,innodb默认是想要使用行级别锁的,有索引时才会使用行级别锁,使用行锁时对于两个客户端处理同一行共享锁和排他锁的表现形式是和表锁一样的

例如:

create table t5(id int not null,name char(20) default null,primary key(id),key idx_name(name)); insert into t5 values(10,'10'),(20,'20'),(30,'30');

select * from t5 for update;

这里因为有索引,所以行级别锁生效了,但是因为没有where条件,这里会以行级别的锁锁住每一行,那么其他客户端再处理无论哪一行现在都是有锁的,也就是两个客户端处理同一行,表现出来的状况其实和表锁是一致的,但是底层确确实实是行级别锁

select * from t6 where id=10 for update;

这里依然使用行锁,并且只锁id=10;那么其他客户端再处理id=10这一行时,两个客户端处理同一行,共享锁和排他锁的表现形式就展现出来了,如果处理id=9,就没有问题,因为两个客户端此时并没有处理同一行