mysql行锁和表锁怎么实现,说一下 mysql 的行锁和表锁,Mysql锁机制之行锁、表锁、死锁的实现

本文主要介绍了Mysql锁机制的实现，如行锁、表锁和死锁。通过实例代码详细介绍，具有一定的参考价值。感兴趣的朋友可以参考一下。

一、Mysql锁是什么？锁有哪些类别？

锁定义：

同一时间同一资源只能被一个线程访问

在数据库中，除了传统计算资源的竞争(如CPU、I/O等。)，数据也是很多用户共享的资源。如何保证数据并发访问的一致性和有效性是所有数据库都必须解决的问题，锁冲突也是影响数据库并发访问性能的重要因素。

乐观锁用的最多的是数据的版本记录来反映版本，其实就是一个logo。

示例：更新测试集a=a-1，其中id=100，a为0；对应的版本是A字段。没有必要拥有名为版本的字段。要求有该字段，满足该条件时触发。

锁的分类：

从对数据操作的类型分法（读或写）

读锁(共享锁):对于同一数据，可以同时进行多个读操作，互不影响。

写锁(独占锁):在当前写操作完成之前，它会阻塞其他写锁和读锁。

从对数据操作的粒度分法

表级锁：表级锁是MySQL中粒度最大的锁，意味着锁定当前操作的整个表(MyISAM引擎默认为表级锁，只支持表级锁)。比如说更新一张10万表数据中的一条数据，在这条update没提交事务之前，其它事务是会被排斥掉的，粒度很大。

行级锁：行级锁是Mysql中最细的锁粒度，也就是说只锁当前操作的行(基于索引实现的，所以一旦某个加锁操作没有使用索引，那么该锁就会退化为表锁)。

页面级锁：页面级锁是MySQL中的一种锁，其锁定粒度介于行级锁和表级锁之间。它一次锁定一组相邻的记录。

从并发角度的分发--实际上乐观锁和悲观锁只是一种思想

悲观锁：保守(悲观)的是数据被外界修改(包括系统的其他当前事务和来自外部系统的事务)。因此，在整个数据处理过程中，数据是被锁定的。

乐观锁定：乐观锁定假设数据在一般情况下不会引起冲突，所以当数据被提交更新时，数据将被正式测试冲突。如果发现冲突，将返回错误消息并重试该业务。

其他锁：

Gap lock:在条件查询中，比如where id100，InnoDB会锁定满足条件的已有数据记录的索引条目；对于键值在条件范围内但不存在的记录，称为“GAP”。gap的目的是防止误读。

意向锁：意向锁分为意向共享锁(IS)和意向排他锁(IX)。有意锁的目的是指示事务正在锁定或将锁定表中的某一行。

二、行锁和表锁的区别

表级锁是MySQL中锁定粒度最大的一种锁，即锁定当前操作的整张表。很容易实现。最常用的MYISAM和INNODB支持表级锁定。

特点：开销小，加锁快；不会出现僵局；锁的粒度大，锁冲突的概率最高，并发性最低。

行级锁是Mysql中锁定粒度最细的一种锁，这意味着仅锁定当前行。行级锁可以大大减少数据库操作的冲突。锁定的粒度最小，但锁定的开销也最大。

特点：成本高，锁定慢；将出现死锁；锁的粒度最小，锁冲突的概率最低，并发性最高。

用途：InnoDB行锁是通过锁定索引上的索引项来实现的。InnoDB仅对通过索引条件检索数据使用行级锁，否则InnoDB将使用表锁。

在下面的update语句中，如果B是常规字段而不是索引列，那么行级锁将被更改为表级锁。

从测试集a=100更新，其中b=' 100

现在，让我们举一个实际的例子来看看innnodb是如何使用行锁的。

当前表中的数据：

首先打开两个session会话窗口，然后将mysql事务级别设置为未提交级别：

会话窗口：

会话窗口2:

其中，session 2的更新一直在运行，直到超时结束，或者session 1提交事务，才会结束。

可以通过' % innodb _ lock _ wait _ timeout% '这样的show变量查询当前mysql设置的锁超时时间，默认为50秒。

可以设置innodb _ lock _ wait _ timeout=60设置锁的超时时间。

当第一个会话提交时，第二个会话的update语句将成功执行。这意味着innodb使用了锁。

那我们怎么确定行锁被使用了呢？

摘要：当id=125在会话1中被更新时，数据被添加了锁，因此当id=125在会话2中再次被更新时，数据被锁定。这个锁添加了id=125的记录。这时候除了id=125之外的都可以成功，证明这个线锁是默认添加的。

三、InnoDB死锁概念和死锁案例

定义：当两个或两个以上的事务相互持有并请求锁，形成循环依赖关系时，就会发生死锁。当多个事务同时锁定同一资源时，也会发生死锁。在事务系统中，死锁确实存在，而且无法完全避免。

解决方案：InnoDB将自动检测事务死锁，立即回滚其中一个事务，并返回一个错误。它根据某种机制选择最简单(成本最低)的事务进行回滚。

死锁场景一之select for update:

生成场景：两个事务都有两个select for update。事务A先锁定记录1，然后锁定记录2；事务b首先锁定记录2，然后锁定记录1。

写：更新，读：我的共享模式显示引擎innodb状态

验证死锁情况：

步骤1更新会话1:

开始交易；

select * from wnn_test其中a=199进行更新；

步骤2更新会话2:

开始交易；

select * from wnn_test其中a=101用于更新；

步骤3更新会话1:

select * from wnn_test其中a=101用于更新；

步骤4:更新会话2；

select * from wnn_test其中a=199进行更新；

当更新到第三和第四步时，出现了死锁。

看一下执行日志：

show engine innodb status;上次锁的时间、锁的表、导致锁的语句。其中会话1被锁定14秒(活动14)，会话2被锁定10秒(活动10)。

死锁场景二之两个update

生成场景：两个事务有两次更新，事务a先更新记录1，然后更新记录2；事务b首先更新记录2，然后更新记录1。

生成日志：

注意:仔细看上面两个例子，可以发现一个现象。当两个资源被锁定时，第三个会成功执行，但第四个会提示死锁。在mysql5.7中，当第三个被执行时，它将一直处于运行状态。这篇博文用的是mysql8.0，其中这个参数innodb_deadlock_detect可以用来控制InnoDB是否进行死锁检测，当启用了死锁检测时（默认设置），InnoDB 自动执行事务的死锁检测，并且回滚一个或多个事务以解决死锁。InnoDB 尝试回滚更小的事务，事务的大小由它所插入、更新或者删除的数据行数决定。。

那么，这个innodb_deadlock_detect参数到底该不该启用呢？

对于高并发的系统，当大量线程等待同一个锁时，死锁检测可能会导致性能的下降。此时，如果死锁检测被禁用，那么在发生死锁时，依靠参数innodb_lock_wait_timeout来执行事务回滚可能会更有效。

通常来说，应该启用死锁检测，并尽量避免应用出现死锁，并对死锁进行相应的处理，比如重启事务。

只有在确认死锁检测会影响系统性能，并且禁用死锁检测不会产生负面影响时，才可以尝试关闭innodb_deadlock_detect选项。另外如果禁用了 InnoDB 死锁检测，需要调整参数 innodb_lock_wait_timeout 的值满足实际需求。

四、程序开发过程中应该如何注意避免死锁

锁定的本质是资源互相竞争，互相等待，往往是因为锁定两个(或多个)会话的顺序不一致。

如何有效避免：

在程序中，操作多张表时，尽量以相同的顺序来访问（避免形成等待环路）

批量操作单张表数据的时候，先对数据进行排序（避免形成等待环路） A线程 id：1 ,10 ,20按顺序加锁 B线程id:20,10,1 这种的话就容易锁。

如果可以，大事务化成小事务，甚至不开启事务 select for update==insert==update = insert into update on duplicate key

尽量使用索引访问数据，避免没有 where 条件的操作，避免锁表有走索引是记录行锁，没走索引是表锁

使用等值查询而不是范围查询查询数据，命中记录，避免间隙锁对并发的影响 1，10，20 等值where id in (1,10,20) 范围查询 id1 and id20

避免在同一时间点运行多个对同一表进行读写的脚本，特别注意加锁且操作数据量比较大的语句；我们经常会有一些定时脚本，避免它们在同一时间点运行

关于Mysql锁机制的行锁、表锁和死锁的实现，本文到此结束。有关Mysql行锁、表锁和死锁的更多信息，请搜索我们以前的文章或继续浏览下面的相关文章。希望大家以后能多多支持我们！

郑重声明：本文由网友发布，不代表盛行IT的观点，版权归原作者所有，仅为传播更多信息之目的，如有侵权请联系，我们将第一时间修改或删除，多谢。