I had an application from another group fail, and lock a DB row that I needed to use. In the investigation, a DBA showed me this code to figure out which rows were locked.
create table show_locks(id_num number); declare 1x number; begin for i in 1..[max_id] loop insert into show_locks values(i); commit; update [original_table] set [id] = i where id = i; end loop; end;
update will throw an exception, and the max_id of the
show_locks table will contain the id of the locked row. This statement can be run again by updating the
1..[max_id] to be
I haven’t tried it, but I didn’t want to lose the idea either.