I recently came across this situation on a M$ SQL Server 2008 database:
I was doing an update operation on a certain table. While doing this update, a clustered index was being read. To be able to read from that index, a lock was obtained on a non-clustered index. The update operation went perfectly.
Now, in the same transaction, I was doing a select query which needed a lock on a non-clustered index. Unfortunately for me, this lock was already taken by the update statement and it would only be released when the transaction completed.
More details on this issue can be found on this blogpost.
At first, I knew there was a deadlock, but I didn't know on which resources/queries. This query helped me a lot while debugging:
SELECT DTL.resource_type, CASE WHEN DTL.resource_type IN ('DATABASE', 'FILE', 'METADATA') THEN DTL.resource_type WHEN DTL.resource_type = 'OBJECT' THEN OBJECT_NAME(DTL.resource_associated_entity_id, SP.[dbid]) WHEN DTL.resource_type IN ('KEY', 'PAGE', 'RID') THEN ( SELECT OBJECT_NAME([object_id]) FROM sys.partitions WHERE sys.partitions.hobt_id = DTL.resource_associated_entity_id ) ELSE 'Unidentified' END AS requested_object_name, DTL.request_mode, DTL.request_status, DEST.TEXT, SP.spid, SP.blocked, SP.status, SP.loginame FROM sys.dm_tran_locks DTL INNER JOIN sys.sysprocesses SP ON DTL.request_session_id = SP.spid --INNER JOIN sys.[dm_exec_requests] AS SDER ON SP.[spid] = [SDER].[session_id] CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS DEST WHERE SP.dbid = DB_ID() AND DTL.[resource_type] <> 'DATABASE' ORDER BY DTL.[request_session_id];
It lists the queries which are being blocked and it also tells you on which queries they are blocked (see columns spid and blocked).
Eventually, I solved the issue by handling the transaction at bean level (my bean was container managed and update / select queries where in the same bean). Before the update, I manually started a transaction. After the update was successful, I committed the transaction. Finally, I got a resultdata (select query) - without any transaction, as I didn't need a transaction for this query.