MySQL数据库锁

  1. 数据库锁定机制就是防止多用户操作共享数据的时候数据的一致性问题,使用数据库锁可以使共享资源在被并发访问变得有序。MySQL数据库由于其自身架构的特点,存在多种存储引擎,每种存储引擎的锁定机制都是为各自所面对的特定场景优化设计。MySQL的各存储引擎使用了三种类型的锁定机制。
  2. MySQL数据库的三种锁
  • 表级锁定,是将对整张表进行锁定,它是MySQL存储引擎中最大颗粒度的锁定机制。它的实现逻辑特别简单,所以带来的负面影响很小,获取锁和释放锁都会很快。但是由于它是将整张表锁定,所以能很好的避免死锁问题。死锁就是两个或者两个以上的线程在争夺资源的过程中,使互相都无限进入等待。举个例子,线程A锁住了数据1等待数据2,线程B锁住了数据2等待数据1,那么这两个线程就会发生死锁。使用表锁定带来的负面影响就是,锁定颗粒大,导致锁定资源的争用也会增大,那么就会导致并发大打折扣。
  • 行级锁定是MySQL存储引擎中最小颗粒的锁定机制,因为锁定颗粒小,所以资源的竞争也最小,发生锁冲突的概率也最低,能更好的处理并发问题。
  • 页级锁定它是介于行级锁定和表级锁定之间的,开销适中,会发生死锁,处理并发能力也适中。
  1. 各种锁的实现
  • 表级锁定的实现,使用MyISAM存储引擎的锁定机制就是表级锁定。MySQL的表级锁定模式有两种,一种是表共享读锁,一种是表独占写锁。意思就是,当对MyISAM表进行读操作的时候不影响其他用户对该表的读操作,但是会阻塞其他用户对当前表的写操作。当对MyISAM表的写操作的时候,则会阻塞其他用户对当前表的读写操作。当使用的是MyISAM存储引擎,用户在执行查询也就是select操作的时候会自动对当前查询的所有的表都加上读锁。在执行update,delete,insert,操作之前会对操作的表都加上写锁。因此不用用户直接使用LOCK TABLE命令对MyISAM表手动加锁。因为表级锁定会造成资源的争夺激烈。所以要对MyISAM表进行优化。可以先查询当前表的资源争夺情况,通过使用show status like ‘table%’命令查询。会出现两个值,一个是代表当前表出现表级锁定的次数,这个一般不用管,另一个值是代表当前这个表发生的资源争夺而等待的次数,如果这个数值越高,代表当前表的资源争夺越激烈,就要进行表优化操作了。对于表级锁定的优化策略主要是缩短对表锁定的时间,对于复杂的sql语句尽量拆分成多个简单的sql从而让表的锁定时间变短。当数据量足够大的时候,可以创建索引从而让数据得查询速度更快,减少表的锁定时间。还可以控制字段类型,减少非必要信息的存放。
  • 行级锁定的实现,行级锁定并不是MySQL自己实现的锁定方式,而是由存储引擎来实现的,最常用的实现行级锁定的引擎就是InnoDB。InnoDB的锁定有两种,一种是共享锁,一种是排他锁。但是为了让了让行级锁和表级锁共存,InnoDB使用了意向共享锁和意向排他锁。
  • 这里就引出来了一个概念,如果没有意向锁,表级锁定和行级锁定能否共存的问题。假设现在A事务锁定了某一行,其他的事务就无法对锁定的该行进行操作,而表级锁定的概念是只要一个事务锁定了当前这个表,那么就可以对当前表进行操作。这与行级锁的概念相冲突。所以没有意向锁,表级锁定和行级锁定无法共存。
  • 那么表级锁定和行级锁定是如何共存的呢。同样是事务A申请锁定某一行进行写操作,在他锁定之前数据库会自动的给当前表加上意向排他锁,当其他事务去申请表的写锁的时候就会失败,因为表上有意向排他锁之后事务B申请写锁的时候就会失败。
  • 那么InnoDB的共享锁和排他锁是如何实现的呢,如果事务A要操作当前某一行的数据的时候,发现当前行被共享锁锁定了。那么事务A可以在当前行添加一个共享锁,但是不能添加排他锁。如果事务A要操作当前某一个行的数据的时候发现当前的数据被排他锁锁定。那么事务A之后等待当前行的排他锁释放之后才能操作当前行数据。
  • 那么意向锁的作用是什么呢,如果事务A操作当前某一行数据的时候,发现当前的数据被排他锁锁定了。那么该事务可以在当前行的表上面添加一个意向锁,如果当前事务需要的是共享锁,那么就在表上加意向共享锁,如果需要的排他锁,那么就在表上加上意向排他锁。
  • InnoDB存储引擎会自动的给当前事务加锁,如果当前事务执行的select操作那么就会给当前数据加上共享锁,如果是其他操作就会加上排他锁。当然也可以通过sql语句指定加锁,例如:
共享锁:select * from table_name where ... LOCK IN SHARE MODE;
排他锁:select * from table_name where ... for update;

  • InnoDB行锁是通过索引来实现的,只要是通过索引操作的数据都会给当前的数据加上行锁。如果不是使用索引来实现的,那么就会使用表锁。在平常使用中,必须要注意到这一特性,如果有大量的sql通过索引操作共享数据的时候,就会发生锁冲突,降低并发性。并且因为InnoDB是通过索引来加锁的,并不是根据记录来加锁的,所以只要使用相同的索引,那么就是会出现锁冲突的。有些情况MySQL并不会根据索引条件来查找数据的,比如说当前表的数据非常的少,那么MySQL会自动给当前表加表级锁定,而不会因为使用索引而加行级锁定。所以在sql执行的时候可以检查执行情况,看当前sql是否使用了索引。
  • 间隙锁,当在sql语句中使用的是范围检索的时候,并且请求共享锁或者排他锁的时候,就会给当前符合范围的已有的数据加锁。对于在当前范围内但是没有数据的记录,叫做间隙,InnoDB也会对当前这些记录加锁,又叫间隙锁,例如:
select * from table_name where id > 100 for update;
假设当前表的数据有1-101个,那么InnoDB不仅会对101这条数据加锁,还会对后续的102,103...等等加锁

  • InnoDB的作用,假如现在事务A执行上述的sql,事务B同时添加了第102个数据,那么就事务A就会发生幻读。有了间隙锁就可以防止这种情况的发生。但是因为有这种锁的存在也会引发一些弊端,比如当其他事务需要对102进行数据的插入操作,这就会非常影响性能。所以在进行sql优化的时候可以考虑此方面。
  • 除了InnoDB间隙锁带来的弊端,其他通过索引实现锁的方式也会存在以下弊端。一是当MySQL无法利用索引的时候就会使用表级锁定,那么就会是性能大大降低。二是上面说到的如果多sql使用相同的索引,也会发生锁冲突。
  1. 死锁
  • MyISAM表锁一般是不会发生死锁的,它总是一次性获得所有的锁,要么全部满足,要么全部等待。而InnoDB行锁容易发生死锁,假设现在有两个事务,这两个事务都需要等待对方的排他锁释放才能进行操作。这样就会造成两个事务无限等待下去,那么就发生死锁了。
  • InnoDB自己也有处理死锁的功能,它有专门检测死锁的机制,会在系统发生死锁之后的很短时间内检测到该死锁的存在,然后InnoDB会判断两个事务的大小,较大的会完成当前事务,较小的则直接回滚。这里判断事务的大小,是InnoDB会根据两个事务操作的数据的多少来判断的。也就是操作数据量大的事务会完成,较少的会回滚。
  • 当产生死锁的场景中不涉及到InnoDB存储引擎的时候,InnoDB是无法检测死锁的,这时候就只能通过锁定超时限定参数InnoDB_lock_wait_timeout来解决。当然在高并发的情况下,由于设置了锁定超时限定参数会导致大量事物因无法立即获得锁而挂起,这样就会造成资源的浪费性能损失,甚至拖垮数据库。
  • 通常避免数据库死锁的方法,尽可能的使用索引来完成数据的检索,以免无法通过索引加锁而升级为表级锁定。合理的设计索引,使InnoDB的锁定范围尽可能的小。尽量不适用范围的条件来检索数据,从而避免间隙锁带来的负面影响。尽可能使用相同的顺序来操作数据。还可以一次性锁定全部资源,避免发生死锁。在那种容易发生死锁的部分,将行级锁定升级为表级锁定,从而降低发生死锁的概率。

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×