I see below details for one of the deadlock detected in oracle 12g trace files but i am not getting why deadlock is happening here ?
Deadlock happens when thread 1 acquires lock on table1 or table rows but wait for table 2 rows and at the same time thread 2 acquires lock on table 2 rows byt wait for table1 rows
But i do not see the details which session is acquired the lock on which table and waiting for which resource . Any help what are the object which got locked here and cause of it ?
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00290010-00015F75-00000000-00000000 295 1200 X 288 10 X
TX-00570012-00005D9B-00000000-00000000 288 10 X 295 1200 X
session 1200: DID 0001-0127-00014421 session 10: DID 0001-0120-00016BD1
session 10: DID 0001-0120-00016BD1 session 1200: DID 0001-0127-00014421
Rows waited on:
Session 1200: obj - rowid = 00051348 - BABRNIAARAAKfNLAAl
...
Session 10: obj - rowid = 000514F2 - BABRTyAAJAAKWbIAAY
....
----- Information for the OTHER waiting sessions -----
....
current SQL:
update employee set name=:1
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=5dfr2prw60rh1) -----
update department set address =:1 where id=:1
===================================================
Your output says the current session is trying to update a locked record in the department table (the "information for THIS session" output). The other session is trying to update every employee record (the "information for the OTHER waiting sessions" output). The current session must have updated a record in the employee table, blocking the other session, while the other session updated the record the current session is trying to update.
I assume this is some sort of exercise to cause a deadlock, since you're setting every employee record to the same name.