MYSQL锁

IT界数据库架构师的漂泊人生 2021-01-08

336

事务基本要数（ACID)

1 原子性（ATOMICITY)

事务开始所有操作要么全部做完，要么全部不做，不可能停留在中间环节。事务执行过程中出错要回滚到事务开始前的状态。

2 一致性（CONSISTENCY)

事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账不可能A扣了钱，B却没有收到

3 隔离性（ISOLATION)

同一时间，只容许一个事务请求同一数据，不同事务之间彼此没有任何干扰。

4 持久性（DURABILITY)

事务并发带来隔离问题

MySQL的众多存储引擎中，只有InnoDB支持事务，所有这里说的事务隔离级别指的是InnoDB下的事务隔离级别。

读未提交：

一个事务可以读取到另一个事务未提交的修改。

这会带来脏读、幻读、不可重复读问题。（基本没用）

读已提交：

一个事务只能读取另一个事务已经提交的修改。

其避免了脏读，但仍然存在不可重复读和幻读问题。

可重复读：

同一个事务中多次读取相同的数据返回的结果是一样的。

其避免了脏读和不可重复读问题，但幻读依然存在。

串行化：

事务串行执行。避免了以上所有问题。

以上是SQL-92标准中定义的四种隔离级别。在MySQL中，默认的隔离级别是REPEATABLE-READ（可重复读），并且解决了幻读问题。

简单的来说，Mysql的默认隔离级别解决了脏读、幻读、不可重复读问题。

MYSQL 实现事务隔离并且解决并发带来各种问题,依靠的是锁这个东西

INNODB 锁: 行锁和表锁

行锁有:

1 共享锁(S) 排它锁(X)

行共享锁是当读取一条记录时防止别人修改而加的锁

SELECT FOR SHARE; SELECT .. LOCK IN SHARE MODE
行排它锁是当修改一条记录时防止其他事务同时修改而加的锁

SELECT .. FOR UPDATE
2 记录锁(Record Lock)

记录锁是锁住记录，锁住索引记录，而不是真正的数据记录,为此应该称谓索引记录锁

锁是非主键索引，会在索引记录上加锁后，在去主键索引上加锁
表上没有索引，会在隐藏的主键索引上加锁
如果要锁的列没有索引，进行全表记录加锁

3 间隙锁(Gap Lock)

锁定索引记录之间的间隙,或第一条或最后一条之前的间隙。

这不难理解，比如说如下语句。

SELECT *
FROM T
WHRE AGE > 10 AND AGE < 24
FOR UPDATE

复制

那么它会在10到24之间加锁，插入15就被阻塞

4 下个关键字锁(NEXT-KEY Lock)

是间隙锁+记录锁的组合

5 插入意向锁

插入的时候会获取插入行的排它锁，同时会添加插入意向锁到值的间隙。原本有值4,7，欲插入5,6 那么在4,7之间加意向锁。

表锁: 1 意向锁 2 自增锁
SELECT ... FOR SHARE：添加表的意向共享锁（IS）获取行共享之前
SELECT ... FOR UDPATE ：添加表的意向排它锁（IX）获取行排它之前

自增锁处理表自增ID的并发，主要参数是INNODB_AUTOINC_LOCK_MODE

锁模式：

IX	意向排它锁模
X	下个关键字排它锁模式
S	下个关键字共享锁模式
X,REC_NOT_GAP	记录锁排它模式
S,REC_NOT_GAP	记录锁共享模式
X,GAP	间隙锁排它模式
S,GAP	间隙锁共享模式
X,GAP,INSERT_INTENTION	插入意向锁

加锁测试

mysql> select * from books;
+----+------------------------+-------+---------------------+
| id | title                  | price | publishDate         |
+----+------------------------+-------+---------------------+
|  1 | Java编程思想           | 98.50 | 2005-01-02 00:00:00 |
|  2 | HeadFirst设计模式      | 55.70 | 2010-11-09 00:00:00 |
|  3 | 第一行Android代码      | 69.90 | 2015-06-23 00:00:00 |
|  4 | C++编程思想            | 88.50 | 2004-01-09 00:00:00 |
|  5 | HeadFirst Java         | 55.70 | 2013-12-17 00:00:00 |
|  6 | 疯狂Android            | 19.50 | 2014-07-31 00:00:00 |
+----+------------------------+-------+---------------------+
6 rows in set (0.01 sec)

复制

查看建表语句

mysql> show create table books;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| books | CREATE TABLE `books` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '编号',
  `title` varchar(100) NOT NULL COMMENT '书名',
  `price` decimal(10,2) DEFAULT NULL COMMENT '价格',
  `publishDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '出版日期',
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`),
  KEY `ix_book_price` (`price`) USING BTREE /*!80000 INVISIBLE */,
  KEY `ix_book_pubdate` (`publishDate`) /*!80000 INVISIBLE */
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
复制

有主键ID,唯一索引TITLE,两个普通索引.

取消自动事务

mysql> set autocommit= off;

对表加锁

mysql> select * from books for update;
+----+------------------------+-------+---------------------+
| id | title                  | price | publishDate         |
+----+------------------------+-------+---------------------+
|  1 | Java编程思想               | 98.50 | 2005-01-02 00:00:00 |
|  2 | HeadFirst设计模式      | 55.70 | 2010-11-09 00:00:00 |
|  3 | 第一行Android代码      | 69.90 | 2015-06-23 00:00:00 |
|  4 | C++编程思想                 | 88.50 | 2004-01-09 00:00:00 |
|  5 | HeadFirst Java           | 55.70 | 2013-12-17 00:00:00 |
|  6 | 疯狂Android                 | 19.50 | 2014-07-31 00:00:00 |
+----+------------------------+-------+---------------------+
6 rows in set (0.01 sec)

复制

查看锁信息(MYSQL8.0)

SELECT 
    engine_lock_id,
    engine_transaction_id,
    thread_id,
    object_schema,
    object_name,
    index_name,
    lock_type,
    lock_mode,
    lock_status,
    lock_data
FROM
    performance_schema.data_locks;

复制

+---------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+-----------+-------------+------------------------+
| engine_lock_id                        | engine_transaction_id | thread_id | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data              |
+---------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+-----------+-------------+------------------------+
| 140093717142640:1060:140093646015904   |                 10253 |        51 | bookstore     | books       | NULL       | TABLE     | IX        | GRANTED     | NULL                   |
| 140093717142640:3:4:1:140093646012928 |                 10253 |        51 | bookstore     | books       | PRIMARY    | RECORD    | X         | GRANTED     | supremum pseudo-record |
| 140093717142640:3:4:2:140093646012928 |                 10253 |        51 | bookstore     | books       | PRIMARY    | RECORD    | X         | GRANTED     | 1                      |
| 140093717142640:3:4:3:140093646012928 |                 10253 |        51 | bookstore     | books       | PRIMARY    | RECORD    | X         | GRANTED     | 2                      |
| 140093717142640:3:4:4:140093646012928 |                 10253 |        51 | bookstore     | books       | PRIMARY    | RECORD    | X         | GRANTED     | 3                      |
| 140093717142640:3:4:5:140093646012928 |                 10253 |        51 | bookstore     | books       | PRIMARY    | RECORD    | X         | GRANTED     | 4                      |
| 140093717142640:3:4:6:140093646012928 |                 10253 |        51 | bookstore     | books       | PRIMARY    | RECORD    | X         | GRANTED     | 5                      |
| 140093717142640:3:4:7:140093646012928 |                 10253 |        51 | bookstore     | books       | PRIMARY    | RECORD    | X         | GRANTED     | 6                      |
+---------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+-----------+-------------+------------------------+
8 rows in set (0.00 sec)

复制

为了阅读方便

mysql> SELECT 
    ->     object_name,
    ->     index_name,
    ->     lock_type,
    ->     lock_mode,
    ->     lock_status,
    ->     lock_data
    -> FROM
    ->     performance_schema.data_locks;
+-------------+------------+-----------+-----------+-------------+------------------------+
| object_name | index_name | lock_type | lock_mode | lock_status | lock_data              |
+-------------+------------+-----------+-----------+-------------+------------------------+
| books       | NULL         | TABLE     | IX        | GRANTED     | NULL                   |
| books       | PRIMARY    | RECORD    | X         | GRANTED     | supremum pseudo-record |
| books       | PRIMARY    | RECORD    | X         | GRANTED     | 1                      |
| books       | PRIMARY    | RECORD    | X         | GRANTED     | 2                      |
| books       | PRIMARY    | RECORD    | X         | GRANTED     | 3                      |
| books       | PRIMARY    | RECORD    | X         | GRANTED     | 4                      |
| books       | PRIMARY    | RECORD    | X         | GRANTED     | 5                      |
| books       | PRIMARY    | RECORD    | X         | GRANTED     | 6                      |
+-------------+------------+-----------+-----------+-------------+------------------------+
8 rows in set (0.01 sec)

复制

1 对表加了IX 锁;

2 对记录加了NEXT-KEY X锁

3 SUPREMUM 最大索引值之后加入NEXT-KEY锁.

RR 级别

1 WHREE 主键

mysql> select * from books where id = 1 for update;
+----+------------------+-------+---------------------+
| id | title               | price | publishDate         |
+----+------------------+-------+---------------------+
|  1 | Java编程思想     | 98.50 | 2005-01-02 00:00:00 |
+----+------------------+-------+---------------------+
1 row in set (0.00 sec)

+-------------+------------+-----------+---------------+-------------+-----------+
| object_name | index_name | lock_type | lock_mode     | lock_status | lock_data |
+-------------+------------+-----------+---------------+-------------+-----------+
| books       | NULL          | TABLE     | IX            | GRANTED     | NULL      |
| books       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)

复制

通过主键加了行锁 X型索引记录.

2 WHERE 普通索引

mysql> select * from books where price = 88.50 for update;
+----+-----------------+-------+---------------------+
| id | title              | price | publishDate         |
+----+-----------------+-------+---------------------+
|  4 | C++编程思想     | 88.50 | 2004-01-09 00:00:00 |
+----+-----------------+-------+---------------------+
1 row in set (0.01 sec)

+-------------+------------+-----------+-----------+-------------+------------------------+
| object_name | index_name | lock_type | lock_mode | lock_status | lock_data              |
+-------------+------------+-----------+-----------+-------------+------------------------+
| books       | NULL          | TABLE     | IX        | GRANTED     | NULL                   |
| books       | PRIMARY    | RECORD    | X         | GRANTED     | supremum pseudo-record |
| books       | PRIMARY    | RECORD    | X         | GRANTED     | 1                      |
| books       | PRIMARY    | RECORD    | X         | GRANTED     | 2                      |
| books       | PRIMARY    | RECORD    | X         | GRANTED     | 3                      |
| books       | PRIMARY    | RECORD    | X         | GRANTED     | 4                      |
| books       | PRIMARY    | RECORD    | X         | GRANTED     | 5                      |
| books       | PRIMARY    | RECORD    | X         | GRANTED     | 6                      |
+-------------+------------+-----------+-----------+-------------+------------------------+
8 rows in set (0.01 sec)

复制

居然是全表加锁 ! 丢

3 WHERE 唯一索引

mysql> select * from books where title='C++编程思想' for update;
+----+-----------------+-------+---------------------+
| id | title              | price | publishDate         |
+----+-----------------+-------+---------------------+
|  4 | C++编程思想     | 88.50 | 2004-01-09 00:00:00 |
+----+-----------------+-------+---------------------+
1 row in set (0.01 sec)

+-------------+------------+-----------+---------------+-------------+----------------------+
| object_name | index_name | lock_type | lock_mode     | lock_status | lock_data            |
+-------------+------------+-----------+---------------+-------------+----------------------+
| books       | NULL          | TABLE     | IX                      | GRANTED     | NULL                 |
| books       | title        | RECORD    | X,REC_NOT_GAP | GRANTED     | 'C++编程思想', 4     |
| books       | PRIMARY    | RECORD    | X,REC_NOT_GAP  | GRANTED     | 4                    |
+-------------+------------+-----------+---------------+-------------+----------------------+
3 rows in set (0.01 sec)

复制

这就两个行锁了,两个索引记录锁. 简单没GAP

这是规范的表要求,有些没有主键的表情况就更加糟糕了。从这里我们看出根据普通索引，非唯一的话进行DML操作基本上是锁全表。

这比ORACLE 就差多了，非唯一索引的话，找出一个ROWID，就锁一个行，更新一行，接着再找下一个ROWID，直到索引找完，然后提交。相对来说ORACLE比较精准打击，MYSQL是范围杀伤！

有些DBA建议把MYSQL 设置成RC级别。 RC级别的MYSQL无法完成事务的隔离机制，会出现幻读，不可重复读的毛病。

//查看当前事物级别：
SELECT @@tx_isolation;
select @@transaction_isolation;

//设置read uncommitted级别：
set session transaction isolation level read uncommitted;

//设置read committed级别：
set session transaction isolation level read committed;

//设置repeatable read级别：
set session transaction isolation level repeatable read;

//设置serializable级别：
set session transaction isolation level serializable;

mysql> select * from books where publishDate='2004-01-09 00:00:00' for upate;
+----+-----------------+-------+---------------------+
| id | title           | price | publishDate         |
+----+-----------------+-------+---------------------+
|  4 | C++编程思想     | 88.50 | 2004-01-09 00:00:00 |
+----+-----------------+-------+---------------------+
1 row in set (0.00 sec)

+-------------+------------+-----------+---------------+-------------+-----------+
| object_name | index_name | lock_type | lock_mode     | lock_status | lock_data |
+-------------+------------+-----------+---------------+-------------+-----------+
| books       | NULL       | TABLE     | IX            | GRANTED     | NULL      |
| books       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 4         |
+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.01 sec)



mysql> select * from books where price = 55.70 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction



复制

第一个普通时间索引 RC下只加了个索引记录锁,

第二个SQL 要加锁就被阻塞了,虽然使用普通价格索引.

最后阻塞时查看锁类型

+-------------+------------+-----------+---------------+-------------+-----------+
| object_name | index_name | lock_type | lock_mode     | lock_status | lock_data |
+-------------+------------+-----------+---------------+-------------+-----------+
| books       | NULL         | TABLE      | IX                     | GRANTED     | NULL      |
| books       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 2         |
| books       | PRIMARY    | RECORD    | X,REC_NOT_GAP | WAITING     | 4         |
| books       | NULL          | TABLE     | IX                      | GRANTED     | NULL      |
| books       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 4         |
+-------------+------------+-----------+---------------+-------------+-----------+
5 rows in set (0.00 sec)

复制

这就奇怪了第二个语句只有第2行和第5行啊,为啥要加第4行锁?

mysql> select * from books;
+----+------------------------+-------+---------------------+
| id | title                          | price | publishDate         |
+----+------------------------+-------+---------------------+
|  1 | Java编程思想               | 98.50 | 2005-01-02 00:00:00 |
|  2 | HeadFirst设计模式      | 55.70 | 2010-11-09 00:00:00 |
|  3 | 第一行Android代码      | 69.90 | 2015-06-23 00:00:00 |
|  4 | C++编程思想                 | 88.50 | 2004-01-09 00:00:00 |
|  5 | HeadFirst Java           | 55.70 | 2013-12-17 00:00:00 |
|  6 | 疯狂Android                | 19.50 | 2014-07-31 00:00:00 |
+----+------------------------+-------+---------------------+
6 rows in set (0.01 sec)

复制

加最后一行也被阻塞

select * from books where price =19.50 for update;
+-------------+------------+-----------+---------------+-------------+-----------+
| object_name | index_name | lock_type | lock_mode     | lock_status | lock_data |
+-------------+------------+-----------+---------------+-------------+-----------+
| books       | NULL       | TABLE     | IX            | GRANTED     | NULL      |
| books       | PRIMARY    | RECORD    | X,REC_NOT_GAP | WAITING     | 4         |
| books       | NULL       | TABLE     | IX            | GRANTED     | NULL      |
| books       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 4         |
+-------------+------------+-----------+---------------+-------------+-----------+
4 rows in set (0.01 sec)

复制

加第一行也如此

 select * from books where price =98.50 for update;
+-------------+------------+-----------+---------------+-------------+-----------+
| object_name | index_name | lock_type | lock_mode     | lock_status | lock_data |
+-------------+------------+-----------+---------------+-------------+-----------+
| books       | NULL       | TABLE     | IX            | GRANTED     | NULL      |
| books       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
| books       | PRIMARY    | RECORD    | X,REC_NOT_GAP | WAITING     | 4         |
| books       | NULL       | TABLE     | IX            | GRANTED     | NULL      |
| books       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 4         |
+-------------+------------+-----------+---------------+-------------+-----------+
5 rows in set (0.00 sec)

复制

这样说来RC级别加锁的范围减小了,没有NEXT-KEY 锁. 使用普通索引也不会全表记录加锁了.如果不设置自动提交的话也容易造成阻塞,一个表并发能力只有1个事务才能操作.

mysql

文章转载自IT界数据库架构师的漂泊人生，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MYSQL锁

评论

相关阅读