首页 > 数据库 >MySQL锁：全局锁、表级锁和行锁

MySQL锁：全局锁、表级锁和行锁

时间：2022-10-13 22:13:18浏览次数：75  
标签：name 行锁 lock MySQL sec mysql test table 表级

事务的实现离不开MySQL数据库的锁机制，设计锁的目的也是为了处理并发访问问题，本文简单介绍MySQL 里面的全局锁、表级锁和行锁三类锁。

目录

按对数据操作的类型可分为读锁（read lock）和写锁（write lock）。

  • 读锁也叫共享锁（S锁），加了读锁后，加锁的当前会话以及其它会话只能进行读操作，并且多个读操作可以同时进行。
  • 写锁也称为排他锁， X锁， exclusive 的缩写，因为加了写锁之后其它会话不能进行读写操作。

从对数据操作的颗粒度来分，锁可以分为全局锁、表级锁和行锁，下面分别对他们进行介绍。

全局锁

全局锁是对整个数据库加锁，数据库被加上全局锁后所有数据表会处于只读状态，修改数据表的DDL语句，数据更新语句（增删改）等操作会被阻塞。

全局锁的命令是：FLUSH TABLES WITH READ LOCK; , 简称FTWRL，解锁命令：UNLOCK TABLES;

窗口A加全局锁：

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

窗口A插入数据会报错：

mysql> insert into department (name) values ('测试');
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

窗口B查询：

mysql> select * from department;
+----+------+
| id | name |
+----+------+
|  1 | 开发 |
|  2 | 产品 |
|  5 | 测试 |
+----+------+
3 rows in set (0.04 sec)

窗口B插入数据会被阻塞：

mysql> insert into department (name) values ('测试');

窗口A解锁：

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

窗口B插入语句执行成功：

mysql> insert into department (name) values ('测试');
Query OK, 1 row affected (2 min 44.85 sec)

mysql> select * from department;
+----+------+
| id | name |
+----+------+
|  1 | 开发 |
|  2 | 产品 |
|  5 | 测试 |
|  6 | 测试 |
+----+------+
4 rows in set (0.00 sec)

全局锁的一个使用场景是做全库逻辑备份（mysqldump），加了全局锁后，在备份过程中整个库处于只读状态。如果备份主库，备份期间更新相关业务都无法执行；如果是备份从库，备份期间从库就不能执行主库同步过来的 binlog，会导致主从延迟。

支持事务的引擎InnoDB可以不使用FTWRL，使用mysqldump命令备份时可以使用参数 --single-transaction 来获取一致性备份，它会在开始备份前启动一个事务（可重复读隔离级别），利用了MVCC技术（可参考文章MySQL事务：事务隔离），确保拿到一致性视图，备份过程可以正常更新数据。

而不支持事务的引擎MyISAM就无法使用 --single-transaction参数了，它不支持事务隔离，所以使用MyISAM引擎的数据库备份需要使用FTWRL命令。

表级锁

表级锁是对整张表加锁，MyISAM、InnoDB、BDB和MEMOR都支持表级锁。

MySQL 表级锁包括两种：表锁和元数据锁（meta data lock，MDL)。

表锁

表锁包括读锁（read lock）和写锁（write lock）。

lock table 表名 read;  -- 读锁 
lock table 表名 write; -- 写锁
unlock tables; -- 释放表锁，连接断开时会自动释放表锁

以MyISAM引擎为例演示一下表锁的作用效果：

创建一个以MyISAM为引擎的表，并插入一条记录：

create table table_lock_test(
    id int not null auto_increment primary key,
    name char(30) not null) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    
insert into table_lock_test (name) values ('zhangsan');

1、读锁实例

窗口A给table_lock_test表加读锁：

mysql> lock table table_lock_test read;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from table_lock_test;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

查看被锁定的表：

mysql> show open tables where In_use > 0;
+----------+-----------------+--------+-------------+
| Database | Table           | In_use | Name_locked |
+----------+-----------------+--------+-------------+
| testdb   | table_lock_test |      1 |           0 |
+----------+-----------------+--------+-------------+
1 row in set (0.00 sec)

窗口A执行更新、插入操作：

mysql> update table_lock_test set name='lishi' where id=1;
ERROR 1099 (HY000): Table 'table_lock_test' was locked with a READ lock and can't be updated

mysql> insert into table_lock_test (name) values ('lishi');
ERROR 1099 (HY000): Table 'table_lock_test' was locked with a READ lock and can't be updated

窗口A读其它表：

mysql> select * from department;
ERROR 1100 (HY000): Table 'department' was not locked with LOCK TABLES

未解锁前不能操作其它表。

窗口B查询：

mysql> select * from table_lock_test;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

窗口B执行更新操作：

mysql> update table_lock_test set name='lishi' where id=1;

更新操作会被阻塞，因为读锁没有被释放，只能查询不能更新。到窗口A将锁释放：

unlock tables;

解锁后，窗口B更新操作成功：

mysql> update table_lock_test set name='lishi' where id=1;
Query OK, 1 row affected (3 min 22.88 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from table_lock_test;
+----+-------+
| id | name  |
+----+-------+
|  1 | lishi |
+----+-------+
1 row in set (0.00 sec)

2、写锁实例

窗口A给table_lock_test表加写锁：

mysql> lock table table_lock_test write;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from table_lock_test;
+----+-------+
| id | name  |
+----+-------+
|  1 | lishi |
+----+-------+
1 row in set (0.00 sec)

窗口A执行更新、插入操作：

mysql> update table_lock_test set name='zhangsan' where id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into table_lock_test (name) values ('lishi');
Query OK, 1 row affected (0.05 sec)

mysql> select * from table_lock_test;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lishi    |
+----+----------+
2 rows in set (0.00 sec)

窗口A读其它表：

mysql> select * from department;
ERROR 1100 (HY000): Table 'department' was not locked with LOCK TABLES

和读锁一样，未解锁前不能其它表进行CRUD操作。

窗口B查询：

mysql> select * from table_lock_test;

被阻塞了，因为写锁为排它锁，其它连接不能对table_lock_test表进行读写操作。

到窗口A将锁释放：

unlock tables;

解锁后，窗口B查询成功：

mysql> select * from table_lock_test;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lishi    |
+----+----------+
2 rows in set (3 min 10.99 sec)

元数据锁

在 MySQL 5.5 以后的版本中，访问一个表的时候会自动加上 MDL（metadata lock)，作用是保证读写的正确性。比如在RR隔离级别下，当前事务A在操作一个表的过程中，另外一个事务B对表执行DDL操作（表结构变更），如果没有MDL，会导致事务A 两次查询结果不一样。要实现可重复度，就需要使用到MDL。

当对一个表做DML操作（增删改查）时，加 MDL 读锁，当其它事务对表进行DDL操作时，需要先获得MDL 读锁才能对表结构进行修改；当要对表做DDL操作（表结构变更）时，会加 MDL 写锁。

  • 读锁之间不互斥，多事务同时对一张表进行DML操作不会阻塞。 注意：实际进行增删改查时可能会看到锁等待，这是InnoDB的行锁导致的。
  • 读写锁之间互斥，也就是对同一表进行的DML和DDL操作会相互阻塞。
  • 写锁之间互斥，多事务同时对一张表进行DDL操作会阻塞，其中一个要等另一个执行完才能开始执行。

MDL锁在语句开始执行时申请，在事务提交后释放。如果事务A对表T加了一个MDL读锁，且未提交。事务B对数据表执行DDL操作，此时事务B会被锁住（Waiting for table metadata lock），因为事务A 的 MDL 读锁没有释放，而事务B需要 MDL 写锁，因此会被阻塞。而后续对表T的任何操作都会被阻塞，此时表T完全不可读写了。MDL 锁的这一机制需要特别注意。

在窗口A启动一个事务：

mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | performance_schema | metadata_locks | NULL        |         1883582562928 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:5929 |              75 |              4 |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from table_lock_test;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lishi    |
+----+----------+
2 rows in set (0.14 sec)

mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+-----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME     | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+-----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | testdb             | table_lock_test | NULL        |         1883582562928 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:5929 |              75 |              7 |
| TABLE       | performance_schema | metadata_locks  | NULL        |         1883583662656 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:5929 |              75 |              8 |
+-------------+--------------------+-----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
2 rows in set (0.00 sec)

mysql>

事务A启动后，metadata_locks表新增了一条table_lock_test表的加锁记录。

窗口B执行一个修改表结构的DDL操作：

mysql> ALTER TABLE table_lock_test ADD age TINYINT;

会被阻塞。

窗口A查询数据库线程：

mysql> show processlist;
+----+-----------------+-----------------+--------+---------+---------+---------------------------------+---------------------------------------------+
| Id | User            | Host            | db     | Command | Time    | State                           | Info                                        |
+----+-----------------+-----------------+--------+---------+---------+---------------------------------+---------------------------------------------+
|  4 | event_scheduler | localhost       | NULL   | Daemon  | 1693591 | Waiting on empty queue          | NULL                                        |
| 36 | root            | localhost:61623 | testdb | Query   |       0 | starting                        | show processlist                            |
| 37 | root            | localhost:55438 | testdb | Query   |      93 | Waiting for table metadata lock | ALTER TABLE table_lock_test ADD age TINYINT |
+----+-----------------+-----------------+--------+---------+---------+---------------------------------+---------------------------------------------+
3 rows in set (0.05 sec)

可以发现线程37在等待MDL锁。

接着窗口C执行查询操作：

mysql> select * from table_lock_test;

也被阻塞了，后续对表table_lock_test的任何操作都会被阻塞掉。

到窗口A提交事务

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

B和C执行成功。查询表，字段添加成功：

mysql> select * from table_lock_test;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan | NULL |
|  2 | lishi    | NULL |
+----+----------+------+
2 rows in set (0.00 sec)

查询table_lock_test表的加锁记录，表table_lock_test消失：

mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | performance_schema | metadata_locks | NULL        |         1883583665248 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:5929 |              75 |             13 |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
1 row in set (0.00 sec)

行锁

行锁简介

行锁是对数据行加锁，是在引擎层实现的，MyISAM 引擎不支持行锁，而 InnoDB 引擎是支持行锁的，因此相比MyISAM 引擎，InnoDB可以提供良好的并发控制。

当然行锁也包括读锁（read lock）和写锁（write lock）。

  • 读锁也叫共享锁，S锁，加了读锁后，加锁的当前会话以及其它会话只能进行读操作，并且多个读操作可以同时进行。
  • 写锁也称为排他锁，X锁， exclusive 的缩写，因为加了写锁之后其它会话不能进行读写操作。

当前读都会加行锁：

select * from account lock in share mode; # 添加读锁（S 锁，共享锁）
select * from account for update; # 添加写锁（X 锁，排他锁）
insert语句
update语句
delete语句

InnoDB 引擎的行锁会自动加上，比如在更新数据时会自动加上行锁，其它事务对相同行数据进行更新时就会被阻塞，直到锁被释放才能更新。

行锁不是在数据更新完行之后就立马解锁，而是在事务提交(commit)或者回滚(rollback)之后才会解锁。这称为两阶段锁协议，包括加锁和解锁两个阶段。

行锁实例

下面看一个例子：

先创建一个表，使用InnoDB引擎：

create table table_row_lock(
    id int not null auto_increment primary key COMMENT 'ID',
    name char(30) not null COMMENT '名称') ENGINE=InnoDB 
	DEFAULT CHARSET=utf8
	comment = '行锁测试';

事务隔离级别为可重复读（RR）。

插入数据：

mysql> insert into table_row_lock (name) values ('张三');
Query OK, 1 row affected (0.07 sec)

mysql> insert into table_row_lock (name) values ('李四');
Query OK, 1 row affected (0.09 sec)

mysql> select * from table_row_lock;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | 李四 |
+----+------+
2 rows in set (0.00 sec)

窗口A启动一个事务，并更新数据：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update table_row_lock set name="王二" where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from table_row_lock;
+----+------+
| id | name |
+----+------+
|  1 | 王二 |
|  2 | 李四 |
+----+------+
2 rows in set (0.00 sec)

窗口B更新相同行的记录，会被阻塞：

mysql> update table_row_lock set name="张三" where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

行锁等待时间可以执行 SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; 命令查看：

mysql> SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set, 1 warning (0.01 sec)

窗口B更新其它行记录：

mysql> update table_row_lock set name="王五" where id=2;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from table_row_lock;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | 王五 |
+----+------+
2 rows in set (0.00 sec)

id=2行数据没有被加写锁，所以可以对它进行更新操作。

窗口A提交事务：

mysql> commit;
Query OK, 0 rows affected (0.07 sec)

此时窗口B就可以更新id=1那行数据了。

mysql> update table_row_lock set name="张三" where id=1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from table_row_lock;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | 王五 |
+----+------+
2 rows in set (0.00 sec)

死锁

真实业务中通常对数据库进行并发控制，多个事务可能会依赖相同的资源，如果都在等待其它事务释放锁时，这会导致这几个事务进入无限等待的状态，也就是出现死锁。

比如下图就会出现死锁，事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。

出现死锁后，有两种策略来解决：
  1. 等待锁超时。默认超时等待时间为50s，可通过innodb_lock_wait_timeout参数来设置，具体等待时间需要根据自己的实际业务来设置。
  2. 死锁检测。设置参数innodb_deadlock_detect = on，开启后，系统会自动检测死锁的事务并回滚某一个事务，让其他可以事务继续执行。

总结

本文简单介绍了全局锁、表级锁和行锁，全局锁主要用于全库逻辑备份。如果数据库引擎使用的是 InnoDB，可以使用 -–single-transaction 参数。

表锁是对整张表加锁，访问一个表的时候会自动加上元数据锁(MDL)，MDL锁在语句开始执行时申请，在事务提交后释放。如果表T的MDL锁没有释放，后续对表T的任何操作都会被阻塞。

行锁是对数据行加锁，MyISAM引擎不支持行锁，它一般使用的是表级锁。InnoDB 引擎支持行锁，因此它可以实现更好的并发控制。另外使用行锁要注意尽量减少死锁对数据库的影响。

另外要注意的是，加锁是要消耗资源的，对锁的各种操作会增加系统的开销，相比MyISAM引擎，InnoDB 引擎的行锁会导致占用更大的数据空间。

--THE END--

凡是对自己有一点利益就会选择相信。有时候即使毫无可能，人们也会顽冥不化。
——艾萨克·阿西莫夫《神们自己》

标签：name,行锁,lock,MySQL,sec,mysql,test,table,表级
From： https://www.cnblogs.com/hiyong/p/16617749.html

相关文章

  • Centos7部署单点mysql
    目录Centos7部署单点mysql一、部署mysql1.1、下载rpm包1.2、安装1.3、启动数据库，修改初始秘密二、创建远程用户，授权Centos7部署单点mysql一、部署mysql1.1、下载rpm包w......
  • 11.1 LAMP架构介绍 11.2 MySQL、MariaDB介绍 11.3/11.4/11.5 MySQL安装
     11.1LAMP架构介绍LAMP是Linux+Apache(httpd)+MySQL+PHP的简称•Linux：操作系统•Apache(httpd)：WEB服务器软件，提供WEB服务•MySQL：关系型数据库用于存储数据（字符串）•......
  • MySQL双机热备
     1.MySQL双机热备原理双机热备就是使用MySQL提供的一种主从备份机制实现。所谓双机热备其实是一个复制的过程，复制过程中一个服务器充当主服务器，一个或多个服务器充当从服......
  • MYSQL-->客户端常用工具指令
    mysql这个mysql指的是mysql的客户端管理工具语法mysql选项数据库选项内容-u指定用户名-p指定密码-h指定ip地址-P指定端口-e执行SQL语句并退出-e选项可......
  • Mysql使用load命令报错
    问题1:ERROR1290(HY000):TheMySQLserverisrunningwiththe--secure-file-privoptionsoitcannotexecutethisstatement需要在my.cnf文件里面添加[mys......
  • MYSQL-->InnoDB引擎底层原理
    逻辑存储结构逻辑存储结构图表空间表空间文件在Linux下存放在/var/lib/mysql文件中的xxx.ibd文件就是表空间文件表空间文件用来存储，记录，索引等数据。段段分......
  • Mysql----锁
    参考：https://www.bilibili.com/video/BV1Kr4y1i7ru?p=121&vd_source=c85b4a015a69e82ad4f202bd9b87697f概述介绍锁是计算机协调多个进程或者线程并发访问某一资源的机制......
  • Mysql创建索引
    创建索引方式一：建立表的时候创建CREATETABLE`student`(`ID`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(20)DEFAULTNULL,`grade`varchar(20)DE......
  • mysql用户授权
    查看mysql库user表结构>descmysql.user;查看mysql用户和密码>selectUser,Host,authentication_stringfrommysql.user;查看crm_user'@'10.%.%.%'授权>sh......
  • Unity---2020版本连接Mysql报错
    连接Mysql需要Mysql.data打包发布后无法连接数据库需要导入I18N.dll,I18N.CJK.dll,I18N.West.dll导入System.Data.dll2020版本以上不需要导入System.Data.dll如......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99