MySQL 如何锁定单行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3878672/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to lock a single row
提问by svk
I have a usertable with field lastusedecnumber.
我有一个user带有字段的表lastusedecnumber。
I need to access and increment lastusedecnumber.
我需要访问和递增lastusedecnumber。
During that accessing time I need to lock that particular user row (not the entire table).
在访问期间，我需要锁定该特定用户行（而不是整个表）。
How do I do this?
我该怎么做呢？
The table type is MyISAM.
表类型是MyISAM.
回答by belwood
MySQL uses only table-level locking from MyISAM tables. If you can, switch to InnoDB for row-level locking.
MySQL 仅使用来自 MyISAM 表的表级锁定。如果可以，请切换到 InnoDB 进行行级锁定。
Here's a link to the MySQL site describing Locks set by SQL Statements for InnoDB tables. http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html
这是 MySQL 站点的链接，描述了由 SQL 语句为 InnoDB 表设置的锁。 http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html
回答by Aleksandr Guidrevitch
Kind of late, but hope it will help someone:
有点晚了，但希望它会帮助某人：
UPDATE user SET lastusedecnumber = LAST_INSERT_ID(lastusedecnumber + 1);
SELECT LAST_INSERT_ID();
Will give you atomic increment of lastusedecnumber and ability to read new value of lastusedecnumberfield (after increment) using SELECT LAST_INSERT_ID().
将为您提供 lastusedecnumber 的原子增量以及lastusedecnumber使用SELECT LAST_INSERT_ID().
回答by dhh
As a workaround you could add a column to your table, like locked TINYINT(1)- whenever you want the row to be locked you set it to 1. When you now try to access this row, the first thing you do is check if the lockedfields is set.
作为一种解决方法，您可以向表中添加一列，例如locked TINYINT(1)- 无论何时您希望该行被锁定，您都可以将其设置为1. 当您现在尝试访问此行时，您要做的第一件事是检查是否locked设置了字段。
To unlock the row, simply set it to 0again. Not nice but a really simple workaround.
要解锁该行，只需将其设置为0再次。不好，但一个非常简单的解决方法。
回答by oyvey
I didn't feel like converting my whole database from myisam. So I simply try to create a new table named based on the id of the record I want to lock. If create table is successful, do my work and delete the table at the end. If create table not successful, stop.
我不想从 myisam 转换我的整个数据库。所以我只是尝试创建一个基于我想要锁定的记录的 id 命名的新表。如果创建表成功，请执行我的工作并在最后删除该表。如果创建表不成功，停止。
回答by Barnaby Dawson
A better workaround is to create a column containting a timestamp. Whenever you want to lock the row you update it to the current time. To unlock update to a time at least x minutes in the past. Then to check if its locked check that the time stamp is at least x minutes old.
更好的解决方法是创建一个包含时间戳的列。每当您想锁定该行时，您都将其更新为当前时间。解锁更新到过去至少 x 分钟的时间。然后检查其锁定检查时间戳是否至少为 x 分钟。
This way if the process crashes (or the user never completes their operation) the lock effectively expires after x minutes.
这样，如果进程崩溃（或用户从未完成他们的操作），锁会在 x 分钟后有效地过期。
