Oracle PL/SQL循环中的保存点，用于停止死锁或记录锁争用
我有一个简单的过程，但我不确定如何最好地实现一个策略来停止死锁或记录锁。我正在更新游标循环中的多个表，同时调用一个也会更新表的过程。
有死锁或记录锁的问题，所以我的任务是解决程序遇到死锁或记录锁时崩溃的问题，但要休眠5分钟，并继续处理任何新记录。
完美的解决方案是跳过死锁或记录锁，继续处理其余未锁定的记录，休眠5分钟，然后在再次调用游标时拾取该记录。该程序将持续运行一天，直到被终止。
我的过程如下，我已经放入了我认为最好的东西，但是我应该在内循环而不是外循环中有异常吗？同时在内部循环中也有一个保存点？
PROCEDURE process_dist_data_fix
IS
lx_record_locked EXCEPTION;
lx_deadlock_detected EXCEPTION;
PRAGMA EXCEPTION_INIT(lx_record_locked, -54);
PRAGMA EXCEPTION_INIT(lx_deadlock_detected, -60);
CURSOR c_files
IS
SELECT surr_id
FROM batch_pre_dist_data_fix
WHERE status = 'REQ'
ORDER BY surr_id;
TYPE file_type IS TABLE OF batch_pre_dist_data_fix.surr_id%TYPE;
l_file_tab file_type;
BEGIN
LOOP
BEGIN
OPEN c_files;
FETCH c_files BULK COLLECT INTO l_file_tab;
CLOSE c_files;
IF l_file_tab.COUNT > 0
THEN
FOR i IN 1..l_file_tab.COUNT
LOOP
-- update main table with start date
UPDATE batch_pre_dist_data_fix
SET start_dtm = SYSDATE
WHERE surr_id = l_file_tab(i);
-- update tables
update_soundmouse_tables (l_file_tab(i));
END LOOP;
END IF;
Dbms_Lock.Sleep(5*60); -- sleep for 5 mins before looking for more records to process
-- if there is a deadlock or a locked record then log the error, rollback and wait 5 minutes, then loop again
EXCEPTION
WHEN lx_deadlock_detected OR lx_record_locked THEN
ROLLBACK;
Dbms_Lock.Sleep(5*60); -- sleep for 5 minutes before processing records again
END;
END LOOP;
END process_dist_data_fix;回答 1
Stack Overflow用户
发布于 2019-04-09 04:38:00
首先要明白死锁和“记录锁定”是完全不同的问题。因此，对于“记录锁定”，大多数情况下不应该有任何需要做的事情。9/10你想让程序等待一个锁。如果您等待的时间太长，那么您可能必须重新定义您的事务边界。例如，在这里，您的代码模式非常典型。你读了一份“待办事项”的清单，然后你就“去做”了。在这种情况下，你很少会想要为“记录锁定”做一些特殊的事情。如果batch_pre_dist_data_fix表格行由于某种原因被锁定，您只需等待锁定释放并继续。如果reverse为真，那么由于此作业花费了如此长的时间，并且您为另一个进程锁定batch_pre_dist_data_fix的时间如此之长，那么您可能希望重新定义事务边界。也就是说，也许你会说，在每次循环迭代之后，你都会提交。但要注意open游标在提交时的行为。
死锁是一个完全不同的动物。在这里，你总是有“另一个会话”，db检测到一种情况，你永远无法摆脱这种情况，它杀死了一个随机的会话。当会话1等待会话2的资源，会话2等待会话1的资源时也是如此，这是一个数据库可以检测到的无限等待条件，所以它会终止一个随机会话。解决这个问题的一种简单方法是，如果处理多个表的所有事务只是以相同的顺序锁定它们，我们就不会出现死锁。假设我们有表A，B，C，D。如果我们简单地决定表将按照这个顺序被锁定。也就是说，可以做A，B，D或A，C，D或C，D-，但不能做D，C。这样你就不会死锁了。要解决死锁问题，请查看发生错误时创建的转储oracle，找到另一个会话，以及该会话拥有的表的列表，并查看应该如何锁定这些表。
https://stackoverflow.com/questions/55571126
复制相似问题
腾讯云开发者
