Solved

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

Posted on 2010-11-08
7
241 Views
Last Modified: 2012-05-10
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?

0
Comment
Question by:DaveChoiceTech
  • 3
  • 2
  • 2
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34084985
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34084991
Change zUPDATE for UPDATE
0
 

Author Comment

by:DaveChoiceTech
ID: 34085029
zUPDATE does not appear to be a TSQL statement in MS SQL 2005
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 21

Expert Comment

by:mastoo
ID: 34086044
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
 

Author Comment

by:DaveChoiceTech
ID: 34086411
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
 
LVL 21

Accepted Solution

by:
mastoo earned 500 total points
ID: 34086627
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
 

Author Closing Comment

by:DaveChoiceTech
ID: 34086936
The TRANS_Status was had a high cardinatliy. Reordering the indexes solved the problem.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL Delete Query 9 30
SQL Server Designer 19 39
sql server computed columns 11 29
SQL Server 2012 r2 - Varible Table 3 23
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question