Running Microsoft SQL 2012 standard, no compression, no partitions. Running on Windows Server 2008 R2. My loader program is VS C# 2013, pure 64 bit, .NET 4.5.2 I use Dapper 1.42 for my ORM. I do NOT use commit/rollback/transaction logic, these are just simple inserts.
At the top of the loader, I issue 'SET RECOVERY SIMPLE' (this was done to try to resolve this issue).
The loader reads a .csv, does some processing on the data, and then does an insert, really the simplest form of a program, it's only a few lines of code. The loader never does a select. The loader is the only process running on this server (test server). The loader is not multi threaded. I do commandtimeout = 0 on the insert.
I continuously keep getting 'Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction'.
The error sounds obvious, it says I have 2 processes running, and one of them is deadlocking. But I am on a test server, I am the only user, there is no other process running.
I'm at a loss of how to debug this. In my 'catch' statement, I issue this SQL command to catch what else is running, but it shows NOTHING running every time I get the error. So if nothing is running, why do I keep getting deadlocks on the insert?
SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS DatabaseName, O.Name AS LockedObjectName, P.object_id AS LockedObjectId, L.resource_type AS LockedResource, L.request_mode AS LockType, ST.text AS SqlStatementText, ES.login_name AS LoginName, ES.host_name AS HostName, TST.is_user_transaction as IsUserTransaction, AT.name as TransactionName, CN.auth_scheme as AuthenticationMethod FROM sys.dm_tran_locks L LEFT JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id LEFT JOIN sys.objects O ON O.object_id = P.object_id LEFT JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id LEFT JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id LEFT JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id LEFT JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST WHERE DB_NAME(L.resource_database_id) = DB_NAME()
In my 'catch' statement, I issue this SQL command to catch what else is running
When a deadlock occurs the victim transaction is rolled back. This releases all the locks and thus allows the competing transactions(s) to continue. Only after the rollback is complete is control returned to the victim session and an exception is raised. By the time your
catch runs the concurrent processes have probably completed.
What you're trying to do is fundamentally incorrect. Not to mention, you already have the means to capture the deadlock information:
And, as a last issue:
If the user has VIEW SERVER STATE permission on the server, the user will see all executing sessions on the instance of SQL Server; otherwise, the user will see only the current session.