Mysql精华总结05——Mysql锁机制

七、MySQL锁机制

 锁是计算机协调多个进程或线程并发访问某一资源的机制。
 在数据库中,除传统的计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供许多用户共享的资源。数据库锁机制简单来说,就是数据库为了保证数据的一致性,而使各种共享资源在被并发访问时变得有序所设计的一种规则。
 打个比方,我们到淘宝上买一件商品,商品只有一件库存,这个时候如果还有另一个人买,那么如何解决是你买到还是另一个人买到的问题?这里肯定要用到事物,我们先从库存表中取出物品数量,然后插入订单,付款后插入付款表信息,然后更新商品数量。在这个过程中,使用锁可以对有限的资源进行保护,解决隔离和并发的矛盾。

 7.1 锁的分类

  从对数据操作的类型分类:
  读锁(共享锁):针对同一份数据,多个读操作可以同时进行,不会互相影响;
  写锁(排他锁):当前写操作没有完成前,它会阻断其他写锁和读锁;
  从对数据操作的粒度分类:为了尽可能提高数据库的并发度,每次锁定的数据范围越小越好,理论上每次只锁定当前操作的数据的方案会得到最大的并发度,但是管理锁是很耗资源的事情(涉及获取,检查,释放锁等动作),因此数据库系统需要在高并发响应和系统性能两方面进行平衡,这样就产生了“锁粒度(Lock granularity)”的概念。
  表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低(MyISAM 和 MEMORY 存储引擎采用的是表级锁);
  行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高(InnoDB 存储引擎既支持行级锁也支持表级锁,但默认情况下是采用行级锁);
  页级锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
  应用:从锁的角度来说,表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用;而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。

行锁表锁页锁
MyISAM
BDB
InnoDB
Memory
 7.2 MyISAM 表锁

  MyISAM 的表锁有两种模式:
  表共享读锁 (Table Read Lock):不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;
  表独占写锁 (Table Write Lock):会阻塞其他用户对同一表的读和写操作;
  MyISAM 表的读操作与写操作之间,以及写操作之间是串行的。当一个线程获得对一个表的写锁后, 只有持有锁的线程可以对表进行更新操作。其他线程的读、 写操作都会等待,直到写锁被释放为止。
  默认情况下,写锁比读锁具有更高的优先级:当一个锁释放时,这个锁会优先给写锁队列中等候的获取锁请求,然后再给读锁队列中等候的获取锁请求。

 7.3 InnoDB 行锁

  InnoDB 实现了以下两种类型的行锁:
  共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
  排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。
  为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB 还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁:
  意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
  意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。
  索引失效会导致行锁变表锁。比如 varchar 查询不写单引号的情况。

 7.4 加锁机制

  乐观锁与悲观锁是两种并发控制的思想,可用于解决丢失更新问题。
  乐观锁会“乐观地”假定大概率不会发生并发更新冲突,访问、处理数据过程中不加锁,只在更新数据时再根据版本号或时间戳判断是否有冲突,有则处理,无则提交事务。用数据版本(Version)记录机制实现,这是乐观锁最常用的一种实现方式。
  悲观锁会“悲观地”假定大概率会发生并发更新冲突,访问、处理数据前就加排他锁,在整个数据处理过程中锁定数据,事务提交或回滚后才释放锁。另外与乐观锁相对应的,悲观锁是由数据库自己实现了的,要用的时候,我们直接调用数据库的相关语句就可以了。

 7.5 锁模式(InnoDB三种行锁的算法)

  记录锁(Record Locks):单个行记录上的锁。索引项加锁,锁定符合条件的行。其他事务不能修改和删除加锁项;

SELECT * FROM table WHERE id = 1 FOR UPDATE;

  它会在 id=1 的记录上加上记录锁,以阻止其他事务插入,更新,删除 id=1 这一行。
  在通过主键索引或唯一索引对数据行进行 UPDATE 操作时,会自动对该行数据加记录锁:

-- id 列为主键列或唯一索引列
UPDATE table SET age = 50 WHERE id = 1;

  间隙锁(Gap Locks)当我们使用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录,叫做“间隙”。InnoDB 也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁。
  对索引项之间的“间隙”加锁,锁定记录的范围(对第一条记录前的间隙或最后一条将记录后的间隙加锁),不包含索引项本身。其他事务不能在锁范围内插入数据,这样就防止了别的事务新增幻影行,避免了幻读的问题。
  间隙锁基于非唯一索引,它锁定一段范围内的索引记录。间隙锁基于下面将会提到的Next-Key Locking 算法,请务必牢记:使用间隙锁锁住的是一个区间,而不仅仅是这个区间中的每一条数据。

SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;

  即所有在(1,10)区间内的记录行都会被锁住,所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞,但是 1 和 10 两条记录行并不会被锁住。
  GAP锁(即间隙锁)的目的,是为了防止同一事务出现幻读的情况。
  临键锁(Next-key Locks):临键锁,是记录锁与间隙锁的组合,它的封锁范围,既包含索引记录,又包含索引区间。(临键锁的主要目的,也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC,临键锁也会失效。)
  Next-Key 可以理解为一种特殊的间隙锁,也可以理解为一种特殊的算法。通过临键锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁,当某个事务持有该数据行的临键锁时,会锁住一段左开右闭区间的数据。需要强调的一点是,InnoDB 中行级锁是基于索引实现的,临键锁只与非唯一索引列有关,在唯一索引列(包括主键列)上不存在临键锁。
  对于行的查询,都是采用该方法,主要目的是解决幻读的问题。
  问题1:select for update有什么含义,会锁表还是锁行还是其他?
  for update 仅适用于InnoDB,且必须在事务块(BEGIN/COMMIT)中才能生效。在进行事务操作时,通过“for update”语句,MySQL会对查询结果集中每行数据都添加排他锁,其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁。
  InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!假设有个表单 products ,里面有id和name二个栏位,id是主键,会有以下几种情况:
  ①明确指定主键,并且有此记录:row lock

SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and name='Cat' FOR UPDATE;

  ②明确指定主键,但查无此记录:无lock

SELECT * FROM products WHERE id='-1' FOR UPDATE;

  ③无主键:table lock

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

  ④主键不明确:table lock

SELECT * FROM products WHERE id<> '3' FOR UPDATE;
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;
 7.6 死锁

  死锁的产生:
  死锁是指两个或多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环。
  当事务试图以不同的顺序锁定资源时,就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁;锁的行为和顺序和存储引擎相关。以同样的顺序执行语句,有些存储引擎会产生死锁有些不会——死锁有双重原因:一真正的数据冲突;二存储引擎的实现方式。
  死锁检测:数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。
  死锁恢复死锁发生以后,只有部分或完全回滚其中一个事务,才能打破死锁,InnoDB目前处理死锁的方法是,将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁,多数情况下只需要重新执行因死锁回滚的事务即可。
  外部锁的死锁检测:发生死锁后,InnoDB 一般都能自动检测到,并使一个事务释放锁并回退,另一个事务获得锁,继续完成事务。但在涉及外部锁,或涉及表锁的情况下,InnoDB 并不能完全自动检测到死锁, 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。
  死锁影响性能:死锁会影响性能而不是会产生严重错误,因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上,当许多线程等待同一个锁时,死锁检测可能导致速度变慢。有时当发生死锁时,禁用死锁检测(使用innodb_deadlock_detect配置选项)可能会更有效,这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

 7.6.1 MyISAM避免死锁

  在自动加锁的情况下,MyISAM 总是一次获得 SQL 语句所需要的全部锁,所以 MyISAM 表不会出现死锁。

 7.6.2 InnoDB避免死锁

  InnoDB中以下情况可能产生或避免死锁:
  ①为了在单个InnoDB表上执行多个并发写入操作时避免死锁,可以在事务开始时通过为预期要修改的每个元组(行)使用SELECT … FOR UPDATE语句来获取必要的锁,即使这些行的更改语句是在之后才执行的。
  ②在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请共享锁、更新时再申请排他锁,因为这时候当用户再申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁。
  ③如果事务需要修改或锁定多个表,则应在每个事务中以相同的顺序使用加锁语句。在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会
  ④通过SELECT … LOCK IN SHARE MODE获取行的读锁后,如果当前事务再需要对该记录进行更新操作,则很有可能造成死锁。
  ⑤改变事务隔离级别可以避免死锁
  如果出现死锁,可以用 show engine innodb status;命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息,如引发死锁的 SQL 语句,事务已经获得的锁,正在等待什么锁,以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值