解决Sybase更新表期间的并发问题
我在 Sybase 中有一个包含以下代码的过程。
begin transaction get_virtual_acc
UPDATE store_virtual_acc SET isProc = 1, Uid = @uid, DateReserv = getdate()
from store_virtual_acc (index idx_id) WHERE id = (SELECT min(id) FROM store_virtual_acc (index idx_uid) where Uid = null and isProc = null)
commit transaction get_virtual_acc
问题是，当多个用户同时调用该过程时，他们可以接收相同的 min(id) 并使用不同的值 @uid 更新表中的同一行。结果是数据失真。需要实现一个结果，即如果该行已经选择更新单个用户，则其他用户无法选择它。表具有锁定类型的数据行。
尝试使用事务级锁定如下 设置事务隔离级别3 在事务开始之前但调用过程的应用程序出现异常 java.sql.SQLException：您的服务器命令（系列 id # 0，进程 id # 530）遇到死锁情况。请重新运行您的命令。
我将不胜感激任何帮助。
I have a procedure in Sybase with the following code.
begin transaction get_virtual_acc
UPDATE store_virtual_acc SET isProc = 1, Uid = @uid, DateReserv = getdate()
from store_virtual_acc (index idx_id) WHERE id = (SELECT min(id) FROM store_virtual_acc (index idx_uid) where Uid = null and isProc = null)
commit transaction get_virtual_acc
The problem is that when the procedure is called multiple users concurently they can receive the same min(id) and update same row in the table with different value @uid. The result is a distortion of the data. It is necessary to achieve a result, that if the line has already selected for update a single user, the other can't select it. Table have lock type datarows.
Tried to use a transaction-level locking as follows
set transaction isolation level 3
before the transaction begin but aplication wich call the procedure get exception
java.sql.SQLException: Your server command (family id # 0, process id # 530) encountered a deadlock situation. Please re-run your command.
I would be grateful for any help.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
尝试这样的事情：
关键字是holdlock
Try something like this:
The keyword is holdlock