mysql 锁与一致性视图

参考

MySQL实战45讲

mysql技术内幕

锁

lock latch

latch是闩(shuan)锁,就是互斥量，读写锁这种控制临界资源的锁。

lock说的是数据库中的锁，用于锁定事物，包括表锁，行锁，意向锁。

innodb的锁

innodb支持行锁，有两种类型：

S锁，共享锁，允许事物读
x锁，排他锁，允许事物删除或更新

兼容性：

                    X                   S
X                 不兼容                不兼容

S                 不兼容                兼容

监测锁

show processlist

查看所有线程

MySQL [mysql]> show processlist;
+----+------+-----------+-------+---------+------+-------------+------------------+
| Id | User | Host      | db    | Command | Time | State       | Info             |
+----+------+-----------+-------+---------+------+-------------+------------------+
| 16 | root | localhost | mysql | Query   |    0 | System lock | show processlist |
| 17 | root | localhost | mysql | Sleep   |   86 |             | NULL             |
| 18 | root | localhost | mysql | Sleep   |  611 |             | NULL             |
+----+------+-----------+-------+---------+------+-------------+------------------+

information_schema.innodb_trx

查看运行的事务

| INNODB_TRX | CREATE TEMPORARY TABLE `INNODB_TRX` (
  `trx_id` varchar(18) NOT NULL DEFAULT '',
  `trx_state` varchar(13) NOT NULL DEFAULT '',
  `trx_started` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `trx_requested_lock_id` varchar(81) DEFAULT NULL,
  `trx_wait_started` datetime DEFAULT NULL,
  `trx_weight` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_mysql_thread_id` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_query` varchar(1024) DEFAULT NULL,
  `trx_operation_state` varchar(64) DEFAULT NULL,
  `trx_tables_in_use` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_tables_locked` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_lock_structs` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_lock_memory_bytes` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_rows_locked` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_rows_modified` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_concurrency_tickets` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_isolation_level` varchar(16) NOT NULL DEFAULT '',
  `trx_unique_checks` int(1) NOT NULL DEFAULT '0',
  `trx_foreign_key_checks` int(1) NOT NULL DEFAULT '0',
  `trx_last_foreign_key_error` varchar(256) DEFAULT NULL,
  `trx_adaptive_hash_latched` int(1) NOT NULL DEFAULT '0',
  `trx_adaptive_hash_timeout` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_is_read_only` int(1) NOT NULL DEFAULT '0',
  `trx_autocommit_non_locking` int(1) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8 |

MySQL [mysql]> select trx_id,trx_state,trx_query from information_schema.innodb_trx;
+-----------------+-----------+-----------+
| trx_id          | trx_state | trx_query |
+-----------------+-----------+-----------+
| 421346370501344 | RUNNING   | NULL      |
+-----------------+-----------+-----------+

information_schema.innodb_locks

MySQL [mysql]> select * from information_schema.innodb_locks;
+--------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| lock_id      | lock_trx_id | lock_mode | lock_type | lock_table   | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
| 18651:76:4:5 | 18651       | X         | RECORD    | `mysql`.`tt` | c          |         76 |         4 |        5 | 4         |
| 18650:76:4:5 | 18650       | X         | RECORD    | `mysql`.`tt` | c          |         76 |         4 |        5 | 4         |
+--------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.01 sec)

information_schema.innodb_lock_waits

MySQL [mysql]> select * from information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 18651             | 18651:76:4:5      | 18650           | 18650:76:4:5     |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

视图

一致性非锁定读

一致性非锁定读是innodb根据mvcc的方式来读取数据库中的数据。读取不需要等待对应行上的x锁的释放。读取是按照快照的形式，快照是通过undo日志实现的。

图中u1,u2,u3记录的是undo日志，v1,v2,v3,v4是根据undo日志反推出来的结果。trx_id是事务id,是事务系统维护的一个递增的值，可以用来判断版本和事务之间的关系。

在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。

在隔离级别read committed, repeatable read下，innodb使用一致性非锁定读。两种隔离级别选择快照的时间点是不一样的。

RC下，在每条语句执行前，记录快照/视图
RR下，在事物启动后，第一条查询语句执行后(或者start transaction with consistent snapshot),记录快照/视图

测试下：

MySQL [mysql]> select * from tt;
+----+------+------+-------+------+------+
| id | c    | d    | field | f2   | f3   |
+----+------+------+-------+------+------+
|  1 |    1 |    1 |  NULL | NULL | NULL |
|  2 |    2 |  200 |  NULL | NULL | NULL |
|  3 |    3 |   30 |  NULL | NULL | NULL |
| 20 |    4 |  500 |  NULL | NULL | NULL |
+----+------+------+-------+------+------+

        sessionA                        sessionB
start transaction with 
consistent snapshot;


                                        update tt set d = 0 where c = 4;

select * from tt where c = 4;
//d=500 isolation=RR
//d=0   isolation=RC

//REPEATABLE-READ
MySQL [mysql]> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

MySQL [mysql]> select * from tt where c = 4;
+----+------+------+-------+------+------+
| id | c    | d    | field | f2   | f3   |
+----+------+------+-------+------+------+
| 20 |    4 |  500 |  NULL | NULL | NULL |
+----+------+------+-------+------+------+
1 row in set (0.00 sec)

//READ-COMMITTED 
MySQL [mysql]> set session transaction isolation level READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

MySQL [mysql]> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)

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

//sessionB MySQL [mysql]> update tt set d = 20 where c = 4;
MySQL [mysql]> select * from tt where c = 4;
+----+------+------+-------+------+------+
| id | c    | d    | field | f2   | f3   |
+----+------+------+-------+------+------+
| 20 |    4 |   20 |  NULL | NULL | NULL |
+----+------+------+-------+------+------+

一致性锁定读

一致性非锁定读基于mvcc，利用快照去找对应版本的行记录，不需要等待x锁的释放。而一致性锁定读需要加锁，来取最新版本的数据。有两种形式：

select … for update
select … lock in share mode

两阶段锁

两阶段锁（two-phase locking，2PL）是数据库事务处理时的并发控制方法，以保证可串行化。[1][2]

这种方法使用数据库锁在两个阶段：

扩张阶段：不断上锁，没有锁被释放
收缩阶段：锁被陆续释放，没有新的加锁

2PL可能会导致死锁。

证明如下，太麻烦了，贴个链接不看了

CMU-15445 LAB3:事务隔离，two-phase locking，锁管理器

gap lock

放在幻读的文章总结了。

TheRiver | blog