February 22, 2022 at 9:27 am
Hi, my understanding of deadlocks is that 2 queries become deadlocked if query 1 locks table A and then wants a lock on table B while query 2 locks table B and then wants a lock on table A. We have had a deadlock where one of the queries only uses a single table, so I don't know how it can become involved in a deadlock situation. The single table query was chosen as the deadlock victim. I assume the deadlock must be due to contention on a resource other than one of the tables; could anyone shed any light on this?
Note: after a previous deadlock, both queries were amended to use the TABLOCKX hint because I thought the deadlock may be due to locks on different rows within the same table, but it still happens occasionally.
TIA
February 22, 2022 at 3:44 pm
Be real cautious about fixing deadlocks using table lock hints. It may fix the deadlock, but can cause other performance hits. Testing is your friend.
Likely, it's a parallelism deadlock. You'll see that with a single query against a single table (or even a set of tables) when it starts to fight with itself. Look at the execution plan and see if it's gone parallel.
Largely, deadlocks are a performance issue. If all transactions complete before any possibility of blocking, let alone deadlocks, you won't see the problem. This is especially the case in a parallelism deadlock. I always look at tuning opportunities in a deadlock situation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 22, 2022 at 4:47 pm
Thanks for the response. I executed the queries in SSMS to obtain the actual execution plans (see attached) and they both had Degree of Parallelism = 1. The tables involved are;
CREATE TABLE [loads].[perm_avq_json_file_load_stats](
[log_dwh_batch_id] [int] NOT NULL,
[file_path] [varchar](500) NOT NULL,
[name_of_object] [varchar](50) NULL,
[is_delta] [varchar](5) NULL,
[date_from] [varchar](20) NULL,
[date_to] [varchar](20) NULL,
[file_name] [varchar](255) NULL,
[bank_date] [varchar](20) NULL,
[timestamp_created] [varchar](50) NULL,
[bu_id] [varchar](10) NULL,
[shred_start] [datetime] NOT NULL,
[shred_finish] [datetime] NULL,
[load_start] [datetime] NULL,
[load_finish] [datetime] NULL,
[file_size] [bigint] NULL,
[object_count] [int] NULL,
[is_trans_seq_nr] [varchar](5) NULL,
[trans_seq_nr_from] [int] NULL,
[trans_seq_nr_to] [int] NULL,
[timestamp_from] [datetimeoffset](7) NULL,
[timestamp_to] [datetimeoffset](7) NULL,
[dss_create_time] [datetime] NOT NULL,
[dss_update_time] [datetime] NOT NULL
) ON [PRIMARY]
CREATE UNIQUE NONCLUSTERED INDEX [p_avq_json_load_stats_idx_x] ON [loads].[perm_avq_json_file_load_stats]
(
[log_dwh_batch_id] asc,
[file_path] asc
)
INCLUDE ([bu_id],[shred_start]) WITH (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on) ON [PRIMARY]
CREATE TABLE [loads].[load_avq_json_shredded1](
[file_path] [varchar](500) not null,
[valid_json] as (isjson([value])) persisted not null,
[int] not null,
[value] [nvarchar](max) not null,
[bu_id] [int] null,
[id] [int] null,
[tab_name] [varchar](100) null,
[trans_seq_nr] [int] null,
[evt_id] [int] null,
[evt_version_nr] [int] null
) ON [PRIMARY] textimage_on [PRIMARY]
CREATE TABLE [loads].[load_avq_json_shredded_siphon](
[file_path] [varchar](500) not null,
[name_of_object] [varchar](100) null,
[valid_json] as (isjson([value])) persisted not null,
[int] not null,
[value] [nvarchar](max) not null,
[bu_id] [int] null,
[id] [int] null,
[tab_name] [varchar](100) null,
[trans_seq_nr] [int] null,
[evt_id] [int] null,
[evt_version_nr] [int] null
) ON [PRIMARY] textimage_on [PRIMARY]
The victim query is
(@P1 varchar(89),@P2 int)
DECLARE @Filepath VARCHAR(500), @BatchId INT;
SELECT @Filepath= @P1 ,@BatchId = @P2;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
IF NOT EXISTS
(SELECT * FROM loads.perm_avq_json_file_load_stats WHERE log_dwh_batch_id = @BatchId
AND file_path = @Filepath)
INSERT loads.perm_avq_json_file_load_stats
WITH (TABLOCKX)
(log_dwh_batch_id, file_path, shred_start, dss_create_time, dss_update_time)
SELECT @BatchId, @Filepath, GETDATE(), GETDATE(), GETDATE();
ELSE
UPDATE loads.perm_avq_json_file_load_stats
WITH (TABLOCKX)
SET shred_start = GETDATE()
WHERE log_dwh_batch_id = @BatchId
AND file_path = @Filepath;
COMMIT TRANSACTION;
and the query that wins is dynamically created (@PackageInstance is an integer from 1 to 4 and there are 4 identical tables - the CREATE statement above is for the 1st one).
'INSERT loads.load_avq_json_shredded' + @PackageInstance
+ ' (file_path, , [value], bu_id, id, tab_name, trans_seq_nr, evt_id, evt_version_nr)
SELECT file_path, , [value], bu_id, id, tab_name, trans_seq_nr, evt_id, evt_version_nr
FROM (DELETE del_rows
OUTPUT Deleted.*
FROM loads.load_avq_json_shredded_siphon del_rows WITH (TABLOCKX)
INNER JOIN loads.perm_avq_json_file_load_stats fl WITH (TABLOCKX)
ON fl.log_dwh_batch_id = @PBatchID
AND fl.name_of_object = ''dtm_pos_list''
AND TRY_CAST(fl.bu_id AS INT) = del_rows.bu_id
WHERE del_rows.name_of_object = @PNameOfObject
AND del_rows.bu_id = @PBusinessUnit
AND del_rows.trans_seq_nr <= fl.trans_seq_nr_to) siphon_rows;'
Both queries are executed from SSIS packages. The siphon table is empty at present, so the INSERT above will not actually have any rows. perm_avq_json_file_load_stats has around 60k rows with 116 rows per batch ID split across 3 different values for bu_id.
February 22, 2022 at 5:03 pm
Why are you exclusively locking the table? That's almost never necessary, particularly on an INSERT. That could definitely cause blocking / locking issues.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 22, 2022 at 5:23 pm
Oh, same query, but not just a single instance of the query. Yeah, totally different. I understood you meant that only the one process and one query was involved. Instead, it's two calls to the same query.
First, 100% agreement with Scott.
Second, again, we're back to classic deadlock issues. The UPDATE could be holding locks that the INSERT needs & vice versa. Yeah, you have table locks in there (and probably shouldn't) but they're not immediately going to take effect because there are other processes going on, creating tables and the rest. You're still looking at a pretty classic deadlock scenario. It really is down to cleaning these up so they do processing to the same tables in the same order. Chuck the lock hints. Tune the queries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 22, 2022 at 6:31 pm
The table lock was an attempt to fix a previous deadlock - on the assumption that the queries were locking different groups of rows and then needing to grab more.
There are 4 identical packages running in parallel and each will use a different load_avq_json_shredded# table. The perm_avq_json_file_load_stats table is used to keep track of which files are being processed and how far each has got, so it gets updated at specific points during execution of the package and then the package loops to the next file. Each individual update affects only a single row and should be very quick. The INSERT could potentially involve a number of rows, but the source table for it is currently empty so, again, that should be quick as there's nothing to insert. I still don't see how the victim query can be part of a deadlock when it is only looking at a single table - I think that's the basic lack of understanding on my part.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply