本文展示了MySQL中如何使用开始事务、SELECT ... FOR UPDATE以及REPEATABLE-READ隔离级别进行数据锁定。在Session1中，启动了一个事务并选择了手机号码在30到40之间的记录进行锁定。随后在Session2中尝试插入数据时遇到了锁等待超时错误，说明了锁定机制在并发操作中的作用。
Session 1:
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 2 |
+-----------------+
1 row in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t200 where phoneNo between 30 and 40 for update;
+----+---------+-------------+--------+
| sn | phoneNo | channelType | status |
+----+---------+-------------+--------+
| 30 | 30 | 2 | 1 |
| 34 | 34 | 3 | 2 |
| 39 | 39 | 3 | 2 |
+----+---------+-------------+--------+
3 rows in set (0.00 sec)
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql> explain select * from t200 where phoneNo between 30 and 40 for update;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t200 | ALL | t200_idx1 | NULL | NULL | NULL | 34 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
Session 2:
mysql> insert into t200 values(29,29,4,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t200 values(100,100,4,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t200 values(1000,1000,4,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t200 values(1000,1000,4,1);
扫一扫
119
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
