oracle行锁排队时间长
当我在 oracle 9 DB 中运行下面的 Sql 命令时，我不断遇到“enq: TX - 行锁争用”。 表 mytable 是一个小表，不到 300 行。
UPDATE MYTABLE
SET col1 = col1 + :B3 ,
col2 = SYSDATE
WHERE :B2 = col3
AND :B1 = col4
我同时运行10个线程，有的要等待长达10秒才有机会更新。我知道我会面临这个死锁问题，但对我来说问题是它们应该能够更快，因为表很小，所以更新会很快。
编辑：我无法更改此代码，它位于第三方应用程序内。我只能调整数据库。
我可以做些什么来提高行锁队列的速度？ 如何改善等待时间以使线程运行得更快？
I keep running into "enq: TX - row lock contention", when I run the Sql command below in a oracle 9 DB.
The table mytable is a small table, with less than 300 lines.
UPDATE MYTABLE
SET col1 = col1 + :B3 ,
col2 = SYSDATE
WHERE :B2 = col3
AND :B1 = col4
I run 10 threads at the same time, and some wait as long as 10 seconds to get a chance to update. I know I will face this deadlock issues, but the problem for me is that they should be able to be much faster, since the table is small, so updating it would be fast.
edit:I cannot alter this code, it is inside a third party application. I can only tweak the DB.
what can I do to improve the speed of the rowlock queue?
How can I improve this waiting time so my threads run faster?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您不想被阻止，请不要尝试盲目更新。首先执行 SELECT FOR UPDATE NOWAIT。如果引发异常 (ORA-00054)，则意味着另一个会话当前正在处理该行。在这种情况下，根据您的要求，您可以尝试更新另一行（您是否正在尝试构建队列/出队过程？）、等待（dbms_lock.sleep）或向调用应用程序返回错误消息。
如果您首先使用 FOR UPDATE NOWAIT 锁定行，您将永远面临死锁。
If you don't want to be blocked don't try to update blindly. Perform a SELECT FOR UPDATE NOWAIT first. If you raise an exception (ORA-00054) this means that another session is currently working with that row. In that case, depending upon your requirement, you could try to update another row (are you trying to build a queue/dequeue process?), wait (dbms_lock.sleep) or return an error message to the calling app.
You will never face a deadlock if you lock the row with FOR UPDATE NOWAIT first.