January 3, 2013 at 9:41 am
After some more read maybe this would lock a record (and only one record) until all work is done,
BEGIN TRAN
SELECT * FROM tblName WITH (HOLDLOCK, ROWLOCK)
WHERE ID = someID
/* While the record is locked do work*/
COMMIT TRAN
January 3, 2013 at 9:47 am
john 60195 (1/3/2013)
After some more read maybe this would lock a record (and only one record) until all work is done
No.
Rowlock just says *start* with row locks. Not only ever take row locks. If there's a lot of row locks taken, SQL escalates to table locks.
It is a very, very bad idea to start a transaction and then allow the user to input stuff, watch the screen, go for lunch, take the weekend off, etc. You'll end up with major blocking and probably a tonne of support calls for 'my session's hanging/timing out'. Transactions should always start and commit in a single database call.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 3, 2013 at 1:24 pm
john 60195 (1/3/2013)
After some more read maybe this would lock a record (and only one record) until all work is done,
I just want to second what Gail said above. You cannot force the system to only lock a particular row. You can suggest and optimize to that's what it *wants* to do, but you can't force it. Cross platform transactions also come with their own issues, but I just wanted to present an alternative to rebuilding the wheel from scratch.
You really want to use a self-releasing indicator, like a 'last updater' timestamp, on the record. This way it's self managing in case of user disconnects, network issues, and anything else of the like.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 5, 2013 at 1:25 am
Thanks Gail & Craig for your input,
Would use of the TIMESTAMP data type be of any help? As I understand it, it is used for version-stamping table rows. I have not tried it yet, but an UPDATE could go ahead WHERE ID = specificID AND LOGTIMESTAMP = SpecificTimeStamp. If the record is changed while another user had it in edit mode the Update would generate an error. Will try it and report.
John
January 5, 2013 at 1:48 am
Yup, that's an alternative way of doing things. Your original version we would call pessimistic concurrency (I expect someone else to try to change the same data, hence I'll lock it). The version with a rowversion (don't call it a timestamp, that type name is deprecated) is optimistic concurrency (I don't thing anyone will change the data, but I'll check when I save, just in case)
You can do it with a row version, you can do it with a datetime column last modification date, you can do it by comparing the columns with what they were when you read the row. All work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2013 at 6:12 am
Gail thanks for the succinct analysis.
It is also nice to have someone in the same timezone!!
It appears that the "rowversion" data type would do the trick. It also appears that the "timestamp" data type has been deprecated and msdn advises the modification of existing timestamp to rowversion.
For reference puproses we can use something like
CREATE TABLE tblTest (intID int PRIMARY KEY ,intValue int, RV rowversion)
where rowversion must now be explicitly defined compared to timestamp.
The resulting RV value can now be used to chack for any edits.
John
February 6, 2013 at 1:14 am
I have been trying to use ROWVERSION. I created a simple table as can be seen in attachment Gail1.jpg.
I use the following ADO code in the open event of the form shown in attachment Gail2.jpg
Private Sub Form_Open(Cancel As Integer)
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
'OLE DB (ADO) Native CLient
con.ConnectionString = "Provider=SQLNCLI11;" _
& "DataSource=MSSQLSERVER;" _
& "Server=(local);" _
& "Database=JP2;" _
& "Uid=sa;" _
& "Pwd=saPass;" _
& "DataTypeCompatibility=80;" _
& "MARS Connection=True;"
con.Open
con.CursorLocation = adUseClient
con.CommandTimeout = 0
rst.Open "dbo.tblMyTest", con, adOpenForwardOnly, adLockOptimistic
Set Me.Recordset = rst
con.Close
Set con = Nothing
Set cmd = Nothing
Set rst = Nothing
As you can see the Rowversion field is empty. Any ideas as to how I can view it in my front end?
John
February 6, 2013 at 2:29 am
I'm not a VB programmer. Why would you want to display it anyway? It's a binary value, meaningless to any user.
btw...
& "Uid=sa;" _
& "Pwd=saPass;" _
Please, please, please tell me you're not actually doing that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2013 at 6:24 am
Regarding Username=sa and Password = saPass, I can safely say "i am not doing that":-)
Regarding ROWVERSION, I need this value if I want to check whether the specific row was changed or not. How can I perform the comparison?
John
February 6, 2013 at 6:41 am
In the stored procedure that does your updates. In the VB code. Up to you.
There's no point at all in displaying it though, no user's going to understand the meaning of it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2013 at 6:50 am
Many thanks Gail,
I am not planning to display the RV binary number, but I need to know what it was when I read the record or records. How will the SP that does the update compare the original value to the current value. The original RV value has to be stored somewhere. Also, we may have a case that the record is updated several minutes after it was read so I need to know the original value that corresponds to the record read.
John
February 6, 2013 at 6:59 am
Ok, so read it from the recordset into a variable (or leave it in the recordset) and compare when you're going to do the update.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2013 at 7:11 am
The problem is that no value comes across in the FE. The contents of the RV column is empty as shown in attachments Gail1 an Gail2.
Can you think of a way to make the RV values available in the FE?
John
February 6, 2013 at 8:13 am
Again, don't display it. There's absolutely no value in what it looks like and if all you need to do is compare it, it does not need to be displayed.
Have you checked programatically what that column is in the recordset? Have you checked for errors or conversion problems? Have you done some basic debugging as to how that value is fetched?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2013 at 8:34 am
Gail, there is no need to display it, I just need to know the value. In the recordset the RV value is not Null, nothing prints when you try to print the value and the field is empty on the form. I will look into this a bit more.
What do you think about using, instead of the RV field, a DATETIME2 field with SYSDATETIME() entered in it?
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply