We have an online application which checks room availability. A procedure is used and it uses a table (with no constraints) temporarily to delete its contents first and then insert the searched rows (searched data using select queries in cursor). Another user from another session deletes the table data and inserts again the table data.
Some observations from deadlock graph: 1. Enqueue type is TX (after seeing the deadlock graph), so, this is definitely not locking due to unindexed foreign keys.
- The mode the lock is being waited on is 'X' (exclusive), so there is row-level locking.