There are set of statements under a transaction that are getting executed on Prod and DR. Ones on Prod are running fine, but on DR, they are failing with Deadlock error
Deadlock: [Txn ......] initiator locks for query - deadlock error Deadlock X locking table schema.table1. I held by [user abc] select....]. Your current isolation level is SERIALIZABLE.
Isolation level is set to 'read committed' and it is not being changed at transaction level. Set of statements in this transaction:
1. select current_session(), session_id
2. copy into gtt table
3. insert from gtt to final table
4. delete from final table (this is where it is showing deadlock)
I understand because it is showing up as SERIALIZABLE, it cannot perform delete unless we issue a commit after insert. But why is it going into SERIALIZABLE mode, how to avoid it?
Interesting, same set of transactions works fine in Prod. Any thoughts on what are the things that we can check to see what must be going wrong?