Styjun

We get a deadlock in one of our merge statement

merge into target tt using
(select * from global_tmp_tb) hh
on( hh.pk=tt.pk)
when matched then
 update
when not matched then
 insert


---------Blocker(s)-------- ---------Waiter(s)--------- 
process session holds waits process session holds waits 
 2404 6309 X 2407 6502 S 
 2407 6502 X 2405 6372 S 
 2405 6372 X 1409 4103 S 
 1409 4103 X 2404 6309 S 

Rows waited on: 
 Session 6309: obj - rowid = 0033DA34 - AAM9o0ACfAAOH6cAAA 
 (dictionary objn - 3398196, file - 159, block - 3702428, slot - 0) 
 Session 6502: obj - rowid = 0033D9B0 - AAM9mwACfAAOJaXAAA 
 (dictionary objn - 3398064, file - 159, block - 3708567, slot - 0) 
 Session 6372: obj - rowid = 0033D9B0 - AAM9mwACfAAOJhzAAA 
 (dictionary objn - 3398064, file - 159, block - 3709043, slot - 0) 
 Session 4103: obj - rowid = 0033DDD0 - AAM93QACfAAOLC5AAA 
 (dictionary objn - 3399120, file - 159, block - 3715257, slot - 0) 

It is likely that the merge insert will cause the deadlock, so let say its update

In particular, it is waiting a S lock which is a shared lock.
But if its deadlocking on update then why it is not exclusive lock ?

Also what is all these mean

 Session 4103: obj - rowid = 0033DDD0 - AAM93QACfAAOLC5AAA 
 (dictionary objn - 3399120, file - 159, block - 3715257, slot - 0) 

What data dictionary object I need to query

Locking is very complicated and I admit there's lots about it I don't understand. A MERGE is a combination of a SELECT statement (the USING clause) and an INSERT and/or an UPDATE statement. My guess is:

1. The share (S) locks are those sessions locking the rows in a non-exclusive mode because they've checked the table for the source data of the MERGE in the USING clause of the statement.


2. The exclusive (X) locks are for an update that's been made on the table.

So I think basically what's happening is:

1. Session A gets the source data in global_tmp_tb and locks the rows in share mode.


2. Session B gets the source data in global_tmp_tb and locks the rows in share mode. Since share mode is not exclusive, two sessions can both have a share row lock with no problem.


3. Session A updates row 1 based on the ON clause. This gets an exclusive lock on the row.


4. Session B updates row 2 based on the ON clause. This gets an exclusive lock on the row.


5. Session A tries to update row 2. It can't because session B has it locked in row-exclusive mode.


6. Session B tries to update row 1. It can't because session A has it locked in row-exclusive mode.


7. Boom, deadlock.

Do you really need to get every table in global_tmp_tb for all these sessions? Can you narrow down the source data for each session with a WHERE clause?

Could you do select * from global_tmp_tb for update prior to your MERGE to get around this? This would mean that access to the merge is serialized and each session has to go in order, which may be bad.