MySql的锁机制

mysql的锁机制

  • 数据库锁机制简单来说，就是数据库为了保证数据的一致性，使各种 共享资源 在被访问时变得 有序而设计 的一种规则。
  • MysQL的锁机制比较简单最著的特点是不同的存储引擎支持不同的锁机制。 InoDB支持行锁,(有时也会升级为表锁）MyISAM只支持表锁。
  • 表锁 的特点就是开销小、加锁快，不会出现死锁。锁粒度大，发生锁冲突的概率小，并发度相对低。
  • 行锁 的特点就是开销大、加锁慢，会出现死锁。锁粒度小，发生锁冲突的概率高，并发度高。

一、InnoDB行锁的种类

InnoDB默认的事务隔离级别是RR，并且参数innodb_locks_unsafe_for_binling=0的模式下，行锁有三种。

1、记录锁（Record Lock）

（1）不加索引，两个事务修改同一行记录

事务一：

begin;
update teacher set teacher_no = 'T2010005' where name = 'wangsi';

事务二：

begin;
update teacher set teacher_no = 'T2010006' where name = 'wangsi';

发现卡住了：

事务一提交了，事务二才获取了。

（2）不加索引，两个事务修改同一表非同行记录

事务一：

begin; 
update teacher set teacher_no = 'T2010005' where name = 'wangsi';

事务二：

begin;
update teacher set teacher_no = 'T2010006' where name = 'wangsi';

发现卡住了：

事务一提交了，事务二才获取了。

说明锁的是表！

（3）加索引，修改同一行记录，不行

事务一：

begin; 

update teacher set teacher_no = 'T2010005' where name = 'wangsi';

事务二：

begin; 
update teacher set teacher_no = 'T2010006' where name = 'wangsi';

发现卡住了：

事务一提交了，事务二才获取了。

（4）加索引，修改同表的不同行，可以修改

事务一：

begin;
update teacher set teacher_no = 'T2010008' where name = 'wangsi';

事务二：

begin; 
update teacher set teacher_no = 'T2010009' where name ='jiangsi';

发现都可一顺利修改，说明锁的的确是行。证明行行锁是加在索引上的，这是标准的行级锁。

2、间隙锁（GAP Lock）

在RR这个级别下 ，为了避免幻读，引入了间隙锁，他锁定的是记录范围，不包含记录本身，也就是不允许在范围内插入数据。

查看隔离级别：

show variables like '%iso%';

第一步把teacher表的id的4改成8

事务一：

begin;
select * from teacher where id < 6 lock in share mode;

事务二：

begin; 
insert into teacher values (5,'zhangnan','T888888');

发现卡住了，因为他会把小于6的数据锁定，并不允许间隙中间的值插入：

事务三：

begin;
insert into teacher values (9,'huijun','T66666666');

发现成功了，因为9不在锁定的范围

3、记录锁和间隙锁的组合（next-key lock）

是记录锁和间隙锁的组合，当InnoDb扫描索引时会先对索引记录加上记录锁，在对索记录两边加上间隙锁

二、表锁

1、对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个另特殊事务中，也可以考虑使用表级锁。

第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事

务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提

高该事务的执行速度。

第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

2、在InnoDB下 ，使用表锁要注意以下两点。

（１）使用LOCK TALBES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层ＭySQL Server负责的，仅当autocommit=0、innodb_table_lock=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，ＭySQL Server才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。

（２）在用LOCAK TABLES对InnoDB锁时要注意，要将AUTOCOMMIT设为0，否则ＭySQL不会给表加锁；事务结束前，不要用UNLOCAK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK不能释放用LOCAK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁，正确的方式见如下语句。

lock tables teacher write,student read; 
select * from teacher; 
commit; 
unlock tables;

表锁的力度很大，慎用。

三、InnoDB的锁类型

InnoDB的锁类型主要有读锁(共享锁)、写锁(排他锁)、意向锁和MDL锁。

1、读锁

读锁（共享锁，shared lock）简称S锁。一个事务获取了一个数据行的读锁，其他事务能获得该行对应的读锁但不能获得写锁，即一个事务在读取一个数据行时，其他事务也可以读，但不能对该数行增删改的操作。

简而言之：就是可以多个事务读，但只能一个事务写。

读锁有两种select方式的应用：

  1. 第一种是自动提交模式下的select查询语句，不需加任何锁,直接返回查询结果，这就是一致性非锁定读。

  2. 第二种就是通过select.... lock in share mode被读取的行记录或行记录的范围上加一个读锁,让其他事务可以读,但是要想申请加写锁,那就会被阻塞。

事务一：

begin; 
select * from teacher where id = 1 lock in share mode;

事务二：

begin;
update teacher set name = 'lucy2' where id = 1;

卡住了，说明加了锁了。

2、写锁

写锁，也叫排他锁，或者叫独占所，简称x锁。一个事务获取了一个数据行的写锁，其他事务就不能再获取该行的其他锁与锁优先级最高。写锁的应用就很简单了，有以下两种情况：

简而言之：就是只能有一个事务操作这个数据，别的事务都不行。

（1）一些DML语句的操作都会对行记录加写锁。

事务一：

begin; 
update teacher set name = 'lucy' where id = 1;

事务二：

begin;
update teacher set name = 'lucy2' where id = 1;

卡住了，说明加了锁了。

你发现他还能读，这是应为mysql实现了MVCC模型。

2、写锁

写锁，也叫排他锁，或者叫独占所，简称x锁。一个事务获取了一个数据行的写锁，其他事务就不能再获取该行的其他锁与锁优先级最高。

写锁的应用就很简单了，有以下两种情况：

简而言之：就是只能有一个事务操作这个数据，别的事务都不行。

（1）一些DML语句的操作都会对行记录加写锁。

事务一：

begin;
update teacher set name = 'lucy' where id = 1;

事务二：

begin;
update teacher set name = 'lucy2' where id = 1;

卡住了，说明加了锁了。

你发现他还能读，这是应为mysql实现了MVCC模型。

（2）比较特殊的就是select for update，它会对读取的行记录上加一个写锁，那么其他任何事务戴不能对被锁定的行上加任何锁了，要不然会被阻塞。

事务一：

begin; 
select * from teacher where id = 1 for update;

事务二：

begin;
update teacher set name = 'lucy2' where id = 1;

卡住了，说明加了锁了。

你发现他还能读，这是应为mysql实现了MVCC模型。

（2）比较特殊的就是select for update，它会对读取的行记录上加一个写锁，那么其他任何事务戴不能对被锁定的行上加任何锁了，要不然会被阻塞。

事务一：

begin;
select * from teacher where id = 1 for update;

事务二：

begin;
update teacher set name = 'lucy2' where id = 1;

卡住了，说明加了锁了。

你发现他还能读，这是应为mysql实现了MVCC模型。

3、MDL锁

MySQL 5.5引入了meta data lock，简称MDL锁，用于保证表中 元数据 的信息。在会话A中，表开启了查询事务后，会自动获得一个MDL锁，会话B就不可以执行任何DDL语句，不能执行为表中添加字段的操作，会用MDL锁来保证数据之间的一致性。

元数据就是描述数据的数据，也就是你的表结构。意识是在你开启了事务之后获得了意向锁，其他事务就不能更改你的表结构。

4、意向锁

在mysql的innodb引擎中，意向锁是表级锁，意向锁有两种

意向共享锁（IS） 是指在给一个数据行加共享锁前必须获取该表的意向共享锁

意向排它锁（IX） 是指在给一个数据行加排他锁前必须获取该表的意向排他锁

意向锁和MDL锁都是为了防止在事务进行中，执行DDL语句导致数据不一致。

从另一个角度区分锁的分类

1、乐观锁

乐观锁大多是基于数据版本记录机制实现，一般是给数据库表增加一个"version"字段。读取数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

比如下单操作：

查询出商品信息。

select name, version from teacher where id = 1;

根据商品信息生成订单。

将商品数量减1。

update teacher set name = 'lucy'，version = version + 1 where id = 1 and version = 3

2、悲观锁

总有刁民想害朕

悲观锁依靠数据库提供的锁机制实现。MySQL中的共享锁和排它锁都是悲观锁。数据库的增删改操作默认都会加排他锁，而查询不会加任何锁。此处不赘述。

五、锁等待和死锁

锁等待是指一个事务过程中产生的锁，其他事务需要等待上一个事务释放它的锁，才能占用该资源。如果该事务一直不释放，就需要持续等待下去，直到超过了锁等待时间，会报一个等待超时的错误。MysQL中通过innodb_lock_wait_timeout参数控制,单位是秒。

死锁的条件

  1. 两行记录，至少两个事务

  2. 事务A 操作 第n行数据，并加锁 update teacher set name = 'a' where id = 1;

  3. 事务B 操作 第m行数据，并加锁 update teacher set name = 'b' where id = 2;

  4. 事务A 操作 第m行数据 update teacher set name = 'c' where id = 2;

  5. 事务B 操作 第n行数据 update teacher set name = 'd' where id = 1;

  6. 形成死锁 Deadlock found when trying to get lock; try restarting transaction

死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，就是所谓的锁资源请求产生了回路现象，即死循环。

InnoDB引擎可以自动检测死锁并 回滚该事务 好不容易执行了一个业务给我回滚了，所以死锁尽量不要出现。

六、如何避免死锁

  1. 出现死锁并不可怕，但我们要尽量避免死锁

  2. 如果不同的程序会并发处理同一个表，或者涉及多行记录，尽量约定使用相同顺序访问表，可以大大减少死锁的发生。

  3. 业务中尽量采用小事务，避免使用大事务，要即使提交和回滚事务，可减少死锁产生的概率。

  4. 同一个事务中尽量做到一次锁定所需要的所有资源，减少死锁发生的概率。

  5. 对于非常容易发生死锁的业务，可以尝试使用升级锁的力度，该用表锁减少死锁的发生。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/jinronga/p/14579264.html