mysql事务 锁
mysql事务 锁
messud4312 发表于3年前
mysql事务 锁
  • 发表于 3年前
  • 阅读 108
  • 收藏 5
  • 点赞 0
  • 评论 0

简介:

在默认的情况下,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)


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

 

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,所以右边的可拿到锁

 

 
 
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;
修改记录也是不行的

 

标签: 事务
共有 人打赏支持
粉丝 2
博文 92
码字总数 54741
×
messud4312
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: