Solved

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

Posted on 2010-11-08
7
249 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
[X]
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
  • 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

729 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