sql locking problem when updating two tables consecutively in a stored procedure

I have a two part stored procedure in a transaction

First I begin the transaction

BEGIN TRANSACTION

The first part updates a table with an unique value is one is not already present

UPDATE Table1
SET Table1.UniqueValue = @unique_value
WHERE Table1.UniqueValue is null

The second part updates a second table based on a join with the first table where the
unique value in the first table has just been set

UPDATE Table2
SET Table2.Statue = 'UPDATED'
FROM Table1, Table2
WHERE Table1.UniqueValue = @unique_value
AND Table1.JoinField = Table2.JoinField

COMMIT

The problem is that the second update just hangs. I'm guessing this is a locking problem.
Can't I use the value set in the first update without doing a commit first? Is there a different type of locking I should use?
I hope I can since I don't want the first table to be updated unless the second one is as well?

DaveChoiceTechAsked:
Who is Participating?
 
mastooConnect With a Mentor Commented:
You confirmed with sp_who2 that it is actually running and not just blocking?
That TRANS_Status likely only has a couple possible values?  Low cardinality on an indexed column generally makes it useless as an index so you'll get a table scan.  Another way of saying that is: if half the table has SENT status sql finds it faster to just scan the table.
0
 
Anthony PerkinsCommented:
See if this makes any difference:

zUPDATE Table1
SET UniqueValue = @unique_value
WHERE UniqueValue is null

UPDATE t2
SET Statue = 'UPDATED'
FROM Table1 t1
     INNER JOIN Table2 t2 ON t1.JoinField = t2.JoinField
WHERE t1.UniqueValue = @unique_value

COMMIT
0
 
Anthony PerkinsCommented:
Change zUPDATE for UPDATE
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
DaveChoiceTechAuthor Commented:
zUPDATE does not appear to be a TSQL statement in MS SQL 2005
0
 
mastooCommented:
I'd guess that was a typo (leave out the z).
If that doesn't fix it run your sql, when it hangs use sp_who2 to see if it is blocking and which spid is blocking.  It is possible you having blocking with someone else, or maybe it isn't blocking and it is just taking a long time to execute.
0
 
DaveChoiceTechAuthor Commented:
The change did not help. The second update itself takes an extraordinary amount of time. Here is a description of the problem

This works fine but if you remove the comment on the last line it doesn't work at least not in an acceptable time frame.

UPDATE Transactions
SET Transactions.TRANS_Status=ResponseIntermediate.TRANS_STATUS,
Transactions.TRANS_RESPONSEDATE = ResponseIntermediate.TRANS_RESPONSEDATE
FROM Transactions
INNER JOIN ResponseIntermediate
ON ResponseIntermediate.TRANS_RefNumber = Transactions.TRANS_RefNumber
WHERE ResponseIntermediate.TRANS_CollectorGUID = 'BA5D2B48-A953-4891-844E-28D730526301'
AND ResponseIntermediate.TRANS_STATUS= 'PEND'
--AND Transactions.TRANS_Status = 'SENT'


The corresponding select statement returns quickly with or with out the commented clause

SELECT Transactions.*
FROM Transactions
INNER JOIN ResponseIntermediate
ON ResponseIntermediate.TRANS_RefNumber = Transactions.TRANS_RefNumber
WHERE ResponseIntermediate.TRANS_CollectorGUID = 'BA5D2B48-A953-4891-844E-28D730526301'
AND ResponseIntermediate.TRANS_STATUS= 'PEND'
AND Transactions.TRANS_Status = 'SENT'

Each of the search columns is indexed. Any ideas why adding the additional condition slows down the update but not the select?
0
 
DaveChoiceTechAuthor Commented:
The TRANS_Status was had a high cardinatliy. Reordering the indexes solved the problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.