sql locking problem when updating two tables consecutively in a stored procedure
Posted on 2010-11-08
I have a two part stored procedure in a transaction
First I begin the transaction
The first part updates a table with an unique value is one is not already present
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
SET Table2.Statue = 'UPDATED'
FROM Table1, Table2
WHERE Table1.UniqueValue = @unique_value
AND Table1.JoinField = Table2.JoinField
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?