Multiple parallel queries with multiple table locks

Question

I am trying to diagnose an issue in one of our developer's code which is blocking and just hanging around forever. The pseudo code is along the lines of:

DECLARE @a INT, @b INT, @c INT  
  
BEGIN TRAN  
  
SELECT @a = [t1].[a], @b = [t1].[b], @c = [t1].[c]  
FROM [table_1] AS [t1] WITH (XLOCK)  
JOIN [table_2] AS [t2] WITH (XLOCK) ON [t2].[c1] = [t1].[c1]  
JOIN [table_3] AS [t3] WITH (XLOCK) ON [t3].[c2] = [t1].[c2]  
JOIN [table_4] AS [t4] WITH (XLOCK) ON [t4].[c3] = [t2].[c3]  
WHERE [t1].[c4] = (SELECT [c4] FROM [table_5] WHERE [x] = 0)  
  
UPDATE [table_5] SET [x] = 1 WHERE [a] = @a, [b] = @b, [c] = @c  
  
COMMIT  

This is being called at the start of an SSIS package and 15 of these packages are running in parallel. Looking at sp_who2 I'm seeing:

• 307 is partially blocked by 321 and 338
• 321 is partially blocked by 307
• 338 is partially blocked by 321
• the other sessions are blocked by 321 and/or 338

My question is, how can this action of parallel processing be rewritten so that each session blocks the tables required in entirety and only releases them once it has finished, allowing the next block of code to do the same.

Thanks.

Answer 1

On what levels are the locks being taken?

And that is about the only useful I can say. You say that is pseudo-code, so I guess there is some variation, because it make little sense to submit 15 parallel instances of the same batch that will give the same result.

And, oh, the code you posted looks funky. There are xlock on Table_! to Table_4 in the SELECT, but not on Table_5 which is the one that is being updated! So, yeah, that looks like the simple answer: remove those XLOCK hints!

I'm sorry, but we can only work with the information you share.

Answer 2

Thanks for the response. As stated, this is the pseudo code of the actual code being used. If you look carefully, you'll see that the SELECT uses "x = 0" and the UPDATE sets "x = 1" on the returned row.

I believe the purpose is to get 15 different combinations of @a, @b and @c which will be used later in the process with the "x" column value identifying whether this combination has already been returned. Without the locking, it's probable that the same @a, @b and @c combinations will be returned since the SELECT doesn't return immediately.

Answer 3

Hi @Peter Bishop ,

Please reference this doc. fristly: resolve-and-troubleshoot-sql-blocking-chain-with-root-session

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 4

Mia - I must be missing something but that article references "fn_get_blocking_session" which I can't seem to locate.

Answer 5

So what about:

; WITH CTE AS (
    select  TOP 15 tbl4.u, tbl1.c, tbl1.s1, s2 
    from    db1.sch1.tbl1 
    join    db1.sch1.tbl2 
        on    tbl1.c = tbl2.id
    join    db1.sch1.tbl3 
        on    tbl2.id = tbl3.id
        and    tbl3.ac = 'matching'
    join    db2.sch2.tbl4 
            on tbl4.s1 = tbl1.s1
    join    db1.sch3.tbl5 
        on    tbl5.code = tbl1.s1
    where    coalesce(tbl1.ip, 0) = 0
        and    not exists
        (
            select 1
            from db1.sch3.tbl5 tbl5a
            where tbl5a.code = tbl1.s1
            and tbl5a.class = tbl3.value
        )
 )
 update db1.sch1.tbl1 
 set    ip = 1 
 from   db1.sch1.tbl1 t
 where  exists (select *
                from   cte
                where  t.s1 = cte.s1 
                  and  t.c = cte.c)

In a single operation, not fifteen in parallel.

I guess that the correct answer, though, is that you need to go back to the developer and ask him or her what this is really supposed to achieve. For this xlock thing to work, locks have to be taken on row level. I don't know the table or the indxes, but the style of the query suggests that locks will be on page or table level, and in the case the chances are not good for parallel execution.

In short: back to the drawing board!