如何在 MySQL 中测试选择更新 [英] How to Test Select for Update in MySQL

问题描述

我正在对 InnoDB 表执行 SELECT ... FOR UPDATE 或行级锁定.

I am performing SELECT ... FOR UPDATE or row level locking with InnoDB tables.

我的意图是只有一个请求可以读取同一行.因此，如果两个用户同时请求相同的数据.只有其中一个获取数据，谁先触发查询.

My intention is to only one request can read the same row. So if two users make request for the same data as the same time. Only one of them get data, who fires the query first.

但是我如何测试是否放置了锁定.因为我正在通过同时检索相同的数据并且两个用户都获取数据来测试它.

But How can i test that locking is placed or not. as I am testing it by retrieving the same data at same time and both users getting the data.

注意:我的表是 InnoDB，我的查询在事务中执行，我的查询如下:

Note: My tables are InnoDB, My query executes in transaction, my query as below:

SELECT * FROM table_name WHERE cond FOR UPDATE;

我还需要检查其他任何事情才能使其工作吗?

Any other thing I have to check for this to make work?

推荐答案

打开 2 个 mysql 客户端会话.

open 2 mysql client session.

在第 1 节:

mysql> start transaction;

mysql> SELECT * FROM table_name WHERE cond FOR UPDATE;

... (result here) ...

1 row in set (0.00 sec)

在第 2 节:

mysql> start transaction;

mysql> SELECT * FROM table_name WHERE cond FOR UPDATE;

... (no result yet, will wait for the lock to be released) ...

返回会话 1，更新所选记录(并释放锁定):

back to session 1, to update selected record (and release the lock):

mysql> UPDATE table_name SET something WHERE cond;

mysql> commit;

回到会话 2:

1) 要么显示锁定超时错误

1) either showing lock timeout error

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

2) 或显示结果

... (result here) ...

1 row in set (0.00 sec)

3) 或者没有结果(因为相应的记录已经被修改，所以不满足指定条件)

3) or showing no result (because corresponding record has been modified, so specified condition was not met)

Empty set (0.00 sec)

这篇关于如何在 MySQL 中测试选择更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！