![]() I'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. Lock resource for a columnstore index rowgroup.I have a Multi Instance Job Sequence (10 wide) that is started concurrently. Lock resource for online index build locks, specifically for a online index build LOB tracking table.Ī lock on an 8-kilobyte (KB) page in a database. ![]() ![]() The object can be anything that has an entry in sys.all_objects. A lock on a heap of data pages, or on the B-tree structure of an index.Ī lock on a piece of catalog information, also called metadata.Ī lock on table, stored procedure, view, etc, including all data and indexes. ItemĪ lock on an application-specified resource.Ī lock on a database, including all objects in the database. Number of lock requests per second that resulted in a deadlock. Number of lock requests per second that required the caller to wait. Total wait time (in milliseconds) for locks in the last second. Number of lock requests per second that timed out, including requests for NOWAIT locks. Number of lock requests per second that timed out, but excluding requests for NOWAIT locks. Number of new locks and lock conversions per second requested from the lock manager. SQL Server Locks countersĪverage amount of wait time (in milliseconds) for each lock request that resulted in a wait. This table describes the SQL Server Locks counters. Multiple instances of the Locks object can be monitored at the same time, with each instance representing a lock on a resource type. Minimizing locks increases concurrency, which can improve performance. For example, if an exclusive (X) lock is held on a row within a table by a transaction, no other transaction can modify that row until the lock is released. Locks are held on SQL Server resources, such as rows read or modified during a transaction, to prevent concurrent use of resources by different transactions. The SQLServer:Locks object in Microsoft SQL Server provides information about SQL Server locks on individual resource types. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |