Andy Niu �����ĵ�

Andy Niu

previous page next page
Andy Niu Help  1.0.0.0
变量
Mysql事务
MySQL

变量

 事务隔离级别
 
 分布式事务
 
 锁的粒度
 

详细描述

变量说明

事务隔离级别 
    read-uncommitted（未提交读）
    测试流程：
    1、A设置read-uncommitted, start transaction
    2、B执行start transaction，修改一条记录，
    3、A查询记录，得到了以为正确的记录
    4、B回滚。
    问题：A读到了B没有提交的记录，也就是脏读。

    read-committed（已提交读）
    测试流程：
    1、A设置read-committed, start transaction
    2、B执行start transaction，修改一条记录，查询记录，记录已经修改成功
    3、A查询记录，结果还是老的记录
    4、B提交事务
    5、A再次查询记录，结果是新的记录。
    问题：解决了脏读的问题，但是出现一个新问题，A在一个事务中，两次读取的记录不一致，也就是不可重复读。

    repeatable-read（可重复读）
    测试流程：
    1、A设置repeatable-read, start transaction，查询记录，结果是老的记录
    2、B执行start transaction，修改一条记录，查询记录，记录已经修改成功
    3、A查询记录，结果还是老的记录
    4、B提交事务
    5、A再次查询记录，结果还是老的记录。
    问题：可以重复读，A在事务过程中，即使B修改了数据，并且commit，A读取的还是老的数据。实际上是A读取的数据还是事务开始时的快照。
    注意：这里可能会存在一个新的问题，A在事务过程中，B增加一条记录，并提交，导致A的两次读取不一致，会多一条记录，也就是幻影读。
    这里只是可能，具体取决于数据库的实现。mysql的repeatable-read实现，不会导致幻影读。

    serializable（可串行化）
    测试流程：
    1、A设置serializable, start transaction，查询记录，结果是老的记录
    2、B执行start transaction，修改一条记录，B卡在这里，要等待A完成才行。
    3、A查询记录，结果还是老的记录，A提交。
    4、B的修改操作才进行下去。
    注意：B在等待过程中，会出现lock超时。这种情况，存在性能问题，因为要一个一个来。

    注意：事务隔离级别，是在不同事务之间隔离的概念，而不是针对当前事务。比如：
    当前事务的隔离级别是提交读，插入一条，在当前事务中可以查询出来。如果另一个事务的隔离级别也是提交读，查询不出来。

    注意：事务隔离的实现，基本上是两种方法：
    1、访问数据之前加锁，阻止其他事务对数据的访问。
    2、不加任何锁，对于一个请求，通过一定机制生成当前时间点的数据快照，并用这个快照提供一定级别的一致性数据访问。
        从用户的角度来看，好像是数据库提供了一份数据的多个版本，这种技术叫做数据多版本并发控制。
        MVCC（MultiVersion Concurrency Control）
    事务隔离是在一定程度上进行串行化处理，这与并行矛盾，要根据实际需要，进行均衡。
分布式事务 
//test为分布式事务的标识，'add student'和'add teacher'为分支标识
//创建一个分布式事务的一个分支                                        //创建一个分布式事务的另一个分支
mysql> xa start 'test','add student';                               mysql> xa start 'test','add teacher';
Query OK, 0 rows affected (0.00 sec)                                Query OK, 0 rows affected (0.00 sec)
//student表插入一条记录                                                //teacher表插入一条记录
mysql> insert student(school,name,age) value('Num_1','Andy',24);    mysql> insert teacher(name) value('Mr Wang');
Query OK, 1 row affected (0.00 sec)                                 Query OK, 1 row affected (0.00 sec)
//第一阶段提交，进入prepeare状态                                   
mysql> xa end 'test','add student';                                 mysql> xa end 'test','add teacher';
Query OK, 0 rows affected (0.00 sec)                                Query OK, 0 rows affected (0.00 sec)
mysql> xa prepare 'test','add student';                             mysql> xa prepare 'test','add teacher';
Query OK, 0 rows affected (0.07 sec)                                Query OK, 0 rows affected (0.07 sec)

//查看分支状态                                                        
mysql> xa recover\G                                                 mysql> xa recover\G
*************************** 1. row ***************************      *************************** 1. row ***************************
    formatID: 1                                                          formatID: 1
gtrid_length: 4                                                      gtrid_length: 4
bqual_length: 11                                                     bqual_length: 11
        data: testadd student                                                data: testadd student
*************************** 2. row ***************************      *************************** 2. row ***************************
    formatID: 1                                                          formatID: 1
gtrid_length: 4                                                      gtrid_length: 4
bqual_length: 11                                                     bqual_length: 11
        data: testadd teacher                                                data: testadd teacher
2 rows in set (0.00 sec)                                             2 rows in set (0.00 sec)

//第二阶段，两个分支都正常，两边都提交。如果任何一个分支出现错误，两边都进行回滚。这样就保证了分布式事务的正确。
mysql> xa commit 'test','add student';                                    mysql> xa commit 'test','add teacher';
Query OK, 0 rows affected (0.08 sec)                                      Query OK, 0 rows affected (0.08 sec)

mysql的分布式事务还存在缺陷，比如：
1、分支进入prepare状态，这个时候数据库异常，重新启动mysql，重新启动之后，可以进行提交或者回滚。
    但是提交的事务没有写入binlog（二进制日志），如果存在数据库复制，会导致主从数据库的数据不一致。
2、多个分支进入prepare状态，其中一个分支连接异常，其他的分支成功提交。
    而对于连接异常的分支，mysql的处理方式是回滚，这就导致了其他分支提交，一个分支回滚，也就是
    导致分布式事务的不完整。
锁的粒度 
1、锁的类型分为读锁和写锁，这个很好区分。可以这样认为：如果有增删改，就是写锁。如果是查询，就是读锁。
2、锁的粒度也就是锁的范围，分为行锁和表锁。锁的范围和多个因素有关，包括事务隔离级别、是否使用索引。

测试 read-committed，结果是行锁
事务A:
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED |
+------------------------+
1 row in set (0.00 sec)

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

mysql> update test set num=num+1 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

事务B:
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED |
+------------------------+
1 row in set (0.00 sec)

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

mysql> update test set num=num+1 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查询事务:
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: DAB09
trx_state: RUNNING
trx_started: 2015-07-02 08:41:28
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 3
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 320
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
*************************** 2. row ***************************
trx_id: DAB08
trx_state: RUNNING
trx_started: 2015-07-02 08:41:00
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 2
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 320
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
2 rows in set (0.00 sec)
两个事务之间没有lock wait，说明read-committed是行锁

测试 repeatable-read，结果是表锁
事务A:
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ |
+------------------------+
1 row in set (0.01 sec)

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

mysql> update test set num=num+1 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

事务B:
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ |
+------------------------+
1 row in set (0.00 sec)

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

mysql> update test set num=num+1 where id=2;

 

查询事务:
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: DAB1A
trx_state: LOCK WAIT
trx_started: 2015-07-02 09:31:29
trx_requested_lock_id: DAB1A:0:100841:2
trx_wait_started: 2015-07-02 09:31:29
trx_weight: 2
trx_mysql_thread_id: 3
trx_query: update test set num=num+1 where id=2
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 320
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
*************************** 2. row ***************************
trx_id: DAB19
trx_state: RUNNING
trx_started: 2015-07-02 09:31:21
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 2
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 320
trx_rows_locked: 4
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
2 rows in set (0.00 sec)

事务B处于lock wait状态，说明repeatable-read是表锁

测试 repeatable-read，查询条件使用主键，也就是id为primary key，结果是行锁
mysql> desc test;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| ID | int(11) | NO | | 0 | |
| NUM | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> alter table test add primary key(id);
Query OK, 0 rows affected (14.70 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| ID | int(11) | NO | PRI | 0 | |
| NUM | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

事务A:
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ |
+------------------------+
1 row in set (0.00 sec)

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

mysql> update test set num=num+1 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

事务B:
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ |
+------------------------+
1 row in set (0.00 sec)

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

mysql> update test set num=num+1 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0


查询事务:
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: DAB23
trx_state: RUNNING
trx_started: 2015-07-02 09:38:48
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 3
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 320
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
*************************** 2. row ***************************
trx_id: DAB22
trx_state: RUNNING
trx_started: 2015-07-02 09:38:45
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 2
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 320
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
2 rows in set (0.00 sec)

表test的id为主键，上面的两个事务，没有lock wait，说明通过主键操作，是行锁。为什么？

这是因为innodb主键索引（聚集索引），可以直接定位相应的行，不需要锁住整个表。对于辅助索引也是同样的道理，是行锁。因为对于辅助索引，也要使用主键索引定位到相应的行。

也就是说innodb 使用索引，只会去锁相应的行（有可能还包括当前行附近的行），而不是锁住整个表。

注意：查询加锁信息，除了表innodb_trx，还有表innodb_locks, innodb_lock_waits，这些表都在information_schema数据库
Copyright (c) 2015~2016, Andy Niu @All rights reserved. By Andy Niu Edit.
previous page start next page