mysql有关事务与for update锁之间的爱恨情仇
MySQL基础属性查询
- 查询事务提交机制
mysql> select @@autocommit ;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
0 是手动提交，1是自动提交。
- 事务隔离级别(默认是可重复读)
mysql> select @@transaction_isolation ;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)
- InnoDB锁超时时间
mysql> select @@innodb_lock_wait_timeout ;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
| 50 |
+----------------------------+
1 row in set (0.01 sec)
单位：秒
- 属性模糊搜索
mysql> show variables like '%timeout%' ;
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 1 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| mysqlx_connect_timeout | 30 |
| mysqlx_idle_worker_thread_timeout | 60 |
| mysqlx_interactive_timeout | 28800 |
| mysqlx_port_open_timeout | 0 |
| mysqlx_read_timeout | 30 |
| mysqlx_wait_timeout | 28800 |
| mysqlx_write_timeout | 60 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| replica_net_timeout | 60 |
| rpl_stop_replica_timeout | 31536000 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| ssl_session_cache_timeout | 300 |
| wait_timeout | 28800 |
+-----------------------------------+----------+
23 rows in set (0.00 sec)
对于不确定名字都属性可以进行模糊搜索。
select... for update与事务的纠缠关系
关闭自动事务提交机制 & 事务隔离级别是默认可重复读
t1与t2事务顺序执行，具体流程如下：
- t1 开启事务并写入数据
mysql> begin ;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into lock_tbl values(1,1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from lock_tbl ;
+----+------+
| id | age |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.01 sec)
- t2开启事务并写入数据
mysql> begin ;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into lock_tbl values(2,2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from lock_tbl ;
+----+------+
| id | age |
+----+------+
| 2 | 2 |
+----+------+
1 row in set (0.01 sec)
- t1提交事务
mysql> commit ;
Query OK, 0 rows affected (0.02 sec)
- t2 执行查询语句
mysql> select * from lock_tbl ;
+----+------+
| id | age |
+----+------+
| 2 | 2 |
+----+------+
1 row in set (0.01 sec)
由于可重复读隔离级别，因此t2无法查询到t1提交insert的数据
- t2 加锁for update执行查询语句
mysql> select * from lock_tbl for update;
+----+------+
| id | age |
+----+------+
| 1 | 1 |
| 2 | 2 |
+----+------+
2 rows in set (0.01 sec)
此时可以查询到t1事务提交insert的数据
- t2再次执行非加锁查询
mysql> select * from lock_tbl ;
+----+------+
| id | age |
+----+------+
| 2 | 2 |
+----+------+
1 row in set (0.01 sec)
此时还是看不到t1提交insert的数据
- t2事务回滚并查询
mysql> rollback ;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from lock_tbl ;
+----+------+
| id | age |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.01 sec)
此时发现t2事务是回滚了，t2写入数据回滚掉了。但是对于t2 for update是有锁查询虽然可以看到t1写入的数据，但是t2并没有中间提交事务，而是t2加锁的for update可以看到最新数据而已。
扩展：将for update写锁换为 for share 结论同for update。
