9/2/2023 0 Comments Page lock deadlock sql serverI'm afraid that I never ever sorted this problem - my jobs run almost every night without any deadlocks and it is not too important if they fail as the missed data will be picked up the following error. From there I could then loop back into the process again via a sequencer and retry the Server Job Activity. I Know that I can add an Abort trigger on the Server Job Activity in the Job Sequence to then search through the Server Job Log.instance to look for 1205. How can I somehow trap the 1205 error and rerun the underlying Server Job ? The Job Sequence Log merely states that the underlying Server Job Aborted so there is nothing to hook onto there specific to deadlock. Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. SQL Server by default returns a code of 1205 when deadlock occurs so the Server Job Log looks like (smiley faces should be 8's)ĭeadlock_test.Get_WH_Portfolio.DW_Funds: DSD.BCIOpenR call to SQLExecDirect failed.Ĭonvert(varchar(26),latest_extract_date),Ĭonvert(char(12),convert(char(8),latest_extract_date,112)+ substring(convert(char(8),latest_extract_date,108),1,2) + substring(convert(char(8),latest_extract_date,108),4,2)),Ĭonvert(char(12),convert(char(8),getdate(),112)+ substring(convert(char(8),getdate(),108),1,2) + substring(convert(char(8),getdate(),108),4,2)) Rather than try to look for the proverbial needle in a haystack I want to find a method within datastage where I can trap deadlocks and re-run the part of the process that failed. I am assured by the DBA's/developers that no other processes (apart from Transaction Log Backups) run at this time. The rows are unique to each hash file and SQL Server by default has row level locking rather than page level. I have an order by which may cause tempoary work files to be created but I have played with this and have not been able to emulate the deadlock problem. This normally works with no problems but occasionally (no pattern) 1, 2 or 3 of the instances fall over wirh database deadlock.Īs far as I am concerned the deadlocks should not be happening as each instance is merely SELECTing data into its own hash file. Each result set is written back to a different hash file (file name based on unique parameters). The first thing the Job Sequence does is to run an underlying Server Job (again multi instance) which attaches to a SQL Server selecting between 50 - 500 rows per instance. Never do this: always specify the data type and length/precision for each parameter.I have a Multi Instance Job Sequence (10 wide) that is started concurrently. This indicates you are relying on your driver to get the right data type and size, such as with SqlClient's AddWithValue. I note one final thing: it appears that the two statements have differently sized varchar parameters. Now the UPDATE will access the exact key it needs, and place a U-lock and then an X-lock only on that, so you should now avoid a deadlock as the access will always be in the correct order. OfferStatus also needs to be there to avoid a key lookup, but it doesn't have to be a key column, it can be an INCLUDE. The reason for this particular index is that the update looks at a single SellerID value, but GlobalOfferID seems to be an IN so it's probably multiple values. (SellerID, GlobalOfferID) INCLUDE (OfferStatus) The most likely index is probably the following, but note that I don't know your table structure nor what other queries are going on, so a different index may prove better. To solve this, you need to add an index to support the UPDATE. The actual cause of the deadlock would then be that the UPDATE scans the table in one order, but the INSERT is going in a different order.This would cause it to scan the whole table, unnecessarily locking a large number of rows, and creating more opportunities for deadlocks. The UPDATE query probably does not have a supporting index.The INSERT query is unlikely to be the culprit, because it's a simple single-row insert with no joins (although it is theoretically possible that an indexed view or a foreign key Assert could cause the deadlock).Note that the Page Locks are not identical, they have different Page IDs.īut most likely what is going on here is as follows: Ideally we need to see the full XDL graph as well as the query plan to know for sure.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |