I need help on a good locking and rollback strategy

Question

I need help on a good locking and rollback strategy

Claude Parent

Grasshopper

Points: 22
More actions
November 7, 2006 at 9:14 am

#103265

Hi all,

I have a serie of transactions that might need a rollback if anything goes wrong. This means that I have to put a BEGIN TRAN (it is in ASP) at some strategic place, and do the same for the END TRAN.

The problem here is that within those two instructions, I need to have a SELECT on a table that will bring back more than one record. I then need to go through each of these records to build an UPDATE statement on each of the same records that have been selected.

I therefore get a locking problem since that my records that I want to update are locked by the SELECT statement. Any hint on how we could get to a good result and keep some good performance?

Thanks for your help.

Claude

Here is some code example:
----------------------------------------------------------
Sub UpdateExpeditionLinesForBoxStrings(indIdExpedition)
Dim strSQL, rsSQL, intBoxLineCount
BEGIN TRAN

'Some updates happens here before we get to the main section of the SELECT and UPDATE problem
'These updates needs to be rolled back if something wrong happens in the SELECT and UPDATE problem
Update .....
Update ....

....
' We get the expedition lines using a non unique key
strSQL = ""
strSQL = strSQL & "SELECT IdCustomerOrder, IdCustomerOrderLine, IdProduct "
strSQL = strSQL & "FROM ExpeditionLine "
strSQL = strSQL & "WHERE IdExpedition=" & cstr(indIdExpedition) & " "

set rsSQL = dbLisa.execute(strSQL)

While (not rsSQL.eof)

  ' For each record found, we build a SQL Update statement...
  strBoxExpFunction=""
  strBoxExpFunction = ReturnBoxString(rsSQL("IdCustomerOrder"),rsSQL("IdCustomerOrderLine"))

  ' And update the record
  strSQL = ""
  strSQL = strSQL & "UPDATE ExpeditionLine "
  strSQL = strSQL & "SET BoxExp='" & replace(strBoxExpFunction,"'","''") & "' "
  strSQL = strSQL & "WHERE IdExpedition=" & cstr(intIDExpedition) & " "
  strSQL = strSQL & "AND IdCustomerOrder=" & rsSQL("IdCustomerOrder") & " "
  strSQL = strSQL & "AND IdCustomerOrderLine=" & rsSQL("IdCustomerOrderLine") & " "
  strSQL = strSQL & "AND IdProduct=" & rsSQL("IdProduct") & " "

  dbLisa.execute strSQL

Wend ' of While (not rsSQL.eof).

END TRAN
'As you can see we are trying to update our recordset but it is locked, it seems, by the BEGIN TRAN

End sub ' de Sub UpdateExpeditionLinesForBoxStrings(intIdCustomerOrder)

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Based on the code you've posted, it is unclear as to exactly what you are trying to accomplish. What are you doing with the results from the SELECT? Is there any reason why you need to SELECT...then UPDATE and not just UPDATE?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Robert Davis One Orange Chip Points: 28027 More actions · Answer 2

Create a stored procedure and allow the transaction to be rolled back within the procedure. And like John said, do 1 query, not two.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Claude Parent Grasshopper Points: 22 More actions · Answer 3

The reason why the whole thing is not in only one SQL statement is that with each record found in the select statement, we need to go get more data and do some formatting and other things (function ReturnBoxString). You can see the function below, unfortunatly with some french only comments)

I guess my question would be, is it possible to do a SELECT statement within the BEGIN and END TRAN without locking the resultset?

--------------

code example

Function ReturnBoxString(intIdCustomerOrder, intIdCustomerOrderLine)

' Description : Génère la string des numéros de boîte qui sera enregistrée dans l'expédition

' The boxes must be formated this way: [# package]-[#box]

Dim strSQL, rsSQL, strIdLicenseIN, intX, strPrefix

Dim intCurrentBoxNumber, intCurrentLocationNumber

Dim strTrimmedLocationName, strLocationPrefix, strBoxNumberPrefix

Dim strTemporaryLocationName

ReturnBoxString=""

'First, we need to select all records of ClosingBox where we will find

'the product of the order lines in the scanned license plate. We will then sort them

'by package # - box number to be able to concenate them more easily

' On génère la string des numéros de license plate scannées

strIdLicenseIN = "(-1"

strPrefix=", "

for intX=1 to ccur(Session("NumLicenses"))

strIdLicenseIN = strIdLicenseIN & strPrefix & cstr(Session("LicenseID" & cstr(intX)))

strPrefix=", "

Next ' de for intX=1 to ccur(Session("NumLicenses"))

strIdLicenseIN=strIdLicenseIN & ")"

' We generate the SQL statement that will retreive the records needed

strSQL = "SELECT DISTINCT ISNULL(Locations.Name,'') AS LocationName, "

strSQL = strSQL & "ISNULL(ClosingBox.BoxNumber,0) AS BoxNumber "

strSQL = strSQL & "FROM ClosingBox "

strSQL = strSQL & "LEFT OUTER JOIN Locations ON Locations.IdLocation=ClosingBox.IdLocation "

strSQL = strSQL & "WHERE ClosingBox.IdCustomerOrder=" & cstr(intIdCustomerOrder) & " "

strSQL = strSQL & "AND ClosingBox.IdLine=" & cstr(intIdCustomerOrderLine) & " "

strSQL = strSQL & "AND ClosingBox.IdLocation IN " & strIdLicenseIN & " "

strSQL = strSQL & "ORDER BY ISNULL(Locations.Name,'') ASC, ISNULL(ClosingBox.BoxNumber,0) ASC "

set rsSQL = dbLisa.execute(strSQL)

intCurrentBoxNumber=0

intCurrentLocationNumber=0

strLocationPrefix=""

strBoxNumberPrefix=""

' On boucle toutes les lignes

While (not rsSQL.eof)

' #1 We need to remove some characters that do not need to be displayed

if (rsSQL("LocationName")<>"") then

strTrimmedLocationName = right(rsSQL("LocationName"),len(rsSQL("LocationName"))-4)

' On elève les zéros devant le chiffre

while (mid(strTrimmedLocationName,1,1)="0")

strTrimmedLocationName=right(strTrimmedLocationName,len(strTrimmedLocationName)-1)

Wend ' de while (mid(strTrimmedLocationName,1,1)="0")

Else ' de if (rsSQL("LocationName")<>"") then

strTrimmedLocationName="0"

End if ' de if (rsSQL("LocationName")<>"") then

' #2 on vérifie si le numéro de package est différent de celui en cours

If (ccur(intCurrentLocationNumber) <> ccur(strTrimmedLocationName)) then

' Si on a un intervalle déjà fait, on le complète avant

if (strBoxNumberPrefix="...") then

' On la ferme

' On inscrit dans notre intervalle le dernier chiffre qu'on a trouvé, cad le intCurrentBoxNumber

ReturnBoxString = ReturnBoxString & strBoxNumberPrefix & cstr(intCurrentLocationNumber) & "-" & cstr(intCurrentBoxNumber)

End if ' de if (strBoxNumberPrefix="...") then

' On commence un nouvel intervalle

ReturnBoxString = ReturnBoxString & strLocationPrefix & strTrimmedLocationName & "-" & cstr(rsSQL("BoxNumber"))

' On change le préfixe pour les prochaines localisations

strLocationPrefix = ", "

Else ' de If (ccur(intCurrentLocationNumber)=ccur(strTrimmedLocationName))

' Oui : on vérifie si la boîte est en suite avec la précéddente

if (ccur(intCurrentBoxNumber) + 1 = ccur(rsSQL("BoxNumber"))) then

' C'est un consécutif, donc le préfixe pour la boîte change

strBoxNumberPrefix="..."

Else ' de if (ccur(intCurrentBoxNumber)=ccur(rsSQL("BoxNumber")) + 1)

' Si notre préfixe est un "...", ca veut dire qu'on a un intervalle à écrire avant de

' faire notre autre bloc

if (cstr(strBoxNumberPrefix)="...") then

' On inscrit dans notre intervalle le dernier chiffre qu'on a trouvé, cad le intCurrentBoxNumber

ReturnBoxString = ReturnBoxString & strBoxNumberPrefix & strTrimmedLocationName & "-" & cstr(intCurrentBoxNumber)

End if ' de if (cstr(strBoxNumberPrefix)="...") then

' On change le préfixe de boîte

strBoxNumberPrefix=""

' On change le préfixe de localisation

strLocationPrefix=", "

' Par la suite, on crée une nouvelle entrée

ReturnBoxString = ReturnBoxString & strLocationPrefix & strTrimmedLocationName & "-" & cstr(rsSQL("BoxNumber"))

End if ' de if (ccur(intCurrentBoxNumber)=ccur(rsSQL("BoxNumber")) + 1)

End if ' de If (ccur(intCurrentLocationNumber)=ccur(strTrimmedLocationName))

' On indique les nouveaux courants

intCurrentLocationNumber=ccur(strTrimmedLocationName)

intCurrentBoxNumber=ccur(rsSQL("BoxNumber"))

' ON passe au prochain enregistrement

rsSQL.moveNext

Wend ' de While (not rsSQL.eof)

' Si on est à la fin de notre recordset et que l'on a toujours une ligne d'ouverte pour l'intervalle

if (rsSQL.eof) AND (strBoxNumberPrefix="...") then

' On la ferme

' On inscrit dans notre intervalle le dernier chiffre qu'on a trouvé, cad le intCurrentBoxNumber

ReturnBoxString = ReturnBoxString & strBoxNumberPrefix & cstr(intCurrentLocationNumber) & "-" & cstr(intCurrentBoxNumber)

End if ' de if (rsSQL.eof) AND (strBoxNumberPrefix="...") then

rsSQL.close

set rsSQL = nothing

End Function ' de Function ReturnBoxString(intIdCustomerOrder, intIdCustomerOrderLine)

Robert Davis One Orange Chip Points: 28027 More actions · Answer 4

Is there any part of that process that can not be done entirely within a single stored procedure? I believe that it could be.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

John Rowan SSC Guru Points: 56440 More actions · Answer 5

Claude,

There are a couple of things going on here. First, you want to know if you can prevent your SELECT from blocking your UPDATE. The answer is yes, but I don't think you'll like the results. If you use the NOLOCK hint, your SELECT will not attempt to lock the rows it is SELECTing; however, this is the equivalent of using the READ UNCOMMITTED transaction isolation level and I do not recommend doing that in your scenario since you are relying on the results from the SELECT in order to do your UPDATE.

On another note, just as Robert has stated, you can (and should) do everything you're trying to do here in a stored procedure. This is where this type of logic belongs. Take a look at what your code is doing: First you open up a recordset with your SELECT logic. You then loop through each row in your recordset and attempt to execute an UPDATE. Basically what you've done here is to write your own VB Cursor!! Row by row processing (RBAR - Modenism) is very inefficient. Your performance would be much better (and your locking problems fixed) if you moved all of your logic into a stored procedure.

I'm not 100% clear on what you are trying to accomplish with your function, but that too can be moved into SQL Server either in the stored procedure as a derived table or into a SQL Server UDF.

Search this site for RBAR and read BOL on stored procedures. If you dare go the route of using the NOLOCK hint, also read BOL on transaction isolation levels and table hints. If you need help with a stored procedure, start a new thread and post your draft and a good description of what you are trying to accomplish and you should get some good help.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden