I am using oracle database with Java/Hibernate, with following table:
Id| Status |....
1 PENDING
2 COMPLETED
From two different transactions, the following queries are executed:
1)select particular record, then update it
for(select some record from table){
update record status to COMPLETED
session.flush()
}
2) update records bunch
update table set status = 'PENDING' where....
(queries could be executed in any order so #1 could occurs before #2 and and vice versa)
As the result I have query 2) executed forever, so I think it is deadlock situation. Oracle Enterprise Manager shows that query 2) waits for TX lock.
How can I handle this situation?
I read about select ... for update construction(lockmode.upgrade in Hibernate), but in this case I think it will lock the entire table, so execution time will increase.