共计 6092 个字符，预计需要花费 16 分钟才能阅读完成。
这篇文章主要介绍了 MySQL 锁机制有什么用，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让丸趣 TV 小编带着大家一起了解一下。
MySQL 的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM 和 MEMORY 存储引擎采用的是表级锁；BDB 存储引擎采用的是页面锁，但也支持表级锁；InnoDB 存储引擎既支持行级锁，也支持表级锁，但默认情况下采用行级锁。
MySQL 这 3 种锁的特性可大致归纳如下：
（1）表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
（2）行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
（3）页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
仅从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如 Web 应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理系统。
一、MyISAM 表锁
1. 查询表级锁争用情况
show status like table%如果 table_locks_waited 的值比较高，则说明存在着比较严重的表级锁争用情况。
2. MySQL 表级锁的锁模式
MySQL 的表级锁有两种模式：表共享读锁和表独占写锁。
当一个 session 对某个表加了读锁之后，该 session 只能访问加锁的这个表，而且只能进行读操作；其他 session 可以对这个表进行读操作，但是进行写操作会被阻塞，需要等待锁的释放。当一个 session 对某个表加了写锁之后，该 session 只能访问加锁的这个表，可以进行读操作和写操作，其他 session 对这个表的读和写操作都会被阻塞，需要等待锁的释放。
MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。
3. 如何加表锁
加读锁：
lock table tbl_name read;加写锁：
lock table tbl_name write;释放锁：
unlock tables;MyISAM 在执行查询语句前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。给 MyISAM 表显式加锁，一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。
注意，当使用 LOCK TABLES 时，不仅需要一次锁定用到的所有表，而且，同一个表在 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”选项，即：lock table tbl_name local read，在满足 MyISAM 表并发插入条件的情况下，其他用户就可以在表尾并发插入记录，但更新操作会被阻塞，而且加锁的用户无法访问到其他用户并发插入的记录。
5. MyISAM 锁调度
当写进程和读进程同时请求同一个 MyISAM 表的写锁和读锁时，写进程会优先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为 MySQL 认为写请求一般比读请求更重要。这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，因为大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。
通过一下一些设置调节 MyISAM 的调度行为：
（1）通过指定启动参数 low-priority-updates，使 MyISAM 引擎默认给予读请求以优先的权利。
（2）通过执行命令 SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
（3）通过指定 INSERT、UPDATE、DELETE 语句的 LOW_PRIORITY 属性，降低该语句的优先级。
（4）给系统参数 max_write_lock_count 设置一个合适的值，当一个表的读锁达到这个值后，MySQL 就暂时将写请求的优先级降低，给读进程一定获得锁的机会。
二、InnoDB 锁问题
1. 查询 InnoDB 行锁争用情况
show status like innodb_row_lock%如果 InnoDB_row_lock_waits 和 InnoDB_row_lock_time_avg 的值比较高，说明锁争用比较严重，这时可以通过设置 InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。
打开监视器：
CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
Show innodb status\G;停止监视器：
DROP TABLE innodb_monitor;打开监视器以后，默认情况下每 15 秒会向日志中记录监控的内容，如果长时间打开会导致.err 文件变得非常的巨大，所以用户在确认问题原因之后，要记得删除监控表以关闭监视器，或者通过使用“–console”选项来启动服务器以关闭写日志文件。
2. InnoDB 的行锁及加锁方法
InnoDB 的行锁有两种：共享锁（S）和排他锁（X）。为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁：意向共享锁和意向排他锁，这两种意向锁都是表锁。一个事务在给数据行加锁之前必须先取得对应表对应的意向锁。
意向锁是 InnoDB 自动加的，不需用户干预。对于 UPDATE、DELETE 和 INSERT 语句，InnoDB 会自动给涉及数据集加排他锁（X）；对于普通 SELECT 语句，InnoDB 不会加任何锁；事务可以通过以下语句显式给记录集加共享锁或排他锁。
Set autocommit=0;共享锁（S）：
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE排他锁（X）：
SELECT * FROM table_name WHERE ... FOR UPDATE释放锁：
unlock tables;（会隐含提交事务）
当一个事务获得一个表的共享锁时，其他事务可以查询该表的记录，也可以对该记录加共享锁。当一个事务对表进行更新操作时，若存在另一个事务也在该表加了共享锁，则需要等待锁的释放，若另一个事务同时也对该表执行了更新操作，则会导致死锁，另一个事务退出，当前事务完成更新操作。当一个事务获得一个表的排他锁时，其他事务只能对该表的记录进行查询，不能加共享锁，也不能更新记录，会出现等待。
3. InnoDB 行锁实现方式
InnoDB 行锁是通过给索引上的索引项加锁来实现的，InnoDB 这种行锁实现特点意味着：
（1）只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。
（2）由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。（虽然使用的是不同的索引，但是如果记录已经被其他 session 锁定的话也是需要等待的。）
（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。
4. 间隙锁
当使用范围条件检索数据的时候，对于键值在条件范围内但并不存在的记录，InnoDB 也会进行加锁，这个锁就叫“间隙锁”。InnoDB 使用间隙锁的目的，一方面是为了防止幻读，另一方面是为了满足恢复和复制的需要。但是这种加锁机制会阻塞符合条件范围内键值的并发插入，造成严重的锁等待，所以应该尽量避免使用范围条件来检索数据。
除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB 也会使用间隙锁！
5. 恢复和复制的需要对 InnoDB 锁机制的影响
MySQL 通过 BINLOG 记录执行成功的 INSERT、UPDATE、DELETE 等更新数据的 SQL 语句，并由此实现 MySQL 数据库的恢复和主从复制。MySQL 的恢复机制（复制其实就是在 Slave Mysql 不断做基于 BINLOG 的恢复）有以下特点：
（1）MySQL 的恢复是 SQL 语句级的，也就是重新执行 BINLOG 中的 SQL 语句。
（2）MySQL 的 Binlog 是按照事务提交的先后顺序记录的，恢复也是按这个顺序进行的。
所以 MySQL 的恢复和复制对锁机制的要求是：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读。
另外，对于一般的 select 语句，MySQL 使用多版本数据来实现一致性，不需要加任何锁，然而，对于“insert into target_tab select * from source_tab where …”和“create table new_tab …select … From source_tab where …”这种 SQL 语句，用户并没有对 source_tab 做任何更新操作，但 MySQL 对这种 SQL 语句做了特别处理，给 source_tab 加了共享锁。这是因为，不加锁的话，如果这个 SQL 语句执行期间，有另一个事务对 source_tab 做了更新并且先进行了提交，那么在 BINLOG 中，更新操作的位置会在该 SQL 语句之前，使用这个 BINLOG 进行数据库恢复的话，恢复的结果就会与实际的应用逻辑不符，进行复制则会导致主从数据库不一致。因为实际上应用插入 target_tab 或 new_tab 中的数据是另一个事务对 source_tab 更新前的数据，而 BINLOG 记录的却是先进行更新再执行 select…insert… 语句。如果上述语句的 SELECT 是范围条件，InnoDB 还会给源表加间隙锁。所以这种 SQL 语句会阻塞对原表的并发更新，应尽量避免使用。
6. InnoDB 使用表锁的情况及注意事项
对于 InnoDB 表，在绝大部分情况下都应该使用行级锁，但在个别特殊事务中，也可以考虑使用表级锁，主要有以下两种情况：
（1）事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
（2）事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。
另外，在 InnoDB 中使用表锁需要注意以下两点：
（1）使用 LOCK TABLES 虽然可以给 InnoDB 加表级锁，但表锁不是由 InnoDB 存储引擎层管理的，而是由其上一层──MySQL Server 负责的，仅当 autocommit=0、innodb_table_locks=1（默认设置）时，InnoDB 层才能知道 MySQL 加的表锁，MySQL Server 也才能感知 InnoDB 加的行锁，这种情况下，InnoDB 才能自动识别涉及表级锁的死锁；否则，InnoDB 将无法自动检测并处理这种死锁。
（2）在用 LOCK TABLES 对 InnoDB 表加锁时要注意，要将 AUTOCOMMIT 设为 0，否则 MySQL 不会给表加锁；事务结束前，不要用 UNLOCK TABLES 释放表锁，因为 UNLOCK TABLES 会隐含地提交事务；COMMIT 或 ROLLBACK 并不能释放用 LOCK TABLES 加的表级锁，必须用 UNLOCK TABLES 释放表锁。
7. 关于死锁
MyISAM 表锁是 deadlock free 的，这是因为 MyISAM 总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。
发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。
通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的 SQL 语句，绝大部分死锁都可以避免。下面就通过实例来介绍几种避免死锁的常用方法。
（1）在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
（2）在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
（3）在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
（4）在 REPEATABLE-READ 隔离级别下，如果两个线程同时对相同条件记录用 SELECT…FOR UPDATE 加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成 READ COMMITTED，就可避免问题。
（5）当隔离级别为 READ COMMITTED 时，如果两个线程都先执行 SELECT…FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第 1 个线程提交后，第 2 个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第 3 个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行 ROLLBACK 释放获得的排他锁。
感谢你能够认真阅读完这篇文章，希望丸趣 TV 小编分享的“MySQL 锁机制有什么用”这篇文章对大家有帮助，同时也希望大家多多支持丸趣 TV，关注丸趣 TV 行业资讯频道，更多相关知识等着你来学习!
