My problem is that sometimes some rows get locked for long time and I don't know who holds lock.
Ideally I want query which takes table_name and primary key value and outputs lock holder information. (e.g sql text, how long query is being running, etc)
I've found in documentation that there is no central place where row locks are stored and that this is row level metadata.
My question is: Can I get this row level metadata somehow for concrete row? (I believe this should be possible)
My usecase would be as follows:
1. try to get lock for row for id=#N (select for update semantics, actually hibernate is used for that but it shouldn't make difference here)
2. get error (now i'm getting ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired)
3. This step is what I want to add. Get information about lock holder for row with id=#N and write it into log file.
Here is query I was playing with which outputs general information about transactions having locks but it's not what I need in terms of row level locking:
select a.sql_text, l.ctime, s.STATUS, s.SQL_EXEC_START, s.PROGRAM from
v$lock l join v$session s on (l.sid = s.sid)
join v$sqlarea a on (a.hash_value = s.sql_hash_value)
where l.type='TX' and l.lmode>0;