Mysql 用于更新
MySQL 支持“for update”关键字。以下是我测试它是否按预期工作的方法。我打开了 2 个浏览器选项卡并在一个窗口中执行了以下命令。
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from myxml where id = 2 for update;
....
mysql> update myxml set id = 3 where id = 2 limit 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.08 sec)
在另一个窗口中，我启动了事务并尝试对同一条记录进行更新锁定。
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from myxml where id = 2 for update;
Empty set (43.81 sec)
从上面的示例中可以看出，我在 43 秒内无法选择记录，因为交易正在由 1 号窗口中的另一个应用程序处理。交易结束后，我必须选择记录，但由于 id 2被最先执行的交易修改为id 3，没有返回任何记录。
我的问题是使用“for update”语法有什么缺点？如果我不提交在窗口 1 中运行的事务，记录会被永远锁定吗？
MySQL supports "for update" keyword. Here is how I tested that it is working as expected. I opened 2 browser tabs and executed the following commands in one window.
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from myxml where id = 2 for update;
....
mysql> update myxml set id = 3 where id = 2 limit 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.08 sec)
In another window, I started the transaction and tried to take an update lock on the same record.
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from myxml where id = 2 for update;
Empty set (43.81 sec)
As you can see from the above example, I could not select the record for 43 seconds as the transaction was being processed by another application in the Window No 1. Once the transaction was over, I got to select the record, but since the id 2 was changed to id 3 by the transaction that was executed first, no record was returned.
My question is what are the disadvantages of using "for update" syntax? If I do not commit the transaction that is running in window 1 will the record be locked for-ever?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
是的，如果事务 #1 没有提交，这些记录将被永远锁定，除非连接断开，或者 innodb 由于检测到死锁而决定回滚事务。
这不是你想要的吗？如果没有，那么您没有正确使用
SELECT ... FOR UPDATE。请参阅http://dev.mysql.com/doc/ refman/5.0/en/innodb-locking-reads.htmlYes, if transaction #1 does not commit, those records will be locked forever, unless the connection drops, or innodb decides to rollback the transaction due to a deadlock detection.
Isn't that what you want? if not, then you are not using
SELECT ... FOR UPDATEproperly. see http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html