南通wap网站建设,在线制作图片动画效果,厦门seo网站排名优化,建个企业网站一年需要多少钱【MySQL】锁 0. 锁的考察点1. 概述1. 锁的分类1.1 属性分类1.2 粒度分类 2. 全局锁2.1 全局锁操作2.2.1 备份问题 3. 表级锁3.1 表锁3.2 语法3.3 表共享读锁#xff08;读锁#xff09;3.4 表独占写锁#xff08;写锁#xff09;3.5 元数据锁(meta data lock, MDL)3.6 意向… 【MySQL】锁 0. 锁的考察点1. 概述1. 锁的分类1.1 属性分类1.2 粒度分类 2. 全局锁2.1 全局锁操作2.2.1 备份问题 3. 表级锁3.1 表锁3.2 语法3.3 表共享读锁读锁3.4 表独占写锁写锁3.5 元数据锁(meta data lock, MDL)3.6 意向锁3.6.1 意向锁的种类3.6.2 兼容关系3.6.3 意向锁测试 4. 行级锁附录 0. 锁的考察点 1. 概述
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中除传统的计算资源(CPU、RAM、I/O)的争用以外数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说锁对数据库而言显得尤其重要也更加复杂。
1. 锁的分类 1.1 属性分类
InnoDB存储引擎实现了两种标准的行级锁共享锁(S Lock)和排他锁(X Lock)。
共享锁(S Lock):允许事务读一行数据。 排他锁(X Lock):允许事务删除或更新一行数据。
1.2 粒度分类
按照锁的粒度来分分为以下三类
全局锁锁定数据库中的所有表。表级锁每次操作锁住整张表。行级锁每次操作锁住对应的行数据。
2. 全局锁
全局锁就是对整个数据库实例加锁加锁后整个实例就处于只读状态后续的DML的写语句DDL语句已经更新操作的事务提交语句都将被阻塞。
其典型的使用场景是做全库的逻辑备份对所有的表进行锁定从而获取一致性视图保证数据的完整性。
如果不上锁边备份业务还正常执行就会造成数据不一致的问题。
2.1 全局锁操作
mysql flush tables with read lock; // 加全局锁
Query OK, 0 rows affected (0.01 sec)mysql insert into migrations values(7,1231231,1); // 插入数据的时候
ERROR 1223 (HY000): Cant execute the query because you have a conflicting read lockmysql unlock tables; // 销毁全局锁
Query OK, 0 rows affected (0.00 sec)mysql insert into migrations values(7,1231231,1); // 可以正常插入了
Query OK, 1 row affected (0.01 sec)备份数据库(newdb3)的数据
(base) ➜ ~ mysqldump -u root -p newdb3/Users/fanzhen/Downloads/newdb3.sql
Enter password:2.2.1 备份问题
全局锁特点
数据库中加全局锁是一个比较重的操作存在以下问题 如果在主库上备份那么在备份期间都不能执行更新业务基本上就得停摆。 如果在从库上备份那么在备份期间从库不能执行主库同步过来的二进制日志(binlog)会导致主从延迟。
在InnoDB引擎中我们可以在备份时加上参数-single-transaction参数来完成不加锁的一致性数据备份。 3. 表级锁
表级锁每次操作锁住整张表。锁定粒度大发生锁冲突的概率最高并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。
对于表级锁主要分为以下三类
表锁元数据锁(meta data lock, MDL)意向锁
3.1 表锁
对于表锁分为两类
表共享读锁(read lock) 简称读锁表独占写锁(write lock) 简称写锁
3.2 语法
1. 加锁: lock tables 表名 read/write
2. 释放锁unlock tables; 或者 客户端断开连接3.3 表共享读锁读锁
如下图当对一张表加读锁我们可以看到不同的客户端都可以进行查询操作但是进行DDL与DML都会阻塞当进行unlock tables操作时其他进行DML、DDL操作的客户端获取资源操作阻塞的SQL语句。 3.4 表独占写锁写锁
提示排他锁又称为写锁、独占锁 写锁在当前客户端既可以读也可以写但是其他客户端既不能写也不能读。
mysql unlock tables;
Query OK, 0 rows affected (0.00 sec)mysql lock tables migrations write;
Query OK, 0 rows affected (0.01 sec)mysql select * from migrations;
------------------------------------------------------------------
| id | migration | batch |
------------------------------------------------------------------
| 1 | 2014_10_12_000000_create_users_table | 1 |
| 2 | 2014_10_12_100000_create_password_resets_table | 1 |
| 3 | 2019_08_19_000000_create_failed_jobs_table | 1 |
| 4 | 2019_12_14_000001_create_personal_access_tokens_table | 1 |
| 5 | 2024_03_31_143916_create_posts_table | 1 |
| 6 | 2024_04_01_143040_create_blogs_table | 1 |
| 7 | dagenihao | 1 |
------------------------------------------------------------------
7 rows in set (0.00 sec)3.5 元数据锁(meta data lock, MDL)
MDL加锁过程是系统自动控制无需显式使用在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性在表上有活动事务的时候不可以对元数据进行写入操作。为了避免DML与DDL冲突保证读写的正确性。
在MySQL5.5中引入了MDL当对一张表进行增删改查的时候加MDL读锁共享当对表结构进行变更操作的时候加MDL写锁排他。 // 客户端1
mysql begin;
Query OK, 0 rows affected (0.00 sec)mysql select * from migrations;
------------------------------------------------------------------
| id | migration | batch |
------------------------------------------------------------------
| 1 | 2014_10_12_000000_create_users_table | 1 |
| 2 | 2014_10_12_100000_create_password_resets_table | 1 |
| 3 | 2019_08_19_000000_create_failed_jobs_table | 1 |
| 4 | 2019_12_14_000001_create_personal_access_tokens_table | 1 |
| 5 | 2024_03_31_143916_create_posts_table | 1 |
| 6 | 2024_04_01_143040_create_blogs_table | 1 |
| 7 | dagenihao | 1 |
------------------------------------------------------------------
7 rows in set (0.00 sec)
// 当客户端2执行完 update migrations set migration MDL where id 7;
mysql select * from migrations;
------------------------------------------------------------------
| id | migration | batch |
------------------------------------------------------------------
| 1 | 2014_10_12_000000_create_users_table | 1 |
| 2 | 2014_10_12_100000_create_password_resets_table | 1 |
| 3 | 2019_08_19_000000_create_failed_jobs_table | 1 |
| 4 | 2019_12_14_000001_create_personal_access_tokens_table | 1 |
| 5 | 2024_03_31_143916_create_posts_table | 1 |
| 6 | 2024_04_01_143040_create_blogs_table | 1 |
| 7 | dagenihao | 1 |
------------------------------------------------------------------
7 rows in set (0.00 sec)
mysql commit- ;
Query OK, 0 rows affected (0.00 sec)------------------------第二个例子-----------------------------------
mysql bagin;
mysql select * from migrations;
------------------------------------------------------------------
| id | migration | batch |
------------------------------------------------------------------
| 1 | 2014_10_12_000000_create_users_table | 1 |
| 2 | 2014_10_12_100000_create_password_resets_table | 1 |
| 3 | 2019_08_19_000000_create_failed_jobs_table | 1 |
| 4 | 2019_12_14_000001_create_personal_access_tokens_table | 1 |
| 5 | 2024_03_31_143916_create_posts_table | 1 |
| 6 | 2024_04_01_143040_create_blogs_table | 1 |
| 7 | MDL | 1 |
------------------------------------------------------------------
7 rows in set (0.00 sec)// 客户端2
mysql begin;
Query OK, 0 rows affected (0.00 sec)mysql select * from migrations;
------------------------------------------------------------------
| id | migration | batch |
------------------------------------------------------------------
| 1 | 2014_10_12_000000_create_users_table | 1 |
| 2 | 2014_10_12_100000_create_password_resets_table | 1 |
| 3 | 2019_08_19_000000_create_failed_jobs_table | 1 |
| 4 | 2019_12_14_000001_create_personal_access_tokens_table | 1 |
| 5 | 2024_03_31_143916_create_posts_table | 1 |
| 6 | 2024_04_01_143040_create_blogs_table | 1 |
| 7 | dagenihao | 1 |
------------------------------------------------------------------
7 rows in set (0.00 sec)
mysql update migrations set migration MDL where id 7;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql commit;
Query OK, 0 rows affected (0.00 sec)------------------------第二个例子-----------------------------------
mysql alter table migrations add column java int; // 不能进行修改会一直阻塞查看元数据锁
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
-------------------------------------------------------------------------------------------
| object_type | object_schema | object_name | lock_type | lock_duration |
-------------------------------------------------------------------------------------------
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ | STATEMENT |
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ | STATEMENT |
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ | STATEMENT |
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ | STATEMENT |
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ | STATEMENT |
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ | STATEMENT |
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ | STATEMENT |
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ | STATEMENT |
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ | STATEMENT |
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ | STATEMENT |
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ | STATEMENT |
| TABLE | performance_schema | metadata_locks | SHARED_READ | TRANSACTION |
| SCHEMA | performance_schema | NULL | INTENTION_EXCLUSIVE | TRANSACTION |
-------------------------------------------------------------------------------------------
13 rows in set (0.01 sec)3.6 意向锁
为了避免DML在执行时加的行锁与表锁的冲突在InnoDB中引入了意向锁使得表锁不用检查每行数据是否加锁使用意向锁来减少表锁的检查。
说明左侧线程A在执行的时候先开启事物然后执行update并会在这一行加上行锁然后对该表加上意向锁线程B要对这个表加上表锁线程B要通过意向锁来决定能不能加表锁。
3.6.1 意向锁的种类
1.意向共享锁(IS)由语句select...lock in share mode添加 2.意向排他锁(IX)由insert、update、delete、select.. for update 添加
3.6.2 兼容关系
意向共享锁(IS)与表锁共享锁(read)兼容与表锁排它锁(write)互斥。意向排他锁(IX)与表锁共享锁(read)及排它锁(write)都互斥。意向锁之间不会互斥。
可以通过以下SQL查看意向锁及行锁的加锁情况。
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;3.6.3 意向锁测试
// TODO
4. 行级锁
行级锁每次操作锁住对应的行数据。锁定粒度最小发生锁冲突的概率最低并发度最高。应用在InnoDB存储引擎中。
InnoDB的数据是基于索引组织的行锁是通过对索引上的索引项加锁来实现的而不是对记录加的锁。
对于行级锁主要分为以下三类
行锁(Record Lock)锁定单个行记录的锁防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。间隙锁(Gap Lock)锁定索引记录间隙不含该记录确保索引记录间隙不变防止其他事务在这个间隙进行insert产生幻读。在RR隔离级别下都支持临键锁(Next-Key Lock)行锁和间隙锁组合同时锁住数据并锁住数据前面的间隙Gap。在RR隔离级别下支持。
// TODO
附录
徐庶MySQL锁 https://blog.csdn.net/bjjx123456/article/details/136180761黑马讲解MySQL锁部分
