锁怎么读 Mysql有多少种锁 怎么写加锁的SQL语句

原标题:Mysql有多少种锁，如何编写锁定的sql语句

什么是数据库锁

简单来说，数据库锁定机制就是数据库在并发访问时，为保证数据的一致性，使各种共享资源有序进行而设计的规则。对于任何一种数据库，都需要有相应的锁定机制，MySQL也不例外。由于MySQL数据库的特点，数据存储引擎很多，每个存储引擎的特点都不一样。为了满足他们特定应用场景的需求，每个存储引擎的锁定机制都是针对他们所面临的特定场景进行优化的，所以每个存储引擎的锁定机制也有很大的不同。MySQL存储引擎使用三种类型的锁定机制:表级锁定、行级锁定和页级锁定。

表级锁定

表级锁定是MySQL存储引擎中最大粒度的锁定机制。这种锁定机制最大的特点是实现逻辑非常简单，对系统的负面影响最小。因此，获取和释放锁的速度非常快。因为表级锁可以一次锁定整个表，所以可以避免困扰我们的死锁问题。

当然，锁定粒度大带来的最大负面影响是锁定资源争用的概率会最高，从而产生丰厚的折扣。

表级锁定主要被MyISAM、MEMORY、CSV等非事务性存储引擎使用。

行级锁定

行级锁定最大的特点是锁定对象的粒度很小，这也是目前各大数据库管理软件实现的最小锁定粒度。因为锁定粒度很小，锁定资源争用的概率也最小，可以给应用尽可能多的并发处理能力，提高一些需要高并发的应用的整体性能。

虽然它在并发处理能力上有很大的优势，但行级锁定也带来了很多缺点。因为锁定资源的粒度很小，每次需要做更多的事情来获取和释放锁，这自然会导致更大的消耗。此外，行级锁定也是最容易出现死锁的。

InnoDB存储引擎主要使用行级锁定。

页面级锁定

页面级锁定是MySQL中特有的锁定级别，在其他数据库管理软件中并不是很常见。页级锁定的特点是锁定粒度介于行级锁定和表级锁定之间，因此获得锁定所需的资源开销和提供的并发处理能力也介于这两者之间。此外，页级锁定和行级锁定一样，会导致死锁。

在数据库资源锁定过程中，随着锁定资源粒度的降低，锁定相同数量的数据所需的内存量也在增加，实现算法也会越来越复杂。但是随着锁定资源粒度的降低，应用访问请求遇到锁等待的可能性也会降低，系统整体并发性也会增加。

页面级锁定主要由BerkeleyDB存储引擎使用。

三种锁的区别

表级锁:开销低，锁定快；不会出现僵局；锁定粒度大，锁定冲突概率最高，并发性最低；

行级锁:开销大，锁定慢；将出现死锁；锁定粒度最小，锁定冲突概率最低，并发性也最高；

页锁:成本和锁定时间介于表锁和行锁之间；将出现死锁；锁定粒度介于表锁和行锁之间，并发性一般。

适用性:从锁的角度来看，表级锁更适合以查询为主，只根据索引条件更新数据的应用，比如Web应用；行级锁更适用于具有大量根据索引条件并发更新的不同数据，并且还具有并发查询的应用程序，例如一些在线事务处理系统。

表级锁定

由于MyISAM存储引擎使用的锁定机制完全是通过MySQL提供的表级锁定来实现的，下面我们就以MyISAM存储引擎为例说明存储引擎。

1.锁定模式为1。MySQL表级锁

MySQL中表级锁有两种模式:表共享读锁和表独占写锁。锁定模式的兼容性:

读取MyISAM表不会阻止其他用户对同一表的读取请求，但会阻止对同一表的写入请求。

写入MyISAM表将阻止其他用户读写同一个表。

MyISAM表的读写操作和写操作是串行的。当线程获得表的写锁时，只有持有锁的线程才能更新表。其他线程的读写操作将一直等到锁被释放。

2.如何添加手表锁

MyISAM将在执行查询语句之前自动向所有涉及的表添加读锁，并在执行更新操作之前向涉及的表添加写锁。这个过程不需要用户干预。因此，用户一般不需要直接使用LOCK TABLE命令来显式锁定MyISAM表。

3.优化建议3。MyISAM表锁

对于MyISAM存储引擎，虽然使用表级锁定的额外成本小于实现行级锁定或页级锁定的额外成本，但锁定本身消耗的资源最少。但是由于锁定粒度的比较，锁定资源的争用会比其他锁定级别更多，会在很大程度上降低并发处理能力。因此，在优化MyISAM存储引擎的锁定问题时，关键是如何提高其并发性。因为锁定级别是不能改变的，所以我们需要尽可能缩短锁定时间，然后我们需要尽可能让可能并发的操作。

查询表级锁争用

MySQL中有两组特殊的状态变量来记录系统中锁资源的争用情况:

mysql >显示像“表%”这样的状态；

+ - + - +

|变量名|值|

+ - + - +

| Table _ locks _ immediate | 100 |

| Table _ locks _ waited | 11 |

+ - + - +

有两个状态变量记录MySQL内部表级的锁定，这两个变量描述如下:

Table_locks_immediate:生成表级锁的次数；

Table _ locks _ waited:由于表级锁争用而发生的等待次数；

这两个状态值是从系统开始记录的，当相应的事件发生时，数字将增加1。如果这里Table _ locks _ waited的状态值比较高，说明系统中表级锁的争用相当严重，所以需要进一步分析为什么锁资源争用比较多。

缩短锁定时间

如何尽可能缩短锁定时间？唯一的方法是使我们的查询执行时间尽可能短。

a)尽可能减少大型复杂查询，将复杂查询拆分成若干个小型查询分布；

b)尽可能建立高效的索引，使数据检索更快；

c)尽量使MyISAM存储引擎的表只存储必要的信息，控制字段类型；

以及d)通过利用适当的机会来优化MyISAM表数据文件。

分离可以并行进行

说到MyISAM的表锁，读写时互相阻塞，可能有人会认为MyISAM存储引擎的表只能完全序列化，没有办法并行化。不要忘记MyISAM的存储引擎还有一个非常有用的特性，那就是ConcurrentInsert的特性。

MyISAM存储引擎有一个参数选项来控制是否打开并发插入函数:concurrent_insert，可以设置为0、1或2。这三个值的详细信息如下:

Concurrent_insert=2，不管MyISAM表中是否有空孔，都允许在页脚同时插入记录；

Concurrent_insert=1。如果MyISAM表中没有空孔，MyISAM允许一个进程读取该表，而另一个进程从页脚插入记录。这也是MySQL的默认设置；

Concurrent_insert=0，不允许并发插入。

MyISAM存储引擎的并发插入特性可以用来解决应用程序中查询和插入同一个表的锁争用问题。例如，将concurrent_insert系统变量设置为2总是允许并发插入；同时定期在系统空空闲时间执行OPTIMIZE TABLE语句，整理空之间的碎片，恢复删除记录导致的中间空空洞。

合理利用阅读和写作优先权

MyISAM存储引擎的读写互相阻塞。那么，当一个进程请求MyISAM表的读锁，另一个进程请求同一个表的写锁时，MySQL是如何处理的呢？

答案是先拿到锁。此外，即使读请求首先到达锁等待队列，而写请求稍后到达，写锁也将在读锁请求之前插入。

这是因为MySQL的表级锁有不同的读写优先级。默认情况下，写的优先级高于读的优先级。

因此，如果我们可以根据我们系统环境的不同来决定读写的优先级:

通过执行命令SET LOW_PRIORITY_UPDATES=1，读取的优先级高于写入的优先级。如果我们的系统主要是基于读取，这个参数可以设置；如果主要是以写为主，就没必要设置了。

通过指定插入、更新和删除语句的低优先级属性来降低它们的优先级。

尽管上述方法要么是更新优先，要么是查询优先，但在查询相对重要的应用程序中，它们仍然可以用来解决读取锁定的严重等待问题。

此外，MySQL还提供了一种折中的方法来调整读写冲突，即为系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值时，MySQL会临时降低写请求的优先级，给读者一个获得锁的机会。

这里还应该强调的是，一些需要长时间运行的查询操作也将使编写过程挨饿。因此，在应用程序中，需要长时间运行的查询操作应该尽可能避免，不应该总是使用SELECT语句来解决问题，因为这种看似巧妙的SQL语句往往很复杂，执行起来需要很长时间。如果可能的话，可以通过使用中间表等措施对SQL语句进行“分解”，使查询的每一步都能在短时间内完成。如果复杂查询不可避免，应该安排在数据库空的空闲时间执行，比如可以安排一些常规统计在晚上执行。

行级锁定

行级锁定不是由MySQL本身实现的，而是由其他存储引擎实现的，比如知名的InnoDB存储引擎和MySQL的分布式存储引擎NDBCluster。考虑到行级锁定是由各个存储引擎实现的，具体实现方式也不一样，InnoDB是事务存储引擎中应用最广泛的存储引擎，所以这里我们主要分析InnoDB的锁定特性。

1.InnoDB锁定模式及实现机制

考虑到行级锁定是由各个存储引擎实现的，具体实现方式也不一样，InnoDB是事务存储引擎中应用最广泛的存储引擎，所以这里我们主要分析InnoDB的锁定特性。

一般来说，InnoDB的锁定机制与Oracle数据库有很多相似之处。InnoDB的行级锁也分为两种，共享锁和排他锁。为了使行级锁和表级锁在锁机制的实现中共存，InnoDB还使用了意向锁的概念，包括意向共享锁和意向排他锁。

当事务需要锁定它需要的资源时，如果共享锁正在锁定它需要的资源，它可以添加另一个共享锁，但不能添加排他锁。但是，如果您需要锁定的资源已被独占锁占用，则只能在锁释放资源后获取锁定的资源并添加自己的锁。意图锁的作用是，当事务需要获取资源锁时，如果它需要的资源已经被排他锁占用，事务可以在被锁行的表中添加一个合适的意图锁。如果需要共享锁，可以在表上添加一个有意的共享锁。如果需要在一行上添加一个独占锁，应该先在表上添加一个意向独占锁。可以同时存在多个有意共享锁，但只能同时存在一个有意排他锁。因此，可以说InnoDB的锁模式实际上可以分为四种:共享锁、排他锁、有意共享锁和有意排他锁。我们可以通过下表总结出以上四类场所的共存逻辑关系:

如果事务请求的锁模式与当前锁兼容，InnoDB会将请求的锁授予该事务。相反，如果两者不兼容，事务将等待锁被释放。

意向锁由InnoDB自动添加，无需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB将自动向所涉及的数据集添加排他锁:从表名中选择*其中...锁定共享模式

当排他锁不通过索引条件进行查询时，InnoDB确实使用了表锁，而不是行锁。

因为MySQL的行锁是索引的锁，而不是记录的锁，虽然它访问不同行的记录，但是如果使用相同的索引键，就会发生锁冲突。

当一个表有多个索引时，不同的事务可以使用不同的索引来锁定不同的行。此外，无论是使用主键索引、唯一索引还是普通索引，InnoDB都会使用行锁来锁定数据。

即使条件中使用了索引字段，是否使用索引来检索数据也是由MySQL通过判断不同执行计划的代价来决定的。如果MySQL认为扫描整个表效率更高，比如对于一些很小的表，就不会使用索引。在这种情况下，InnoDB将使用表锁而不是行锁。因此，在分析锁冲突时，不要忘记检查SQL执行计划，以确认索引是否被实际使用。

3.间隙锁

当我们使用范围条件而不是相等条件来检索数据并请求共享或排他锁时，InnoDB会锁定满足条件的现有数据记录的索引项；

键值在条件范围内但不存在的记录称为“间隙”。

示例:

如果emp表中只有101条记录，empid值为1，2，...，100，101，以下SQL:

mysql >从emp中选择*其中empid > 100进行更新；

这是一个范围条件的搜索。InnoDB不仅会锁定empid值为101的记录，还会锁定empid大于101的“缺口”。

InnoDB使用间隙锁的目的:

防止幻影读数，以满足相关隔离级别的要求。对于上面的例子，如果没有使用间隙锁，如果在其他事务中插入了empid大于100的任何记录，那么如果在这个事务中再次执行上面的语句，就会发生魔法读取；

以满足其恢复和复制需求。

显然，在使用range条件检索和锁定记录时，即使是一些不存在的键值也会被无辜地锁定，这使得在锁定过程中不可能在锁定的键值范围内插入任何数据。在某些情况下，这可能会对性能造成极大的损害。

除了gap lock对InnoDB业绩的负面影响外，按指数锁定的方式还有其他几大业绩风险:

当Query不能使用索引时，InnoDB会放弃使用行级锁定，转而使用表级锁定，导致并发性能下降。

当Query使用的索引不包含所有过滤条件时，数据检索中使用的索引键只需要的一些数据可能不属于Query结果集的行和列，但也会被锁定，因为gap lock锁定的是一个范围，而不是特定的索引键；

当查询使用索引定位数据时，如果它使用相同的索引键但访问不同的数据行，它将被锁定。

因此，在实际应用开发中，尤其是并发插入较多的应用中，要尽可能优化业务逻辑，尽可能使用相等条件访问更新的数据，避免使用范围条件。

特别是，除了在按范围条件锁定时使用间隙锁定外，InnoDB还在使用相等条件请求锁定不存在的记录时使用间隙锁定。

4.僵局

如上所述，MyISAM表锁是无死锁的，因为MyISAM总是一次获得所有需要的锁，要么满足全部，要么等待，所以不会出现死锁。但是，在InnoDB中，锁是一步一步获取的，由单个SQL组成的事务除外。当两个事务需要获取彼此持有的排他锁才能继续完成事务时，这种循环锁等待就是典型的死锁。

在InnoDB的事务管理和锁定机制中，有一种特殊的死锁检测机制，可以在系统发生死锁后的短时间内检测到死锁。当InnoDB在系统中检测到死锁时，InnoDB会在导致死锁来回滚动的两个事务中选择较小的事务，让另一个较大的事务成功完成。

那么，InnoDB用什么标准来确定交易规模呢？这个问题在MySQL官方手册中也有提及。事实上，InnoDB发现死锁后，会计算两个事务插入、更新或删除的数据量，以确定两个事务的大小。也就是说，一个公司变更的记录越多，它在僵局中回滚的次数就越少。

但需要注意的是，当死锁场景涉及多个InnoDB存储引擎时，InnoDB无法检测到死锁，因此只能通过锁定超时限制参数InnoDB_lock_wait_timeout来解决。

需要注意的是，这个参数不仅用于解决死锁问题。在高并发访问的情况下，如果大量事务因为无法立即获得所需的锁而被挂起，将占用大量的计算机资源，造成严重的性能问题，甚至拖跨数据库。我们可以通过设置适当的锁定等待超时阈值来避免这种情况。

一般来说，死锁都是应用程序设计中的问题。大多数死锁可以通过调整业务流程、数据库对象设计、事务大小和访问数据库的SQL语句来避免。以下是通过示例避免死锁的一些常见方法:

在应用中，如果不同的程序可以并发访问多个表，那么它们应该尽可能同意以相同的顺序访问这些表，这样可以大大减少死锁的机会。

程序批量处理数据时，如果提前对数据进行排序，保证每个线程以固定顺序处理记录，也可以大大降低死锁的可能性。

在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不是先申请共享锁，再在更新时申请排他锁，因为当用户申请排他锁时，其他事务可能已经获得了同一记录的共享锁，导致锁冲突甚至死锁。

在可重复读取隔离级别下，如果两个线程使用SELECT...对于同时对同一条件记录进行更新加排他锁，如果没有满足条件的记录，两个线程都将成功锁定。当程序发现记录还不存在时，它会尝试插入一条新记录。如果两个线程都这样做，就会发生死锁。在这种情况下，将隔离级别更改为READ COMMITTED可以避免这个问题。

当隔离级别为READ COMMITTED时，如果两个线程都执行SELECT...对于更新，首先判断是否有合格的记录，如果没有，插入记录。此时只有一个线程可以插入成功，另一个线程会等待锁。第一个线程提交后，第二个线程会因为主键而出错，但是这个线程虽然出错了，但是会得到一个独占锁。此时，如果第三个线程再次来申请独占锁，也会发生死锁。在这种情况下，您可以直接执行插入操作，然后捕获主键复制异常，或者在遇到主键复制错误时始终执行ROLLBACK来释放获得的排他锁。

5.何时使用表锁

对于InnoDB表，大多数情况下应该使用行级锁，因为事务和行锁往往是我们选择InnoDB表的原因。但是，在一些特殊事务中，您也可以考虑使用表级锁:

事务需要更新大部分或全部数据，表比较大。如果使用默认的行锁，这种事务不仅执行效率低，还可能导致其他事务的长期锁等待和锁冲突。在这种情况下，可以考虑使用表锁来提高这个事务的执行速度。

事务涉及多个表，比较复杂，可能造成死锁，导致大量事务回滚。在这种情况下，我们也可以考虑一次锁定事务涉及的表，这样可以避免死锁，减少事务回滚带来的数据库开销。

当然，这两种事务在应用中不应该太多，否则，我们应该考虑使用MyISAM表。

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

虽然LOCK TABLES可以向InnoDB添加表级锁，但必须注意的是，表锁不是由InnoDB存储引擎层管理的，而是由其上层& MySQL Server管理的。只有当自动提交=0，InnoDB _ table _ locks = 1时，InnoDB层才能知道MySQL添加的表锁。MySQL Server还可以感知InnoDB添加的行锁。在这种情况下，InnoDB可以自动识别涉及表级锁的死锁。否则，InnoDB无法自动检测和处理此类死锁。

用LOCK TABLES锁定InnoDB表时，注意将AUTOCOMMIT设置为0，否则MySQL不会锁定表；在事务结束之前，不要使用UNLOCK TABLES来释放表锁，因为UNLOCK TABLES将隐式提交事务；COMMIT或ROLLBACK无法释放使用LOCK TABLES添加的表级锁，但必须使用UNLOCK TABLES来释放表锁。正确的方法见下面的陈述:

例如，如果您需要写入表t1并从表t中读取，您可以执行以下操作:

设置自动提交= 0；

锁表t1写，t2读，...；

；

COMMIT

解锁表格；

6.InnoDB行锁优化建议

InnoDB存储引擎已经实现了行级锁，虽然锁机制实现中的性能损失可能高于表级锁，但在整体并发处理能力上远优于MyISAM的表级锁。当系统并发度高时，InnoDB的整体性能会比MyISAM有明显的优势。但是InnoDB的行级锁定也有其薄弱的一面。当我们使用不当时，InnoDB的整体性能可能不会比MyISAM高，甚至更差。

为了合理利用InnoDB的银行级锁定，扬长避短，必须做好以下工作:

a)尽量所有的数据检索都是通过索引完成的，避免InnoDB因为无法通过索引键锁定而升级到表级锁定；

b)合理设计索引，让InnoDB尽可能准确地锁定索引键，尽可能缩小锁定范围，避免造成不必要的锁定，影响其他查询的执行；

c)尽可能减少基于范围的数据检索的过滤条件，避免因间隙锁定带来的负面影响而锁定不应锁定的记录；

d)尽量控制事务的大小，减少锁定资源的数量和锁定时间的长度；

e)在业务环境允许的情况下，尽量使用较低级别的事务隔离，以降低MySQL因实现事务隔离级别而产生的额外成本。

由于InnoDB的行级锁定和事务性质，死锁肯定会发生。以下是一些降低死锁概率的常见建议:

a)尽可能以相同的访问顺序访问相似的业务模块，防止死锁；

b)在同一个事务中，尽量一次锁定所有需要的资源，减少死锁的概率；

c)对于容易出现死锁的业务部分，我们可以尝试使用升级锁粒度，通过表级锁来降低死锁的概率。

您可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁争用情况:

mysql >显示像“InnoDB_row_lock%”这样的状态；

+ - + - +

|变量名|值|

+ - + - +

| Innodb _ row _ lock _ current _ waits | 0 |

| InnoDB_row_lock_time | 0 |

| InnoDB_row_lock_time_avg | 0 |

| InnoDB_row_lock_time_max | 0 |

| InnoDB_row_lock_waits | 0 |

+ - + - +

InnoDB的行级锁定状态变量不仅记录了锁定等待的次数，还记录了总锁定时间、平均锁定时间和最大锁定时间。此外，还有一个非累积状态量，显示当前等待锁定的等待数量。每个状态量的描述如下:

InnoDB_row_lock_current_waits:当前等待的锁数；

InnoDB_row_lock_time:从系统启动到现在锁定的总时长；

InnoDB_row_lock_time_avg:平均等待时间；

InnoDB_row_lock_time_max:系统启动后等待最频繁时间所花费的时间；

InnoDB _ row _ lock _ waits:自系统启动以来等待的总次数；

五个状态变量主要是InnoDB_row_lock_time_avg、InnoDB_row_lock_waits和InnoDB_row_lock_time。尤其是在等待次数多、等待时间不小的情况下，我们需要分析系统中为什么会有这么多等待，然后根据分析结果开始指定优化方案。

如果发现锁争用比较严重，比如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，可以通过设置InnoDB Monitors进一步观察有锁冲突的表和数据行，分析锁争用的原因。

锁定冲突的表、数据行等。，并分析锁争用的原因。具体方法如下:

mysql >创建表InnoDB_monitor引擎= InnoDB

然后，您可以使用以下语句来查看:

mysql >显示引擎InnoDB状态；

监视器可以通过发出以下语句来停止查看:

mysql > drop table InnoDB _ monitor

设置监视器后，会有详细的当前锁等待的信息，包括表名、锁类型、锁定记录的情况等，便于进行进一步的分析和问题的确定。可能会有读者朋友问为什么要先创建一个叫InnoDB_monitor的表呢？因为创建该表实际上就是告诉InnoDB我们开始要监控他的细节状态了，然后InnoDB就会将比较详细的事务以及锁定信息记录进入MySQL的errorlog中，以便我们后面做进一步分析使用。打开监视器以后，默认情况下每15秒会向日志中记录监控的内容，如果长时间打开会导致.err文件变得非常的巨大，所以用户在确认问题原因之后，要记得删除监控表以关闭监视器，或者通过使用“--console”选项来启动服务器以关闭写日志文件。