, = NULL -use = N'public' /* only run if the caller is a member , = 'Exclusive' /* Shared, Update, IntentShared, SET = N'Maximum nesting level reached for SaveOrganizerDataDependenc圜hange' ĮXEC = N'SaveOrganizerDataDependenc圜hange_Resource'
This allows the procedure to call itself in a retry mechanism if it could not obtain an app lock: IF OBJECT_ID(N'dbo.SaveOrganizerDataDependenc圜hange', N'P') IS NOT NULLĭROP PROCEDURE dbo.SaveOrganizerDataDependenc圜hange It includes a parameter which is used to control nesting into the procedure to a maximum of 5 levels deep. Here is the stored procedure to do the actual upsert. You will also note I have DATA_COMPRESSION set to NONE since we are intentionally filling each page with only a single row, data compression is not required and may in fact be detrimental. This column ensures only one row is created per page, which will reduce allocation and write contention on this table.
SQL SERVER DEADLOCK KALEO WORKFLOW UPDATE
Since this table is quite likely to be very active if you have update history for multiple tables stored in it, I've added a column PadOneRowPerPage that is a char(5000). PRIMARY KEY CLUSTERED (TableID, DatabaseName) , CONSTRAINT PK_OrganizerDataDependenc圜hange IF OBJECT_ID(N'dbo.OrganizerDataDependenc圜hange', N'U') IS NOT NULLĭROP TABLE dbo.OrganizerDataDependenc圜hange ĬREATE TABLE dbo.OrganizerDataDependenc圜hange
SQL SERVER DEADLOCK KALEO WORKFLOW CODE
The example code below first creates the table that will be the target of the upsert: USE tempdb I prefer to be a bit more defensive when performing an upsert, so I use sp_getapplock and sp_releaseapplock to ensure exclusive access to the upsert'd table. If there's anything else I can share to shed more light please let me know! Isolation level is the default Read Committed. MSSQL2014 Standard, if that makes a difference. WHERE = 0 -Only run this if no rows were insertedĪnd finally a couple of the deadlock graphs I'm getting out of XE that shows the ridiculous nested triggers: WHERE TableID = -How many rows were inserted? INSERT INTO dbo.OrganizerDataDependenc圜hange Īnd here's the stored procedure: CREATE PROCEDURE varchar(150) There's only one index on the table, and it's the clustered index that completely covers the query so no lookup deadlock is possible, and though there are two statements in the proc, I specifically re-wrote it to what I understand is the best way to avoid concurrency issues, with a WHERE NOT EXISTS instead of using IF/ELSE logic, so it shouldn't be coming at this index from different angles, right? Since it's fired in triggers and cross-database I'm having some difficulty reproducing the issue, I can of course reproduce blocking but that should be fine and is what I expect.Ĭan someone help me understand what's going on here? I could probably fix it with a NOLOCK hint or maybe an applock but I still wouldn't understand why it was happening. I'm getting read-write deadlocks on the 圜hange table when two different rows on the table are being updated and I don't understand why. These triggers are fired from either Database1 or Database2, so they often are cross-database calls. On each of these tables I have a trigger that calls a stored procedure (圜hange) that simply updates the 圜hange with the time that the trigger fired.
I have a table (圜hange) that contains information about when a row in certain other tables was last changed.
0 kommentar(er)
