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);

1x number;
  for i in 1..[max_id] loop
    insert into show_locks values(i);
    update [original_table] set [id] = i where id = i;
  end loop;

The 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 [last_id+1]..[max_id].

I haven’t tried it, but I didn’t want to lose the idea either.

comments powered by Disqus