Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2010-11-08
7
244 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

792 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