文档章节

mysql事务 锁

messud4312
 messud4312
发布于 2015/05/21 16:56
字数 6799
阅读 118
收藏 5

简介:

在默认的情况下,MySQL在自动提交(autocommit=1/ON)模式运行,这种模式会在每条语句执行完毕后把它作出的修改立刻提交给数据库并使之永久化。事实上,这相当于把每一条语句都隐含地当做一个事务来执行。如果你想明确地执行事务,需要禁用自动提交模式并告诉MySQL你想让它在何时提交或回滚有关的修改。

我们想要明确的执行事务的话,用START TRANSACTION(或BEGIN)开启事务其实它会自动挂起自动提交事务模式(即会忽略autocommit=1),然后执行本次事务的各语句,最后用COMMIT语句结束事务并把它们做出的修改永久性记入数据库。万一事务过程中发生错误,用一条ROLLBACK语句撤销事务并把数据库恢复到事务开始之前的状态。

START TRANSACTION语句在COMMIT/ROLLBACK之后会做什么?

答:在事务被提交或回滚之后,该模式将恢复到开始本次事务的START TRANSACTION语句被执行之前的状态,这里有两种情况:1.如果自动提交模式原来是激活的,结束事务将让你回到自动提交模式

           2.如果它原来是非自动提交模式的,结束当前事务将开始下一个事务

下面实例说明

假如有表 t(a, b, c)主键为a,b为唯一索引,c为常规字段

查看是否自动提交
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
可见以上为自动提交

mysql> select * from t;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    1 |
| 2 |    2 |    2 |
+---+------+------+
2 rows in set (0.00 sec)

 

以下全为自动模式下做的实验:

1。查看自动提交模式数据提交情况。连接1,连接2都未开启事务

连接1 连接2
mysql> select * from t where b=2;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    2 |
+---+------+------+
1 row in set (0.00 sec)
 
 
mysql> select * from t where b=2;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    2 |
+---+------+------+
1 row in set (0.00 sec)
mysql> update t set c=3 where b=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t where b=2; 
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    3 |
+---+------+------+
1 row in set (0.00 sec)
 
 
mysql> select * from t where b=2;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    3 |
+---+------+------+
1 row in set (0.00 sec)
可见别的连接已看到连接1的更改

 

2. 查看自动提交模式下start transaction的作用。连接1开启事务,连接2未开启

连接1 连接2
mysql> start transaction;             
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where b=2;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    3 |
+---+------+------+
1 row in set (0.00 sec)
 
 
mysql> select * from t where b=2;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    3 |
+---+------+------+
1 row in set (0.00 sec)
mysql> update t set c=4 where b=2;  
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t where b=2; 
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    4 |
+---+------+------+
1 row in set (0.00 sec)
可见自身事务下c已为4
 
 
mysql> select * from t where b=2;  
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    3 |
+---+------+------+
1 row in set (0.00 sec)
可见连接2里c还为3,因为连接1里的事务未提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where b=2;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    4 |
+---+------+------+
1 row in set (0.00 sec)
 
 
mysql> select * from t where b=2;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    4 |
+---+------+------+
1 row in set (0.00 sec)
可见在连接1里的事务提交后,这里看到了最新的c

 

3.  连接1,连接2同时开启事务时

连接1 连接2
mysql> start transaction;         
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where b=2;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    4 |
+---+------+------+
1 row in set (0.00 sec)
 
 
mysql> start transaction;         
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where b=2;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    4 |
+---+------+------+
1 row in set (0.00 sec)
mysql> update t set c=5 where b=2; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
 
mysql> update t set c=6 where b=2; 
....
此处会被挂起等待,因为连接1里的事务已在b=2的
这条记录上给加锁了
此处有两种情况:
1. 锁等待超时那么会报错
   ERROR 1205 (HY000): Lock wait timeout exceeded; 
try restarting transaction
2. 未超时的情况,在连接1里的事务执行完后,这个继续执行

此处以未超时的情况走,在连接1里的事务commit后,
此处会自动提交update
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
 
Query OK, 0 rows affected (3.97 sec)
Rows matched: 1  Changed: 0  Warnings: 0
久违的提示信息呢。
mysql> select * from t where b=2; 
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    5 |
+---+------+------+
1 row in set (0.00 sec)
由于连接2里的事务未提交,此处查询的仍是5
 
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where b=2;  
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    6 |
+---+------+------+
1 row in set (0.00 sec)
mysql> select * from t where b=2;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    6 |
+---+------+------+
1 row in set (0.00 sec)
可见已变
 

 

4. 假如有这样的场景,连接1里给c+1,连接2里的给c-2

连接1 连接2
mysql> start transaction;            
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where b=2;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    6 |
+---+------+------+
1 row in set (0.00 sec)
 
 
mysql> start transaction;            
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where b=2;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    6 |
+---+------+------+
1 row in set (0.00 sec)
mysql> update t set c=c+1 where b=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t where b=2;   
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    7 |
+---+------+------+
1 row in set (0.00 sec)
 
 
mysql> update t set c=c-2 where b=2;
...
此处会挂起等待
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where b=2;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    7 |
+---+------+------+
1 row in set (0.00 sec)
 
 
Query OK, 1 row affected (21.60 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t where b=2;    
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    5 |
+---+------+------+
1 row in set (0.00 sec)
可见这里减2,已取到连接1里的事务的更改,
刚才的挂起等待就是为了数据的唯一性
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where b=2;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    5 |
+---+------+------+
1 row in set (0.00 sec)
mysql> select * from t where b=2;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    5 |
+---+------+------+
1 row in set (0.00 sec)
可见已为5,原值6在经过+1,-2的操作后正确值为5,对了
但是如果这里不是c=c+1, c=c-2,而是c=x的方式,那么就
有可能会覆盖原值,所以在金额等的更改上,不应该取出
来再给数据库赋值,而是在原基础上进行加减。
 

 

5. 下面测试下死锁情况

连接1-事务1 连接2-事务2
mysql> start transaction;                   
Query OK, 0 rows affected (0.02 sec)
#因为b是唯一索引,所以这里把b=2的记录给加了个行
级锁,如果查询出的为空的话,则全表加锁,如果b不
是索引,则也是全表加锁
mysql> select * from t where b=2 for update;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    5 |
+---+------+------+
1 row in set (0.02 sec)
 
 
mysql> start transaction;           
Query OK, 0 rows affected (0.02 sec)
此处
mysql> update t set c=2 where b=1;   
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> update t set c=3 where b=1;
...
此处阻塞住了
 
 
mysql> update t set c=6 where b=2; 
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
ERROR 1213 (40001): Deadlock found when trying 
to get lock; try restarting transaction
 
mysql> select * from t;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    1 |
| 2 |    2 |    5 |
+---+------+------+
2 rows in set (0.02 sec)
 
 
mysql> commit;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from t;          
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    2 |
| 2 |    2 |    6 |
+---+------+------+
2 rows in set (0.02 sec)
mysql> select * from t;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    2 |
| 2 |    2 |    6 |
+---+------+------+
2 rows in set (0.04 sec)
可见update t set c=3 where b=1;这条语句没有执行,
事务2的全部执行成功了,因为在死锁发生时,事务1被
回滚了。具体参考下面的日志信息
 

 

5.1  死锁日志分析

使用show engine innodb status\G查看死锁信息,下面只摘取了死锁信息部分,其他的省略。

 

------------------------

LATEST DETECTED DEADLOCK

------------------------

2015-05-21 16:12:55 7fe02cfd2700

*** (1) TRANSACTION:  ## 事务1

TRANSACTION 7651536, ACTIVE 218 sec starting index read  ## 事务ID=7651536, 活跃了218秒

mysql tables in use 1, locked 1

LOCK WAIT 4 lock struct(s), heap size 1248, 3 row lock(s)  ## 有3个行锁

MySQL thread id 192071, OS thread handle 0x7fe02ce0b700, query id 13896576 114.112.84.198 root updating  ## 该事务的线程ID=192071

update t set c=3 where b=1  ## 这是当前事务执行的SQL

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:  ##等待要加的锁

RECORD LOCKS space id 3864 page no 4 n bits 72 index `ib` of table `test`.`t` trx id 7651536 lock_mode X locks rec but not gap waiting  ## ## 等待在唯一索引ib上的page num=4上加一个X锁(lock_mode X locks rec but not gap)waiting意指等待的锁

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0  

 0: len 4; hex 80000001; asc     ;;

 1: len 4; hex 80000001; asc     ;;

 

*** (2) TRANSACTION:  ## 事务2

TRANSACTION 7651538, ACTIVE 200 sec starting index read  ## 事务ID=7651538, 活跃了200秒

mysql tables in use 1, locked 1

4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1  ## 4个锁,3个行锁,1个undo log 

MySQL thread id 192072, OS thread handle 0x7fe02cfd2700, query id 13896591 114.112.84.198 root updating  ## 该事务的线程ID=192072 

update t set c=6 where b=2  ## 这是当前事务执行的SQL

*** (2) HOLDS THE LOCK(S):  ## 这个事务持有的锁信息 

RECORD LOCKS space id 3864 page no 4 n bits 72 index `ib` of table `test`.`t` trx id 7651538 lock_mode X locks rec but not gap   ## 在唯一索引ib上page num=4上已持有一个X锁

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 

 0: len 4; hex 80000001; asc     ;;

 1: len 4; hex 80000001; asc     ;;

 

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:  ## 同时这个事务还等待的锁信息

RECORD LOCKS space id 3864 page no 4 n bits 72 index `ib` of table `test`.`t` trx id 7651538 lock_mode X locks rec but not gap waiting  ## 同样等待在唯一索引ib上的page num=4上加一个X锁(lock_mode X locks rec but not gap

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0  

 0: len 4; hex 80000002; asc     ;;

 1: len 4; hex 80000002; asc     ;;

 

*** WE ROLL BACK TRANSACTION (1)    ## 这里选择回滚了事务7651536

也就是事务7651536的sql没有执行

update t set c=3 where b=1

事务7651538的sql执行了

update t set c=6 where b=2

 

6. for update实验(for update是排它锁X)

注意:如果autocommit为on的状态的话,必须手动begin开启事务,否则for update锁不住

off状态的话会自动锁(因为auto模式下sql执行完了就commit了)

连接1 - 事务1 连接2 - 事务2
mysql> start transaction;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from t where b=2 for update;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    6 |
+---+------+------+
1 row in set (0.03 sec)
 
 
mysql> start transaction;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from t where b=2 for update;
...
此处阻塞住了
mysql> commit;
Query OK, 0 rows affected (0.04 sec)
 
 
+---+------+------+
| a | b    | c    |
+---+------+------+
| 2 |    2 |    6 |
+---+------+------+
1 row in set (26.91 sec)

 

7. for update范围

有如下表:

MariaDB [t]> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` varchar(255) DEFAULT NULL,
  `b` varchar(255) DEFAULT NULL,
  `c` int(10) DEFAULT NULL,
  `d` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_b` (`b`) USING BTREE,
  KEY `index_a` (`a`) USING BTREE,
  KEY `index_c` (`c`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


MariaDB [t]> SHOW VARIABLES LIKE '%AUTOCOMMIT%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [t]> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

MariaDB [t]> select * from t;
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  1 | 1    | 1    |    1 | 1    |
|  2 | 2    | 2    |    2 | 2    |
|  3 | 3    | 3    |    3 | 3    |
|  4 | 4    | 4    |    4 | 4    |
|  5 | 5    | 5    |    5 | 5    |
|  6 | 1    | 6    |    1 | 2    |
|  7 | 1    | 7    |    1 | 2    |
+----+------+------+------+------+
7 rows in set (0.00 sec)

 

7.1 事务1开事务,事务2开事务/不开事务

事务1 事务2
MariaDB [t]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [t]> select * from t where id=1 
for update;
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  1 | 1    | 1    | 1    | 1    |
+----+------+------+------+------+
1 row in set (0.00 sec)

 

 
 
# 未开启事务
MariaDB [t]> select * from t where id=1 lock 
in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; 
try restarting transaction

MariaDB [t]> select * from t where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; 
try restarting transaction

MariaDB [t]> delete from t where id=1;   
ERROR 1205 (HY000): Lock wait timeout exceeded; 
try restarting transaction

MariaDB [t]> update t set d=1 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; 
try restarting transaction

MariaDB [t]> delete from t where id=2;  
Query OK, 1 row affected (0.00 sec)

MariaDB [t]> update t set d=1 where id=3;
Query OK, 1 row affected (0.00 sec)

# 开启事务
MariaDB [t]> begin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [t]> select * from t where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; 
try restarting transaction

手动开启事务后和上面结果一样,因为autocommit虽然自动commit,
但也得先拿锁,拿锁就得等锁释放

 

7.2 事务1不开事务,事务2开事务/不开事务

事务1 事务2
MariaDB [t]> select * from t where id=1 for update;
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  1 | 1    | 1    | 1    | 1    |
+----+------+------+------+------+
1 row in set (0.00 sec)

autocommit下自动commit,所以右边的可拿到锁,
所以for update必须在开启事务的前提下使用

 

 
 
MariaDB [t]> select * from t where id=1 
for update;
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  1 | 1    | 1    | 1    | 1    |
+----+------+------+------+------+
1 row in set (0.00 sec)
事务2没开事务,能查到


MariaDB [t]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [t]> select * from t where id=1 
for update;
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  1 | 1    | 1    | 1    | 1    |
+----+------+------+------+------+
1 row in set (0.00 sec)
事务2开事务,还是能查到

 

   

 

7.3 主键索引锁一条,当查询不到记录时,不锁任何记录

事务1 事务2
MariaDB [t]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [t]> select * from t where id=1 
for update;
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  1 | 1    | 1    | 1    | 1    |
+----+------+------+------+------+
1 row in set (0.00 sec)

 

 
 
MariaDB [t]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [t]> select * from t where id=1 
for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; 
try restarting transaction
可见,id=1的被锁住了

MariaDB [t]> select * from t where id=3 
for update;  
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  3 | 3    | 3    |    3 | 1    |
+----+------+------+------+------+
1 row in set (0.00 sec)
可见id=3的没被锁住,说明主键只锁一条

MariaDB [t]> insert into t(a,b,c,d) 
values(1,2,3,3);
Query OK, 1 row affected (0.01 sec)

MariaDB [t]> select * from t;
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  1 | 1    | 1    |    1 | 1    |
|  3 | 3    | 3    |    3 | 1    |
|  4 | 4    | 4    |    4 | 4    |
|  5 | 5    | 5    |    5 | 5    |
|  6 | 1    | 6    |    1 | 2    |
|  7 | 1    | 7    |    1 | 2    |
|  9 | 1    | 2    |    3 | 3    |
+----+------+------+------+------+
7 rows in set (0.00 sec)

MariaDB [t]> delete from t where id=9;
Query OK, 1 row affected (0.00 sec)

MariaDB [t]> select * from t;         
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  1 | 1    | 1    |    1 | 1    |
|  3 | 3    | 3    |    3 | 1    |
|  4 | 4    | 4    |    4 | 4    |
|  5 | 5    | 5    |    5 | 5    |
|  6 | 1    | 6    |    1 | 2    |
|  7 | 1    | 7    |    1 | 2    |
+----+------+------+------+------+
6 rows in set (0.00 sec)

MariaDB [t]> update t set d=3 where id=3; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [t]> select * from t;
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  1 | 1    | 1    |    1 | 1    |
|  3 | 3    | 3    |    3 | 3    |
|  4 | 4    | 4    |    4 | 4    |
|  5 | 5    | 5    |    5 | 5    |
|  6 | 1    | 6    |    1 | 2    |
|  7 | 1    | 7    |    1 | 2    |
+----+------+------+------+------+
6 rows in set (0.00 sec)

 

以下为间隙锁讲解 

MariaDB [t]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [t]> select * from t where id=100 
for update;    
Empty set (0.01 sec)

MariaDB [t]> SHOW TABLE STATUS\G
*************************** 1. row *****
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 7
 Avg_row_length: 2340
    Data_length: 16384
Max_data_length: 0
   Index_length: 49152
      Data_free: 0
 Auto_increment: 12 ----当前自增id最大值
    Create_time: 2017-08-24 14:34:25
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

当前获取到next-key间隙锁
id是自增索引,当前还未自增到100,所以有可能
新插入数据的id为100,
为了确保没有幻读,加了(12,max)的间隙锁


 

 
 
MariaDB [t]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [t]> select * from t where id=1 for update;
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  1 | 1    | 1    | 1    | 1    |
+----+------+------+------+------+
1 row in set (0.01 sec)

因为锁住的是(12,max),所以前面的数据没有锁,当前可拿到锁

MariaDB [t]> insert into t(a,b,c,d) values(1,2,3,3);
ERROR 1205 (HY000): Lock wait timeout exceeded; 
try restarting transaction

因为锁住的是(12,max),所以后面的被锁住,不能插入

 

假如如下:
MariaDB [t]> select * from t where id=2 
for update;
Empty set (0.00 sec)

自增id不会再等于2,所以没有锁任何记录

 

 
 
MariaDB [t]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [t]> select * from t where id=1 for update;
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  1 | 1    | 1    |    1 | 1    |
+----+------+------+------+------+
1 row in set (0.00 sec)

MariaDB [t]> update t set d=3 where id=3; 
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

MariaDB [t]> delete from t where id=3;
Query OK, 1 row affected (0.00 sec)

MariaDB [t]> insert into t(a,b,c,d) values(8,8,8,8); 
Query OK, 1 row affected (0.00 sec)

MariaDB [t]> rollback;
Query OK, 0 rows affected (0.00 sec)

可见可操作任何操作

 

7.4 非主键索引查询,会锁住查询的记录,其余不锁

事务1:

MariaDB [t]> begin;
Query OK, 0 rows affected (0.01 sec)

MariaDB [t]> select * from t where a='1' for update;  
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  1 | 1    | 1    | 1    | 1    |
|  6 | 1    | 1    | 1    | 2    |
|  7 | 1    | 2    | 2    | 2    |
+----+------+------+------+------+
3 rows in set (0.00 sec)

MariaDB [t]> explain select * from t where a='1' for update;
+------+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id   | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                 |
+------+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | t     | ref  | index_a       | index_a | 768     | const |    3 | Using index condition |
+------+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+

查看查询计划,用的索引,所以锁全表记录

一定要注意:varchar的索引如果查询时不用''单引号包起来不会走索引
MariaDB [t]> explain select * from t where a=1 for update;   
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | t     | ALL  | index_a       | NULL | NULL    | NULL |   17 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

事务2:

MariaDB [t]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [t]> select * from t where a=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> select * from t where a='1' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> select * from t where a='3' for update; 
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  3 | 3    | 3    |    3 | 3    |
+----+------+------+------+------+
1 row in set (0.00 sec)
MariaDB [t]> select * from t where b='1' for update; 
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> select * from t where b='3' for update; 
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  3 | 3    | 3    |    3 | 3    |
+----+------+------+------+------+
1 row in set (0.00 sec)
MariaDB [t]> select * from t where id=1 for update; 
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> select * from t where id=3 for update;      
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  3 | 3    | 3    |    3 | 3    |
+----+------+------+------+------+
1 row in set (0.00 sec)
可见,只锁住了查询出来的3条记录,其余都未加锁

MariaDB [t]> insert into t(a,b,c,d) values('1','2',3,'3');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> insert into t(a,b,c,d) values('2','2',3,'3');
Query OK, 1 row affected (0.00 sec)
可见插入时,a为'1'值的记录插入不进去,其余都可以

MariaDB [t]> update t set d='3' where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> update t set d='3' where id=3;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
可见修改记录时也是,如果此条记录被锁住的话需要等待锁

7.5 非索引字段锁全部记录

事务1

MariaDB [t]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [t]> select * from t where d='1' for update;         
+----+------+------+------+------+
| id | a    | b    | c    | d    |
+----+------+------+------+------+
|  1 | 1    | 1    |    1 | 1    |
+----+------+------+------+------+
1 row in set (0.00 sec)

MariaDB [t]> explain select * from t where d='1' for update;
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |   18 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
可见全表扫描

事务2

MariaDB [t]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [t]> select * from t where a='1' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> select * from t where a='1' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> select * from t where a='3' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> select * from t where b='1' for update; 
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> select * from t where id=1 for update; 
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [t]> select * from t where id=3 for update; 
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
非主键索引时锁全表的记录,其他记录都获取不到锁(包括主键索引),所以一直等待到超时

MariaDB [t]> insert into t(a,b,c,d) values('1','2',3,'3');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
非主键索引时锁全表的记录,可见插入不可插

MariaDB [t]> update t set d='3' where id=3;
修改记录也是不行的

8.幻读

在可重复读的隔离级别下,可以解决不可重复读,以及幻读(按定义是有幻读的,但mysql通过mvcc解决了(一致性读是通过 MVCC 为查询提供了一个基于时间的点的快照。这个查询只能看到在自己之前提交的数据,而在查询开始之后提交的数据是不可以看到的。在默认隔离级别REPEATABLE READ下,同一事务的所有一致性读只会读取第一次查询时创建的快照), 但加锁读还是可以读到的),见下

MariaDB [demo]> desc user;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | varchar(50)  | NO   | PRI | NULL    |       |
| name        | varchar(255) | YES  |     | NULL    |       |
| create_time | datetime     | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
事务1 事务2
MariaDB [demo]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [demo]> select * from user;
+----+------+---------------------+
| id | name | create_time         |
+----+------+---------------------+
| zq | zq1  | 2017-12-05 18:47:26 |
+----+------+---------------------+
1 row in set (0.00 sec)

 

 
 
MariaDB [demo]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [demo]> select * from user;
+----+------+---------------------+
| id | name | create_time         |
+----+------+---------------------+
| zq | zq1  | 2017-12-05 18:47:26 |
+----+------+---------------------+
1 row in set (0.00 sec)

 

MariaDB [demo]> insert into user(id, name) 
values("zq2", "zq2");
Query OK, 1 row affected (0.00 sec)

MariaDB [demo]> select * from user;
+-----+------+---------------------+
| id  | name | create_time         |
+-----+------+---------------------+
| zq  | zq1  | 2017-12-05 18:47:26 |
| zq2 | zq2  | NULL                |
+-----+------+---------------------+
2 rows in set (0.00 sec)

 

 
 
MariaDB [demo]> select * from user;
+----+------+---------------------+
| id | name | create_time         |
+----+------+---------------------+
| zq | zq1  | 2017-12-05 18:47:26 |
+----+------+---------------------+
1 row in set (0.00 sec)

此处查看不到事务1未commit的数据,解决脏读

 

MariaDB [demo]> commit;
Query OK, 0 rows affected (0.02 sec)

 

 
 
MariaDB [demo]> select * from user;
+----+------+---------------------+
| id | name | create_time         |
+----+------+---------------------+
| zq | zq1  | 2017-12-05 18:47:26 |
+----+------+---------------------+
1 row in set (0.00 sec)
此处事务1已commit数据,但还是看不到,保证
了可重复复读以及幻读问题

 

 
MariaDB [demo]> select * from user for update;
+-----+------+---------------------+
| id  | name | create_time         |
+-----+------+---------------------+
| zq  | zq1  | 2017-12-05 18:47:26 |
| zq2 | zq2  | NULL                |
+-----+------+---------------------+
2 rows in set (0.00 sec)
但如果加锁读的话是能看到的
其实,可重复读和提交读是矛盾的。在同一个事务里,
如果保证了可重复读,就会看不到其他事务的提交,
违背了提交读;如果保证了提交读,
就会导致前后两次读到的结果不一致,违背了可重复读。

InnoDB提供了这样的机制,在默认的可重复读的隔离级别里,
可以使用加锁读去查询最新的数据。

这时就会对当前读数据加锁,像查全表是加全部锁

 

 
MariaDB [demo]> commit;
Query OK, 0 rows affected (0.01 sec)

 

 

8.1 那就来详细分析下RR隔离级别下是如何防止幻读的

幻读问题是指一个事务的两次不同时间的相同查询返回了不同的的结果集。例如:一个 select 语句执行了两次,但是在第二次返回了第一次没有返回的行,那么这些行就是“phantom” row.

read view(或者说 快照读 或者说MVCC)实现了一致性不锁定读(Consistent Nonlocking Reads),从而避免了幻读

深一点这里讲解也不错

首先读分为: 
快照读 
select * from table where ?;

当前读:特殊的读操作,插入/更新/删除操作,属于当前读,需要加锁。 
select * from table where ? lock in share mode; 
select * from table where ? for update; 
insert into table values (…); 
update table set ? where ?; 
delete from table where ?;

所有以上的语句,都属于当前读,读取记录的最新版本。并且,读取之后,还需要保证其他并发事务不能修改当前记录,对读取记录加锁。其中,除了第一条语句,对读取记录加S锁 (共享锁)外,其他的操作,都加的是X锁 (排它锁)。

对于快照读来说,幻读的解决是依赖mvcc解决。而对于当前读则依赖于gap-lock解决(因为间隙锁会锁定当前查询间隙中暂时不存在的数据,其他事务想插入或删除或修改都会有当前这个间隙锁而被hang住)。

需要注意的是快照读是在select查询后而不是事务开启后

看下面一个异常现象(也不算异常了)

SESSION_A开始事务并创建快照,或一个普通的查询也会创建一个快照读
SESSION_A>START TRANSACTION WITH CONSISTENT SNAPSHOT;
Query OK, 0 rows affected (0.00 sec)

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


SESSION_A>select * from read_view;
+-------------------------+
| text                    |
+-------------------------+
| init                    |
| after session A select  |
| before Session_A select |
+-------------------------+
3 rows in set (0.00 sec)


SESSION_B>insert into read_view values('anomaly'),('anomaly');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

SESSION_B>update read_view set text='INIT' where text='init';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

SESSION_A>select * from read_view;
+-------------------------+
| text                    |
+-------------------------+
| init                    |
| after session A select  |
| before Session_A select |
+-------------------------+
3 rows in set (0.00 sec)

# SESSION_A更新了它并没有"看"到的行
SESSION_A>update read_view set text='anomaly!' where text='anomaly';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

# 这里居然看到了SESSION_B commit的数据了
SESSION_A>select * from read_view;
+-------------------------+
| text                    |
+-------------------------+
| init                    |
| after session A select  |
| before Session_A select |
| anomaly!                |
| anomaly!                |
+-------------------------+
5 rows in set (0.00 sec)

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

SESSION_A>select * from read_view;
+-------------------------+
| text                    |
+-------------------------+
| INIT                    |
| after session A select  |
| before Session_A select |
| anomaly!                |
| anomaly!                |
+-------------------------+
5 rows in set (0.00 sec)

观察实验步骤可以发现,在倒数第二次查询中,出现了一个并不存在的状态,这里A的前后两次读,均为快照读,而且是在同一个事务中。但是B先插入直接提交,此时A再update,update属于当前读,所以可以作用于新插入的行,并且将修改行的当前版本号设为A的事务号,所以第二次的快照读,是可以读取到的,因为同事务号。这种情况符合MVCC的规则,如果要称为一种幻读也非不可,算为一个特殊情况来看待吧。

InnoDB通过Nextkey lock解决了当前读时的幻读问题,就是读取时采用当前读

Innodb行锁分为:

类型 说明
Record Lock: 在索引上对单行记录加锁.
Gap Lock: 锁定一个范围的记录,但不包括记录本身.锁加在未使用的空闲空间上,可能是两个索引记录之间,也可能是第一个索引记录之前或最后一个索引之后的空间.
Next-Key Lock: 行锁与间隙锁组合起来用就叫做Next-Key Lock。锁定一个范围,并且锁定记录本身。对于行的查询,都是采用该方法,主要目的是解决幻读的问题。
 

假如有下

(mysql@localhost) [fandb]> create table t5(id int,key(id));
Query OK, 0 rows affected (0.02 sec)

(mysql@localhost) [fandb]> insert into t5 values(1),(4),(7),(10);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

 开始实验

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

SESSION_A>select * from t5;
+------+
| id   |
+------+
|    1 |
|    4 |
|    7 |
|   10 |
+------+
4 rows in set (0.00 sec)

SESSION_A>select * from t5 where id=7 for update;
+------+
| id   |
+------+
|    7 |
+------+
1 row in set (0.00 sec)


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

SESSION_B>insert into t5 values(2);
Query OK, 1 row affected (0.00 sec)

SESSION_B>insert into t5 values(12);
Query OK, 1 row affected (0.00 sec)

SESSION_B>insert into t5 values(4); --被阻塞
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
^[[AERROR 1317 (70100): Query execution was interrupted

SESSION_B>insert into t5 values(5); --被阻塞
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
^[[AERROR 1317 (70100): Query execution was interrupted

SESSION_B>insert into t5 values(7); --被阻塞
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

SESSION_B>insert into t5 values(9); --被阻塞
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

SESSION_B>insert into t5 values(10);
Query OK, 1 row affected (0.00 sec)


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


SESSION_A>select * from t5;
+------+
| id   |
+------+
|    1 |
|    4 |
|    7 |
|   10 |
+------+
4 rows in set (0.00 sec)

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

SESSION_A>select * from t5;
+------+
| id   |
+------+
|    1 |
|    2 |
|    4 |
|    7 |
|   10 |
|   10 |
|   12 |
+------+
6 rows in set (0.00 sec)

当以当前读模式select * from t5 where id=7 for update;获取 id=7的数据时,产生了 Next-Key Lock,锁住了4-10范围和 id=7单个record 
从而阻塞了 SESSION_B在这个范围内插入数据,而在除此之外的范围内是可以插入数据的。 
在倒数第二个查询中,因为 read view 的存在,避免了我们看到 2和12两条数据,避免了幻读 
同时因为 Next-Key Lock 的存在,阻塞了其他会话插入数据,因此当前模式读不会产生幻读(select for update 是以当前读模式获取数据)

尽量使用唯一索引,因为唯一索引会把Next-Key Lock降级为Record Lock

 

© 著作权归作者所有

共有 人打赏支持
messud4312
粉丝 5
博文 128
码字总数 102552
作品 0
海淀
后端工程师
私信 提问
MySQL MVCC && 事务隔离级别 && 锁

MySQL MVCC && 事务隔离级别 && 锁 InnoDB多版本并发控制——MVCC http://my.oschina.net/xinxingegeya/blog/208821 MySQLS锁X锁read lockwrite lock http://my.oschina.net/xinxingegeya/bl......

秋风醉了
2014/07/30
0
0
MySQL中的共享锁与排他锁

在MySQL中的行级锁,表级锁,页级锁中介绍过,行级锁是Mysql中锁定粒度最细的一种锁,行级锁能大大减少数据库操作的冲突。行级锁分为共享锁和排他锁两种,本文将详细介绍共享锁及排他锁的概念、...

steven
2016/09/23
32
0
mysql 开发进阶篇系列 6 锁问题

一.概述   在数据库中,数据是属于共享资源,为了保证并发访问的一致性,有效性,产生了锁。接下来重点讨论mysql锁机制的特点,常见的锁问题,以及解决mysql锁问题的一些方法或建议。 相比...

花阴偷移
07/23
0
0
使用mysql中的锁解决高并发问题

为什么要加锁 多核计算机的出现,计算机实现真正并行计算,可以在同一时刻,执行多个任务。在多线程编程中,因为线程执行顺序不可控导致的数据错误。比如,多线程的理想状态是这样的 但是实际情...

紫葡萄0
11/23
0
0
InnoDB中的锁

InnoDB 中的锁 本文翻译自mysql5.6官方文档:InnoDB Locking 本文介绍InnoDB包含的锁的种类: 共享锁(Shared Lock)和 排他锁(Exclusive Lock) 意向锁(Intention Locks) Record Locks Gap Lo...

Gen_zhou
2016/07/21
98
0

没有更多内容

加载失败,请刷新页面

加载更多

新手也能看懂,消息队列其实很简单

该文已加入开源项目:JavaGuide(一份涵盖大部分Java程序员所需要掌握的核心知识的文档类项目,Star 数接近 16k)。地址:https://github.com/Snailclimb/JavaGuide. 本文内容思维导图: 消息...

阿里云官方博客
8分钟前
0
0
如何在Chrome浏览器中启动deviceready事件(尝试调试phonegap项目)?

我正在开发PhoneGap应用程序,我希望能够在Chrome中调试它,而不是在电话上调试。但是,我在onGetReady()函数中初始化我的代码,该函数在PhoneGap触发“deviceready”事件时触发。由于Chr...

kisshua
今天
9
0
nginx中部署vue打包后的静态文件

如何在nginx中部署静态资源就不描述了, 请看我的这篇博客 将vue脚手架项目打包后的静态文件放到nginx上, 发现有个问题, 即url上有#, 怎么去掉这个#呢. 1 项目中router的mode 路由的mode要为h...

克虏伯
今天
13
0
JS容易理解错误的地方

在这端代码执行的末尾,你会不会hi变量回事函数中的hi了?你会不会认为这不是按引用传递了? 对值传递和引用传递产生质疑了? 1 var hi = {};2 function sayHello(hi) { ...

器石_
今天
8
0
Java开发学习--MongoDB

之前只学过sql,第一次使用非关系型数据库。以前对于关系型数据库与非关系型数据库的概念很模糊,通过这次的学习对这两者有了一个清晰的概念。 主键 在MongoDB中,主键名叫"_id",如果在生成...

微笑向暖wx
今天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部