MySQL谬误集01：读不加锁

2023-07-25 20:01:08 浏览数 (2)

| 导语：生活中的问题有时“难得糊涂”，但技术问题，一是一二是二，忌讳模糊的似是而非的答案，也忌讳一刀切的简单结论。我们常常听到一些关于MySQL的说法，比如“读不加锁”，比如“单表数据要小于1000万”，比如“DDL会锁表”等，比如“单表的索引数量应该小于X个”，如果不加思考和测试就直接全盘接受，就可能犯错误，而DB上的错误又非常“昂贵”，我们应该尽量避免。所以有了想法写10-20篇文章，来思考下这些常见说法是否正确，或者说在什么条件下是正确的。水平所限，也可能文章中会有错误，欢迎大家一起探讨。第1篇文章首先分析下“读不加锁”这种说法是否正确呢？

1.Metadata Locking

若考虑元数据锁（metadata lock），读不加锁错误 。MySQL5.5引入了metadata lock，对所有查询都会加表锁（包括非事务引擘）。

开始查询事务后，以下DDL语句会被阻塞：

代码语言：javascript复制
DROP TABLE t;
ALTER TABLE t ...;
LOCK TABLE t ... WRITE;

2.存储引擘 

MySQL是支持多种存储引擘的，不同引擘结论不一样。MySQL 5.1及以前的版本，默认引擘是MyISAM，而MyISAM是表锁，读取的时候会对需要读到的所有表加共享锁，写入时加排它锁。

注：但表有查询的同时，可以插入新记录（CONCURRENT INSERT)

3.ANSI-92标准

在SQL ANSI-92标准中，这个说法是错误的 。为了保证数据一致性，Read/Write, Write/Read是相互block，我们以SQL Server为例，如下表所示：

来源：《Microsoft SQL Server 2008 Bible》

注：前4个事务隔离级别是ANSI定义的4个事务标准隔离级别，后2个是SQL Server自己定义的

SQL Server 2000及以前的版本中，完全遵循SQL ANSI-92标准，Read和Write相互block，导致SQL Server中会产生大量的blocking，性能很差。

在SQL Server 2005及以后的版本，引入了快照读，利用MVCC技术实现了Read和Write互不block。

4. MVCC

在MVCC下读不加锁的说法是有条件的正确。

注：MVCC (Multi-Version Concurrency Control) --基于多版本的并发控制协议（注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control）。

在读多写少的OLTP应用中，读写不冲突大幅增加了系统的并发性能，所以当前几乎所有的RDBMS，都支持了MVCC。

MVCC是如何实现的？引用一段文章：

来源：《高性能MySQL第三版》

举例说明：使用show engine innodb statusG; 查看innodb状态视图，在Transactions栏中看到如下提示：

代码语言：javascript复制
---TRANSACTION F5D517E, ACTIVE 0 sec
MySQL thread id 96032482, OS thread handle 0x7f362d653710, query id 261727550 127.0.0.1 admin
SELECT DISTINCT c from sbtest where id between 60180 and 60280 order by c
Trx read view will not see trx with id >= F5D517F, sees < F5D5171

 最后一行，表明当前事务可以看到事务ID<F5D5171的事务，看不到事务ID>=F5D517F的事务。

5. Serializable隔离级别

注意上面引文中的最后一句话，MVCC与Serializable隔离级别不兼容，Serializable下会对所有读取的行加锁，读不加锁不再成立！

在Serializable隔离级别下，读写冲突，并发度急剧下降，一般情况下极少使用。

我们使用sysbench压测，来对比RC和Serializable隔离级别的性能表现：

压测命令：

代码语言：javascript复制
sysbench --test=oltp --oltp-table-size=100000 --mysql-db=test --mysql-user=xxx --mysql-password='xxx' --mysql-host='127.0.0.1' --mysql-port=3306 --max-time=50 --oltp-read-only=off --max-requests=0 --num-threads=8 run

Serializable隔离级别结果：

代码语言：javascript复制
OLTP test statistics:
    queries performed:
        read:                            508485
        write:                           108486
        other:                           53684
        total:                           670655
    transactions:                        17057  (341.05 per sec.)
    deadlocks:                           19570  (391.29 per sec.)
    read/write requests:                 616971 (12336.08 per sec.)
    other operations:                    53684  (1073.39 per sec.)

Test execution summary:
    total time:                          50.0135s
    total number of events:              17057
    total time taken by event execution: 399.9381
    per-request statistics:
         min:                                  3.10ms
         avg:                                 23.45ms
         max:                                524.50ms
         approx.  95 percentile:              65.69ms

Threads fairness:
    events (avg/stddev):           2132.1250/26.28
    execution time (avg/stddev):   49.9923/0.00

 RC隔离级别结果：

代码语言：javascript复制
OLTP test statistics:
    queries performed:
        read:                            790370
        write:                           282275
        other:                           112910
        total:                           1185555
    transactions:                        56455  (1128.65 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1072645 (21444.40 per sec.)
    other operations:                    112910 (2257.31 per sec.)

Test execution summary:
    total time:                          50.0198s
    total number of events:              56455
    total time taken by event execution: 399.7345
    per-request statistics:
         min:                                  2.40ms
         avg:                                  7.08ms
         max:                                450.69ms
         approx.  95 percentile:               8.98ms

Threads fairness:
    events (avg/stddev):           7056.8750/31.53
    execution time (avg/stddev):   49.9668/0.01

可以看到平均耗时由7ms上升到24ms，而且RC下没有死锁，但在Serializable下出现了19570个死锁！

Serializable隔离级别下，查询informaction_schema看下blocking的情况：

代码语言：javascript复制
SELECT r.trx_id waiting_trx_id,  
       r.trx_mysql_thread_id waiting_thread,
       r.trx_query waiting_query,
       b.trx_id blocking_trx_id, 
       b.trx_mysql_thread_id blocking_thread,
       b.trx_query blocking_query
FROM   information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b  ON  
    b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r  ON   
    r.trx_id = w.requesting_trx_id;

可以看到如下的写被读blocking造成的锁等待： 

代码语言：javascript复制
*************************** 3. row ***************************
 waiting_trx_id: F64AEF1
 waiting_thread: 96090370
  waiting_query: UPDATE sbtest set k=k 1 where id=50265
blocking_trx_id: F64AEED
blocking_thread: 96090368
 blocking_query: SELECT DISTINCT c from sbtest where id between 50229 and 50329 order by c

6. 当前读与快照读

使用MVCC，即在RC或RR隔离级别下，有个问题是，如果想看到记录的最新版本，或者想查到记录的同时不允许其它事务更改，怎么办？

其实在MVCC并发控制的系统中，读分为快照读和当前读，快照读不加锁，但当前读是加锁的。

快照读：

代码语言：javascript复制
select ... from table where ...

 当前读：

代码语言：javascript复制
SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE

 第一句加了S锁 (共享锁)，第2句加了X锁 (排它锁)。

注意：

第2句只有在START TRANSACTION或者autocommit 设置为0的时候才加锁！

7. 总结

MySQL读不加锁是有条件的：

  1. 所有读取都会加Metadata Lock
  2. MyISAM引擘会加表锁
  3. INNODB引擘读不加锁是利用MVCC实现的
  4. Serializable隔离级别会对所有读取的行加锁
  5. MVCC下，当前读也会对读取的行加锁

公众号"数据库之巅"分享这十几年来我在数据库特别是互联网金融数据库运维走过的路和踩过的坑，欢迎大家关注。

mysql 锁

0 人点赞