Different Access Paths as a Source of Deadlock
Multiple transactions updating table data using different access paths can cause single query deadlocks.
In the following example, the EMP table has an ISAM base table structure on name and a hash index on empno.
1. User1 accesses the EMP table through the empno index and is granted an exclusive lock on the fourth page of the table.
2. User2 accesses the EMP table through the ISAM base table structure and is granted an exclusive lock on the third page
3. User1 needs an exclusive lock on the third page, but cannot get one because User2 already has a lock on it.
4. User2 needs an exclusive lock on the fourth page, but cannot get one because User1 already has a lock on it.