May 1, 2007 at 5:05 am
Hi
My VB.Net (with SQL Server backend) application currently allows more than one user to look at a particular record at the same time. This is not a problem unless both those users also try to update that record as well. One user's changes then overwrite the other's.
I've been reading up on locking hints but my database knowledge is a little scant and I'm also rather dense and need things spelling out for me!! So I have a few questions that I hope someone can help with:
If I add an updlock to my update SQL statement, this would allow both users to view the record but would only allow one user's changes through. Is that correct?
For the other user, would SQL Server return an error message that I can use to tell the user that their update has not worked?
Would I have to get my VB.NET application to re-get the record information so that the user who's update failed can see the changes made by the other user and reapply their own changes?
Does the updlock become unlocked once the record is updated or do I need to specifically unlock it somehow?
Thanks!
May 1, 2007 at 5:53 am
This is a basic outline:
Locks in SQL are only held for the duration of a transaction. If you do not define your own transactions, each command is its own transaction. You do not want to have transactions spanning user input.
You should look up 'Concurrency Control' in books online. Most of the time you will want to use Optimistic Concurrency Control. This involves checking all the columns, or the timestamp column if available, are the same at the time of the update. eg:
UPDATE YourTable
SET Col1 = @Col1
,Col2 = @Col2
-- etc
WHERE YourTablePK = @TourTablePK
AND Col1 = @OrgCol1
AND Col2 = @OrgCol2
-- etc
or
UPDATE YourTable
SET Col1 = @Col1
,Col2 = @Col2
-- etc
WHERE YourTablePK = @YourTablePK
AND TimeStampCol = @OrgTimeStampCol
If the @@ROWCOUNT is zero, then changes have occurred and the user will need to be informed that the update failed. This should not happen too often.
Optimistic concurrency normally works at the row level but it does not have to. ie You may not mind one user updating an address and another user updating the email. In this case you adjust the WHERE clause accordingly.
ADO.Net can help you generate the SP calls, or UPDATE commands, to be used with optimistic concurrency. There are plenty of books on ADO.Net.
With regard to your questions:
>>If I add an updlock to my update SQL statement, this would allow both users to view the record but would only allow one user's changes through.
Only one transaction at a time can obtain an UPDLOCK. With optimistic concurreny you do not normally have to use locking hints.
>>Would I have to get my VB.NET application to re-get the record information so that the user who's update failed can see the changes made by the other user and reapply their own changes?
This is part of conflict processing using optimistic concurrency.
>>Does the updlock become unlocked once the record is updated or do I need to specifically unlock it somehow?
All locks are released at the end of a transaction, either by COMMIT or ROLLBACK.
May 2, 2007 at 12:06 am
Well there is a very old and well tested technique for "logical" locking of records. Since you can't have a pending transaction locking everything while a user is let's say vewing a customer you normally test the timestamps.
You keep the timestamp when you read the record and see if it is the same when you try to update the record. It really is a kind of optimistic locking.
ADO does exactly this (although a bit hidden from the developer) when you use an updateable client-side recordset with the property "Update Criteria" set to adCriteriaTimeStamp.
The update fails if the record has been changed since it was last read.
It is a very interesting topic - try reading MSDN on ADO updating.
May 3, 2007 at 5:16 am
I'm nearly there, but I haven't quite managed to get it working. I've got it checking the timestamp and that all works correctly. When there has been an update by someone else, I get the user to re-get the information and re-apply their changes. On the save, the timestamps match and the save goes through but my check to see if it's worked is based on the sql statement returning a row count but it doesn't return anything even when the update has been successful. What am I doing wrong? A snippet of the code appears below:
str =
"If Exists (Select * from ResearchGrant where ResearchGrantID = " & gvar_ResGrantID & ") UPDATE ResearchGrant SET " & strTitle & ", " & strPropID & ", " & strFundCode & ", " & strExtBodyID & ", " & strStartDate & ", " & strEndDate & ", " & strDuration & ", " & strHoD & ", " & strResOff & ", " & strSubmission & ", " & strReturn & ", " & strTotAppFor & ", " & strTotAwd & ", " & strFinCode & ", " & strSuccess & " FROM ResearchGrant WHERE ResearchGrant.ResearchGrantID = " & gvar_ResGrantID & " and RGTimestamp = " & ToHexString(gvar_timestamp)
Dim cmdSP1 As New SqlClient.SqlCommand(str, conn)
cmdSP1.CommandType = CommandType.Text
Try
Dim drSearchDetails As SqlClient.SqlDataReader = Nothing
conn.Open()
drSearchDetails = cmdSP1.ExecuteReader
If drSearchDetails.HasRows = False Then
MessageBox.Show(
"Another user has changed this record since you opened it. Click OK to update the information and re-apply your changes.", "Update Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
populateFields()
conn.Close()
Exit Sub
End If
May 7, 2007 at 8:17 am
First I don't think you really need the "If Exists" portion of the SQL statement, at least not in the context you are using it. Second change the cmdSP1.ExecuteReader to cmdSP1.ExecuteNonQuery which will return an integer value indicating the total number of rows affected by the SQL Statement.
DIM iRc_ as integer = cmdSP1.ExecuteNonQuery
Then check the integer return value and if equal to zero then either the record was previously deleted or changed, if the count = 1 then a record was updated, and if > 1 then you have other problems unless you intended to update multiple rows.
NOTE: This type of activity is often done in a Stored procedure which will control the transaction, check to insure a row was updated and if not then report which of several potential problems were encountered (Data changed by other user, Data previously deleted, Bad data, etc).
HTH,
James.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply