本文主要介绍了mysql锁机制及其用法，并详细分析了MySQL锁机制的分类、原理及相关技巧。有需要的可以参考一下。

本文阐述了MySQL锁的机制和用法。分享给你，供你参考，如下：

MySQL的锁机制比较简单，它最显著的特点就是不同的存储引擎支持不同的锁机制。例如，MyISAM和内存存储引擎使用表级锁；BDB存储引擎使用页锁，但它也支持表级锁。InnoDB存储引擎支持行级锁和表级锁，但默认采用行级锁。

MySQL这3种锁的特性可大致归纳如下：

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

（2）行级锁:成本高，锁定慢；将出现死锁；锁的粒度最小，锁冲突的概率最低，并发性最高。

（3）页面锁:开销和锁时间介于表锁和行锁之间；将出现死锁；锁的粒度介于表锁和行锁之间，并发性一般。

仅从锁的角度来看，表级锁更适合以查询为主，只根据索引条件更新少量数据的应用，比如Web应用；行级锁更适合根据索引条件和并发查询对少量不同数据进行大量并发更新的应用，比如一些在线事务处理系统。

一、MyISAM表锁

1. 查询表级锁争用情况

显示类似“表%”的状态；

如果table_locks_waited的值很高，则存在严重的表级锁争用。

2. MySQL表级锁的锁模式

MySQL中的表级锁有两种模式：表共享读锁和表独占写锁。

当会话向表添加读锁时，会话只能访问锁定的表，并且只能读；其他会话可以读取该表，但是写入将被阻止，并且需要等待锁的释放。当会话向表添加写锁时，会话只能访问锁定的表，并且可以执行读写操作。其他会话对此表的读写操作将被阻止，需要释放锁。

MyISAM表在读取和写入操作之间以及写入操作之间是串行的。

3. 如何加表锁

添加读锁：

锁表tbl _ name read

添加锁：

锁表tbl_name写；

松开锁：

解锁表格；

在执行查询语句之前，MyISAM会自动将读锁添加到所有相关的表中，并在执行更新操作之前将写锁添加到相关的表中。这个过程不需要用户干预，因此用户通常不需要直接使用LOCK TABLE命令显式锁定MyISAM表。显式锁定MyISAM表一般用于在一定程度上模拟事务操作，实现在某个时间点对多个表的一致读取。

注意，使用锁表时，不仅需要一次锁定所有使用的表，而且，同一表在SQL语句中出现多少次，就要用SQL语句中相同的别名锁定多少次，否则会出错！

4. 并发插入

MyISAM存储引擎有一个系统变量concurrent_insert，专门用来控制其并发插入行为。其值可以分别为0、1或2。

(1)当concurrent_insert设置为0时，不允许并发插入。

(2)当concurrent_insert设置为1时，如果MyISAM表中没有空洞(即表中间没有删除的行)，MyISAM允许一个进程读取表，而另一个进程从表的末尾插入记录。这也是MySQL的默认设置。

(3)当concurrent_insert设置为2时，无论MyISAM表中是否有空洞，都允许在表的末尾并发插入记录。

只需在表锁命令中添加“local”选项，即：锁表tbl_name local read。当满足MyISAM表并发插入条件时，其他用户可以在表的末尾并发插入记录，但是更新操作将被阻止，并且被锁定的用户不能访问其他用户并发插入的记录。

5. MyISAM锁调度

当编写器和读取器同时请求同一个MyISAM表的写锁和读锁时，编写器将首先获得锁。而且，即使读请求先到达锁等待队列，写请求后到达，写锁也会在读锁请求之前插入！这是因为MySQL认为写请求一般比读请求更重要。这也是MyISAM表不适合有大量更新操作和查询操作的应用的原因，因为大量的更新操作会使查询操作很难获得读锁，可能会永远阻塞。

通过一些设置调整MyISAM的调度行为：

(1)通过指定启动参数low-priority-updates，默认情况下，MyISAM引擎优先考虑读请求。

(2)通过执行命令集LOW_PRIORITY_UPDATES=1，降低该连接发送的更新请求的优先级。

(3)通过指定它们的LOW_PRIORITY属性来降低INSERT、UPDATE和DELETE语句的优先级。

(4)为系统参数max_write_lock_count设置适当的值。当一个表的读锁达到这个值时，MySQL会暂时降低写请求的优先级，给读进程一定的机会获取锁。

二、InnoDB锁问题

1. 查询InnoDB行锁争用情况

显示类似“innodb_row_lock%”的状态；

如果InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值很高，则锁争用很严重。此时，您可以设置InnoDB监视器来进一步观察表格、数据行等。发生锁冲突的地方，并分析锁争用的原因。

打开监视器：

创建表INNODB _ monitor(a INT)ENGINE=INNODB；

显示innodb状态\ G；

停止监视器：

删除表innodb _ monitor

打开监视器后，默认情况下，每隔15秒就会将被监视的内容记录到日志中。如果它被打开了很长时间。err文件将变得非常大。因此，在确认问题原因后，用户应该记得删除监控表以关闭监控器，或者使用“- console”选项启动服务器以关闭日志文件。

2. InnoDB的行锁及加锁方法

InnoDB中有两种行锁：共享锁(S)和排他锁(X)。为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还拥有两个内部意向锁：意向共享锁和意向排他锁，两者都是表锁。在事务锁定数据行之前，它必须首先获取对应于相应表的方向锁。

InnoDB会自动添加意向锁，无需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给所涉及的数据集添加排他锁(x)；InnoDB不会给普通的SELECT语句添加任何锁；通过以下语句，事务可以显式地向记录集添加共享锁或排他锁。

设置自动提交=0；

共享锁：

SELECT * FROM table_name其中.锁定共享模式

独占锁(x个):

SELECT * FROM table_name其中.用于更新

松开锁：

解锁表格；

(将隐式提交事务)

当一个事务获得一个表的共享锁时，其他事务可以查询该表的记录或将共享锁添加到记录中。当一个事务更新一个表时，如果另一个事务向该表添加了一个共享锁，则需要等待该锁被释放。如果另一个事务也更新表，会导致死锁，另一个事务退出，当前事务完成更新操作。当一个事务获得一个表的独占锁时，其他事务只能查询该表的记录，不能添加共享锁或更新记录，所以会有等待。

3. InnoDB行锁实现方式

InnoDB行锁是通过锁定索引上的索引项来实现的。InnoDB的行锁实现特性意味着：

(1)只有按索引条件检索数据时，InnoDB才会使用行级锁；否则，InnoDB将使用表锁。

(2)因为MySQL的行锁是针对索引的，不是针对记录的，虽然访问的是不同行的记录，但是如果使用相同的索引键，就会出现锁冲突。

(3)当一个表有多个索引时，不同的事务可以使用不同的索引来锁定不同的行。此外，InnoDB将使用行锁来锁定数据，无论是使用主键索引、唯一索引还是公共索引。(尽管使用了不同的索引，但如果记录已被其他会话锁定，则需要等待。)

(4)即使在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的成本来决定的。如果MySQL认为全表扫描效率更高，比如对于一些非常小的表，就不会使用索引。在这种情况下，InnoDB将使用表锁而不是行锁。

4. 间隙锁

当使用范围条件检索数据时，InnoDB还会锁定键值在条件范围内但不存在的记录。这种锁叫做“缝隙锁”。InnoDB使用间隙锁一方面是为了防止幻读，另一方面是为了满足恢复和复制的需要。但是这种锁机制会阻塞合格范围内键值的并发插入，导致严重的锁等待，所以要尽量避免使用范围条件来检索数据。

除了在按范围条件锁定时使用gap lock之外，InnoDB还会在通过使用equal条件请求锁定一个不存在的记录时使用gap lock！

5. 恢复和复制的需要对InnoDB锁机制的影响

MySQL BINLOG记录INSERT、UPDATE、DELETE等成功的SQL语句来更新数据，从而实现MySQL数据库的恢复和主从复制。Mysql的恢复机制(复制实际上是从MySQL中连续的基于BINLOG的恢复)具有以下特征：

(1)1)MySQL的恢复是在SQL语句层面，即重新执行BINLOG中的SQL语句。

(2)2)MySQL的Binlog按照事务提交的顺序记录，恢复也是按照这个顺序进行的。

因此，MySQL恢复和复制对锁定机制的要求是：在一个事务提交之前，其他并发事务不能插入任何符合其锁定条件的记录，即不允许幻影读取。

另外，对于一般的select语句，MySQL使用多个版本的数据来实现一致性，不添加任何锁。但是，对于SQL语句，如“insert into target _ tab select * from source _ tab where .”和“创建表格new _ tab.挑选.from source_tab where . "，用户没有对source _ tab做任何更新操作，但是MySQL对这条SQL语句做了特殊处理，给source _ tab增加了一个共享锁。这是因为，在没有锁定的情况下，如果另一个事务在执行此SQL语句期间更新source_tab并首先提交它，那么更新操作的位置将在BINLOG中的SQL语句之前。如果使用此BINLOG恢复数据库，恢复的结果将与实际的应用程序逻辑不一致，并且复制将导致主数据库和从数据库之间的不一致。实际上，插入target_tab或new_tab的数据是source_tab被另一个事务更新之前的数据，而BINLOG首先记录更新，然后执行select.插入.声明。如果上述语句的SELECT是一个范围条件，InnoDB还将向源表添加一个间隙锁。因此，这个SQL语句会阻塞原始表的并发更新，应该尽可能避免。

6. InnoDB使用表锁的情况及注意事项

对于InnoDB表，大多数情况下应该使用行级锁，但在个别特殊事务中，也可以考虑使用表级锁，主要有以下两种情况：

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

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

另外，在InnoDB中使用表锁时需要注意以下两点：

(1)虽然可以使用锁表在InnoDB中添加表级锁，但是表锁不是由InnoDB存储引擎层管理的，而是由它的上级层MySQL Server管理的。只有当autocommit=0，InnoDB_table_locks=1(默认设置)时，InnoDB层才能知道MySQL添加的表锁，MySQL Server也能感知InnoDB添加的行锁。在这种情况下，innodb添加的表锁可以被MySQL服务器检测到。否则，InnoDB将无法自动检测和处理这个死锁。

(2)用锁表锁InnoDB表时，需要注意的是AUTOCOMMIT要设置为0，否则MySQL不会锁表；在事务结束之前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES隐式地提交了事务。COMMIT或ROLLBACK不能释放添加了锁表的表级锁，但必须使用UNLOCK TABLES来释放表锁。

7. 关于死锁

MyISAM表锁是无死锁的，因为MyISAM总是一次获得所有需要的锁，要么满足所有锁，要么等待，所以不会有死锁。但是在InnoDB中，除了由单个SQL组成的事务外，锁都是逐步获取的，这就决定了在InnoDB中死锁是可能的。

死锁发生后，InnoDB通常可以自动检测到它，并使一个事务释放锁并回滚，而另一个事务获取锁并继续完成事务。但是，当涉及外部锁或表锁时，InnoDB无法自动检测死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。

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

(1)在应用中，如果不同的程序会并发访问多个表，那么应该尽可能约定以相同的顺序访问这些表，这样可以大大降低死锁的几率。

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

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

(4)在REPEATABLE-READ隔离级别下，如果两个线程同时向同一个条件记录添加排他锁，如果没有满足条件的记录，两个线程都将成功锁定。当程序发现记录尚不存在时，它会尝试插入一条新记录。如果两个线程都这样做，就会出现死锁。在这种情况下，将隔离级别更改为READ COMMITTED可以避免该问题。

(5)当隔离级别是读提交时，如果两个线程都执行SELECT.对于UPDATE first，判断是否有符合条件的记录，如果没有，插入该记录。此时，只有一个线程可以成功插入，另一个线程将等待锁。当第一个线程提交时，第二个线程将由于重复的主键而出错，但是尽管这个线程出错了，它将获得一个独占锁！此时，如果第三个线程再次申请独占锁，也会出现死锁。在这种情况下，您可以直接执行插入操作，然后捕获主键权重异常，或者在遇到主键权重错误时总是执行ROLLBACK来释放获得的独占锁。

对更多MySQL相关内容感兴趣的读者可以查看我们的专题：《MySQL数据库锁相关技巧汇总》、《MySQL存储过程技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》和《MySQL事务操作技巧汇总》。

希望这篇文章对大家的MySQL数据库有所帮助。

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