MySQL行级锁 [英] MySQL Row level locks

问题描述

我不确定行级锁的工作方式，但这是我的问题 我有一个表T(id int，balance int)(engine = InnoDB)，我想锁定ID = 1的行，所以我开始这样的事务:

I am not sure about how the row level lock work but here is my problem I have a table T (id int , balance int ) ( engine = InnoDB ) that I want to lock rows having ID = 1, so i start a transaction like this :

start transaction ; 
select * from T where ID = 1 FOR UPDATE ; 

在发送提交之前，我想尝试一下是否将行确实锁定了.所以我开始了另一个会议，然后输入了:

Before sending commit, I wanted to try if really the rows are LOCKED. So I started an other session and I typed :

UPDATE T set balance = balance  + 100 where ID = 1 ;

在这里，我清楚地看到我在等待锁(30秒后超时).

Here I clearly see that I waiting for the lock (timeout after 30 sec).

但是当我输入时:

UPDATE T set balance = balance  + 8500 where ID = 2 ;

我也正在等待锁定，那么如何只锁定ID = 1行而不是完全锁定表?

I am also waiting for the lock, so how can I just lock the ID = 1 rows and not the table entirely?

推荐答案

您需要在id列上添加索引，以确保获得行级锁. SELECT ... FOR UPDATE锁定读取的所有行以执行查询，而不只是锁定实际选择的行.没有索引，它必须执行全表扫描，因此结果每一行都被锁定.

You need to add an index on the id column to ensure that you get a row-level lock. SELECT ... FOR UPDATE locks all the rows that are read to perform the query, not just the rows that are actually selected. Without an index, it has to perform a full table scan, so every row is locked as a result.

有了索引，它只会在该索引条目中放置一个锁，而不必读取任何其他行，因此不会锁定其他任何行.

With an index, it just puts a lock in that index entry, it doesn't have to read any other rows, so no other rows will be locked.

这篇关于MySQL行级锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！