Now I decided to simplify things and just changed my deadlock priority then went straight to MULTI_USER.
DEADLOCK DATABASE SERIES
One of the answers suggested changing the deadlock priority before running a series of ALTERs to set the database OFFLINE, back ONLINE, and then finally back to MULTI_USER mode.
From what they said, their problem, and quite possibly mine, was caused by the system trying to do an auto statistics update. Finally, though, Robert Davis ( b/ t) sent me to this link on dba.stackexchange. Now I don’t know about you but I hate rebooting a server because of a problem like this. After some discussion, it was starting to feel like we were going to have to reboot. I spent a little while trying various things and searching through forums before I went for help on twitter using the #SQLHELP hashtag. Interestingly when I tried to do the ALTER instead of just hanging I immediately got a deadlock error. Don’t ask me how.) I wasn’t able to get that exclusive access I needed. So because they were holding locks on the database (And somehow even though it was in single user there were multiple sessions with locks in the database. Why does it matter that they were system sessions? The important thing to remember here is that these sessions can not be KILLed. In my case the problem sessions were all TASK MANAGER sessions. These sessions include the LOG WRITER, RECOVERY WRITER, TASK MANAGER etc. The best way to tell is that the is_user_process flag in the sys.dm_exec_sessions DMO will be a 0. They typically (but not always) have session IDs under 50. System sessions are those created by SQL itself. Which meant I wasn’t able to get exclusive use of the database which is required to do an ALTER DATABASE to set it back into MULTI_USER.Īt this point you may have a couple of questions so let me try to answer (some of) them: How exactly was it stuck you ask? Well, 4-5 system sessions were holding locks on the database (and blocking each other). A database was stuck in single user mode. Get your copy at the MS Press Store.I had an interesting problem recently. For more information about handling deadlock exceptions, see X++ standards: try/catch Statements See alsoĪnnouncements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available.
You can then test for a deadlock exception and retry the operation.
DEADLOCK DATABASE CODE
If you must get user input during a transaction, implement a time out facility in your code so that the blocking transaction is either rolled back or committed.ĭeadlocks cannot always be avoided so be sure to put database transaction code within a try/catch block. Collect all user input before a database transaction begins to avoid blocking other transactions indefinitely. Reduce lock time by grabbing locks at the latest possible time and releasing locks as early as possible.ĭon't allow user input during a transaction. Requesting access to the CustTable and then the VendTable in one task and requesting access to the VendTable and then the CustTable in another is likely to lead to a deadlock.Įnsure that the database design is normalized. If two separate database tasks always request access to the CustTable first and then the VendTable, a task may be temporarily blocked but is less likely to be deadlocked.
Use the following coding best practices to minimize the occurrence of deadlocks:Īccess server objects in the same order each time. This allows the successful transaction to complete. Typically the database engine resolves the deadlock by selecting one of the transactions as a deadlock victim, terminating that transaction based on certain rules and returning an error. Transaction 1 is waiting for Transaction 2 to complete but it has a lock on the resources that Transaction 2 needs to complete and Transaction 2 is waiting for Transaction 1 to complete but it has a lock on the resources that Transaction 1 needs to complete creating a circular dependency.Įach database engine monitors for deadlocked transactions and follows its own rules for handling deadlocks. Transaction 2 has a lock on the VendTable and wants a lock on the CustTable but is blocked by Transaction 1. Transaction 1 has a lock on the CustTable and wants a lock on the VendTable but is blocked by Transaction 2. An example of this is when each task has obtained a lock on data that the other task needs to complete it work. Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012Ī deadlock occurs when two or more database tasks permanently block each other by maintaining a lock on a resource that the other tasks are attempting to lock.
0 kommentar(er)
