尚硅谷MySQL数据库高级-04-MySQL锁机制

原创已于 2024-05-30 16:10:32 修改 · 189 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #mysql

于 2021-08-23 16:02:25 首次发布

mysql 专栏收录该内容

5 篇文章

订阅专栏

转载参考：

https://github.com/RingoTangs/LearningNote/blob/master/MySQL/MySQL.md
https://blog.youkuaiyun.com/qq_21579045/article/details/99702766

概述

定义

锁是计算机协调多个进程或线程开发访问某一资源的机制。
在数据库中，除传统的计算资源（如CPU，RAM，I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

三锁

表锁（偏读）

特点

特点：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

锁表的命令

查看数据库表锁的命令
- SHOW OPEN TABLES;
给mylock表上读锁，给book表上写锁
- LOCK TABLE mylock READ, book WRITE;
释放表锁
- UNLOCK TABLES

表锁的读锁

在这里插入图片描述

表锁的写锁

在这里插入图片描述

案例结论

MyISAM引擎在执行查询语句SELECT之前，会自动给涉及到的所有表加读锁，在执行增删改之前，会自动给涉及的表加写锁。
MySQL的表级锁有两种模式：
- 表共享读锁（Table Read Lock）。
- 表独占写锁（Table Write Lock）。

锁类型	可否兼容	读锁	写锁
读锁	是	是	否
写锁	是	否	否

対MyISAM表进行操作，会有以下情况：
- 対MyISAM表的读操作（加读锁），不会阻塞其他线程対同一表的读操作，但是会阻塞其他线程対同一表的写操作。只有当读锁释放之后，才会执行其他线程的写操作。
- 対MyISAM表的写操作（加写锁），会阻塞其他线程対同一表的读和写操作，只有当写锁释放之后，才会执行其他线程的读写操作。
- 简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

表锁分析

SHOW STATUS LIKE ‘table%’;查看锁表的次数等信息

mysql> SHOW STATUS LIKE 'table%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Table_locks_immediate      | 221    |
| Table_locks_waited         | 0      |
| Table_open_cache_hits      | 500063 |
| Table_open_cache_misses    | 16     |
| Table_open_cache_overflows | 0      |
+----------------------------+--------+
5 rows in set (0.00 sec)

可以通过Table_locks_immediate和Table_locks_waited状态变量来分析系统上的表锁定。具体说明如下：
- Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1。
- Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在较严重的表级锁争用情况。
此外，MyISAM的读写锁调度是写优先，这也是MyISAM不适合作为主表的引擎。因为写锁后，其他线程不能进行任何操作，大量的写操作会使查询很难得到锁，从而造成永远阻塞。

行锁(偏写)

特点

偏向Innodb存储引擎，开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度也最高。
Innodb与MyISAM的最大不同有两点：
- 支持事务（TRANSACTION）
- 采用了行级锁
事务（Transaction）及其ACID属性回顾:尚硅谷JDBC核心技术(新版jdbc)

行锁定基本演示

在这里插入图片描述

无索引行锁升级为表锁

如果在更新数据的时候出现了强制类型转换导致索引失效，使得行锁变表锁，即在操作不同行的时候，会出现阻塞的现象。

需要where的字段是字符串字段并且不加单引号
比如UPDATE test_innodb_lock SET b = ‘3003’ WHERE a = 3;，这个就不能满足索引失效

间隙锁的危害

什么是间隙锁：当我们用范围条件而不是相等条件索引数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”。InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
危害：
- 因为Query执行过程中通过范围查找的话，会锁定整个范围内所有的索引键值，即使这个键值并不存在。
- 间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

在这里插入图片描述

如何主动锁定一行

SELECT …FOR UPDATE在锁定某一行后，其他写操作会被阻塞，直到锁定的行被COMMIT。
InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

在这里插入图片描述

行锁分析

Innodb_row_lock_current_waits：当前正在等待锁定的数量。
Innodb_row_lock_time：从系统启动到现在锁定总时间长度。
Innodb_row_lock_time_avg：每次等待所花的平均时间。
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间。
Innodb_row_lock_waits：系统启动后到现在总共等待的次数。

mysql> SHOW STATUS LIKE 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 214245 |
| Innodb_row_lock_time_avg      | 17853  |
| Innodb_row_lock_time_max      | 51061  |
| Innodb_row_lock_waits         | 12     |
+-------------------------------+--------+
5 rows in set (0.00 sec)

案例结论

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。
但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。