MYSQL-锁机制

1、MyISAM

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

1.1、表共享读锁（Table Read Lock）

  对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求（读共享）；
加锁： lock table table_name write;
释放锁： unlock tables;

1.2、表独占写锁（Table Write Lock）

  对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的(写独占)；
加锁： lock table table_name read;
释放锁： unlock tables;

1.3、MyISAM写锁阻塞读的案例

  当一个线程获得对一个表的写锁之后，只有持有锁的线程可以对表进行更新操作。其他线程的读写操作都会等待，直到锁释放为止。

session1	session2
获取表的write锁定 lock table A write;
当前session对表的查询，插入，更新操作都可以执行 select * from A ; insert into A values(1,‘1’);	当前session对表的查询会被阻塞 select * from A ；
释放锁： unlock tables；	当前session能够立刻执行，并返回对应结果

1.4、MyISAM读阻塞写的案例

  一个session使用lock table给表加读锁，这个session可以锁定表中的记录，但更新和访问其他表都会提示错误，同时，另一个session可以查询表中的记录，但更新就会出现锁等待。

session1	session2
获得表的read锁定 lock table mylock read;
当前session可以查询该表记录： select * from A ;	当前session可以查询该表记录： select * from A ;
当前session不能查询没有锁定的表 select * from B Table ‘B’ was not locked with LOCK TABLES	当前session可以查询或者更新未锁定的表 select * from A insert into person values(1,‘1’);
当前session插入或者更新表会提示错误 insert into A values(1,‘aa’) Table ‘A’ was locked with a READ lock and can’t be updated update A set name=‘aa’ where id = 1; Table ‘A’ was locked with a READ lock and can’t be updated	当前session插入数据会等待获得锁 insert into A values(2,‘2’);
释放锁 unlock tables;	获得锁，更新成功

注意:
  MyISAM在执行查询语句之前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要使用命令来显式加锁，上例中的加锁时为了演示效果

2、InnoDB锁

2.1、InnoDB的行锁模式及加锁方法

2.1.1、共享锁(s)

  又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改；

2.1.2、排他锁(s)

  又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。
注意：
  mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询查的是快照，没有任何锁机制。

2.2、InnoDB行锁实现方式

  InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，**否则，InnoDB将使用表锁；

2.3、InnoDB锁详解

2.3.1、锁模式的含义

• 意向锁(Intention Locks) - IX： 意向排它锁；
• 临键锁(Next-key Locks) - X： 临键锁，锁定记录本身和记录之前的间隙；
• 临键锁(Next-key Locks) - S： 临键锁，锁定记录本身和记录之前的间隙；
• 记录锁(Record Locks) - X,REC_NOT_GAP： 只锁定记录本身，添加在行索引上的锁；
• 记录锁(Record Locks) - S，REC_NOT_GAP： 只锁定记录本身，添加在行索引上的锁；
• 间隙锁(Gap Locks) - X，GAP： 间隙锁，不锁定记录本身，锁定范围是索引记录之间的间隙，针对可重复读以上隔离级别；
• 间隙锁(Gap Locks) - S，GAP： 间隙锁，不锁定记录本身，锁定范围是索引记录之间的间隙，针对可重复读以上隔离级别；
• 插入意向锁(Insert Intention Locks) - X，GAP,INSERT_INTENTION：
    对已有数据行的修改与删除，必须加强互斥锁(X锁)，那么对于数据的插入，是否还需要加这么强的锁，来实施互斥呢？插入意向锁，孕育而生。插入意向锁，是间隙锁(Gap Locks)的一种(所以，也是实施在索引上的)，它是专门针对insert操作的。多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此；
• 自增锁(Auto-inc Locks)：
    自增锁是一种特殊的表级别锁(table-level lock)，专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值；

2.3.1、mysql加锁情况

2.3.1.1、REPEATABLE-READ隔离级别+表无显式主键和索引

创建表t,没有索引和主键，并插入测试数据

create table t(id int default null,name char(20) default null);
insert into t values(10,'10'),(20,'20'),(30,'30');

手动开启事务，执行语句并采用for update方式（当前读）

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

  从返回的信息中，可以看到对表添加了IX锁和4个记录锁，表中的三行记录上分别添加了Next-key Lock锁，防止有数据变化发生幻读，例如进行了更新、删除操作。同时会出现“ 0: len 8; hex 73757072656d756d; asc supremum;;”这样的描述信息，此操作也是为了防止幻读，会将最大索引值之后的间隙锁住并用supremum表示高于表中任何一个索引的值。
  同表下，如果加上where条件之后，是否会产生Next-key Lock呢？执行如下语句：

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

  从上述反馈信息中，可以发现跟不加where条件的加锁情况是一样的，会同时出现多个行的临键锁和supremum，这到底是为什么呢？
  出现supremum的原因是：虽然where的条件是10，但是每次插入记录时所需要生成的聚簇索引Row_id还是自增的，每次都会在表的最后插入，所以就有可能插入id=10这条记录，因此要添加一个supremum防止数据插入。
  出现其他行的临键锁的原因是：为了防止幻读，如果不添加Next-Key Lock锁，这时若有其他会话执行DELETE或者UPDATE语句，则都会造成幻读。

2.3.1.2、REPEATABLE-READ隔离级别+表有显式主键无索引

创建如下表并添加数据：

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

在此情况下要分为三种情况来进行分析，不同情况的加锁方式也不同：

1、不带where条件

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

通过上述信息可以看到，与之前的加锁方式是相同的。

2、where条件是主键字段

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

  通过上述信息可以看到，只会对表中添加IX锁和对主键添加了记录锁（X locks rec but not gap）,并且只锁住了where条件id=10这条记录，因为主键已经保证了唯一性，所以在插入时就不会是id=10这条记录。

3、where条件包含主键字段和非关键字段

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

通过看到，加锁方式与where条件是主键字段的加锁方式相同，因为根据主键字段可以直接定位一条记录。

2.3.1.3、REPEATABLE-READ隔离级别+表无显式主键有索引(普通索引/唯一索引)

创建如下表：

create table t3(id int default null,name char(20) default null);
create index idx_id on t3(id);
insert into t3 values(10,'10'),(20,'20'),(30,'30');

1、不带where条件，跟之前的情况类似

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

2、where条件是普通索引字段或者（普通索引字段+非索引字段）

执行如下语句：

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

通过上述信息可以看到，对表添加了IX锁，对id=10的索引添加了Next-Key Lock锁，区间是负无穷到10，对索引对应的聚集索引添加了X记录锁，为了防止幻读，对索引记录区间（10，20）添加间隙锁。

此时大家可以开启一个新的事务，插入负无穷到id=19的全部记录都会被阻塞，而大于等于20 的值不会被阻塞

3、where条件是唯一索引字段或者（唯一索引字段+非索引字段）

创建如下表：

create table t4(id int default null,name char(20) default null);
create unique index idx_id on t4(id);
insert into t4 values(10,'10'),(20,'20'),(30,'30');

执行如下语句：

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

通过上述信息可以看到，此方式与where条件是主键字段的加锁情况相同，表无显式主键则会把唯一索引作为主键，因为是主键，所以不能再插入id=10这条记录，因此也不需要间隙锁。

2.3.1.4、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锁，然后对supremum添加临键锁，对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

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

2.3.1.5、READ-COMMITTED隔离级别+表无显式主键和索引

创建表t,没有索引和主键，并插入测试数据

create table t7(id int default null,name char(20) default null);
insert into t7 values(10,'10'),(20,'20'),(30,'30');

手动开启事务，执行语句并采用for update方式（当前读）

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

通过上述信息可以看到，首先对表添加IX锁，然后对表的三行记录添加记录锁（聚簇索引）

同表下，如果加上where条件之后，是否会产生Next-key Lock呢？执行如下语句：

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

通过上述信息可以看到，首先对表添加IX锁，然后会对聚集索引添加记录锁，因为RC隔离级别无法解决幻读问题，所以不会添加临键锁。

2.3.1.6、READ-COMMITTED隔离级别+表有显式主键无索引

创建如下表并添加数据：

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

在此情况下要分为三种情况来进行分析，不同情况的加锁方式也不同：

1、不带where条件

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

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

2、where条件是主键字段

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

通过上述信息可以看到，首先对表添加IX锁，然后对表id=10的积累添加记录锁

3、where条件包含主键字段和非关键字段

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

通过上述信息可以看到，首先对表添加IX锁，然后对表id=10的积累添加记录锁

2.3.1.7、READ-COMMITTED隔离级别+表无显式主键有索引

创建如下表：

create table t9(id int default null,name char(20) default null);
create index idx_id on t9(id);
insert into t9 values(10,'10'),(20,'20'),(30,'30');

1、不带where条件，跟之前的情况类似

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

通过上述信息可以看到，首先对表添加IX锁，然后对表的三行记录添加记录锁（聚簇索引）

2、where条件是普通索引字段或者（普通索引字段+非索引字段）

执行如下语句：

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

通过上述信息可以看到，对表添加了IX锁，对id=10的索引添加了行锁，对索引对应的聚集索引添加了行锁，

3、where条件是唯一索引字段或者（唯一索引字段+非索引字段）

创建如下表：

create table t10(id int default null,name char(20) default null);
create unique index idx_id on t10(id);
insert into t10 values(10,'10'),(20,'20'),(30,'30');

执行如下语句：

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

通过上述信息可以看到，对表添加了IX锁，对id=10的索引添加了行锁，对索引对应的聚集索引添加了行锁。

2.3.1.8、READ-COMMITTED隔离级别+表有显式主键和索引

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

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

创建如下表：

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

(1)不带where条件

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

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

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

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

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

(3)where条件是主键字段

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

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

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

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

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

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

创建如下表：

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

(1)不带where条件

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

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

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

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

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

(3)where条件是主键字段

begin;
select * from t12 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

此处大家需要注意，如果在执行过程中使用的是主键索引，那么跟使用主键字段是一致的，如果使用的是唯一索引，那么跟唯一索引字段是一样的，其实本质点就在于加锁的字段不同而已；
RR加锁的过程主要是为了解决幻读问题；RC不会尝试解决幻读问题，所以锁都退化成行锁；

2.3.2、总结

对于MyISAM的表锁，主要讨论了以下几点：
（1）共享读锁（S）之间是兼容的，但共享读锁（S）与排他写锁（X）之间，以及排他写锁（X）之间是互斥的，也就是说读和写是串行的。
（2）在一定条件下，MyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。
（3）MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。
（4）由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

对于InnoDB表，本文主要讨论了以下几项内容：
mysql rr隔离级别默认解决了快照读幻读，当前读幻读问题通过临键锁解决；
（1）InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。
（2）在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。

在了解InnoDB锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：

• 尽量使用较低的隔离级别； 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
• 选择合理的事务大小，小事务发生锁冲突的几率也更小；
• 给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响； 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。