Solved

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

Posted on 2010-11-08
7
246 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query output (script) from a stored procedure 4 39
SQL Throw Error 7 35
SQL Server Compression Decision 5 46
Delete old Sharepoint backups 2 22
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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 information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

733 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