Record level locking on a table

[mdav2]

I have a visual foxpro system that I am migrating to a sql server database.
The system uses pessimistic record which I would like to replicate with the connections to SQL server.
I connect to the database using ODBC authenticating via active directory.
The steps of processing that usually occur as as follows:

1) User will select the details from the database
2) The user will click an "edit" button which will lock just that record in the specific table
3) User will then make changes to the record
4) The user either clicks a "save" to commit the transaction or "cancel" to rollback the changes

I have read a number of different posts on record locking of which the lock hints seems to be the one that would fit the best.
They seem to behave differently depending upon the types of indes you have on the table.
To test this I wrote a simple statement in SQL management studio below:

SELECT *
FROM [saw_test].[dbo].[client] WITH (ROWLOCK NOWAIT)
WHERE client_id = 1

Index is CLUSTERED called pk_client_id on the field client_id

I then opened an spearate instance of SQL management studio and used the same select statement.
It allowed me to select the record back.
What I would have expected is the second occurance would have generated a record lock error because the first statement had locked the record.
Can someone let me know what I am doing wrong or a good article I can read on this.

Mark Davies
Warwickshire County Council

 

[yelworcm]

A select query only takes out a select lock on a resource (index or table row/page/object). Multiple select locks can be held on a resource. Exclusive locks in SQL Server are the main blockers for select locks. This article is a little out of date, but the basics have not changed since SQL 7.0, really.

http://msdn.microsoft.com/en-us/library/aa213039(v=sql.80).aspx

 

[djj55]

Have you looked at "SET TRANSACTION ISOLATION LEVEL READ COMMITTED"?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[TamarGranor]

I don't think a pessimistic locking strategy like you would use in VFP is appropriate for SQL Server. You need to adjust your thinking to the idea of disconnected data. That is, you retrieve some data to work with, do what you need, and then send your changes back. At that point, if the records have changed in the interim, you deal with it.

But in a SQL Server setting, leaving records locked while you're editing them is, I suspect, considered bad form.

Tamar

 

[Auguy]

My two cents here. I use a "Reserve Record" procedure in SQL that saves the date/time, the ID of the user that reserved the record, and the Edit Count of the record. The Edit Count is an integer field in the record and is incremented every time a user updates a record. The user then has 2-3 minutes (actual time is set in the .Net program and varies by table) to make their changes and save them. After this time period expires any other user can then reserve the record. If the user clicks the save button before the time limit is up, the record is updated and the reserve record is canceled. If the time limit has expired then the program checks the Edit Count and updates the record ONLY if the Edit Count has not changed. If the Edit Count has changed it means another user has made updates since the original user's reservation. I set this up because most of the user updates in this particular system will take the user a minute or more to make all of the changes and I didn't want them to make all of their changes and try and save them only to find out somebody else has made other changes to the same record. I realize this method isn't the best for all situations, but it seems to work for me.

Auguy
Sylvania/Toledo Ohio

 

[gmmastros]

I do something similar to Auguy.

In my system, I have a LockDateTime, LockAppGuid, and LockUserName in each table where I want to implement locking. When a user opens a Widget, the system checks to see if the Record is locked. A record is considered locked if the LockTime is less than 2 minutes ago. I than have a timer on the form that fires every 1 minute. This timer will re-lock the record (using LockAppGuid and LockUserName). This re-lock action updates the LockTime value. Since locks are valid for 2 minutes, and the app re-locks the widget every 1 minute, this allows users to keep a widget open for an extended period of time.

When a user tries to open a widget, and that record is already locked, we can use the LockUserName to display a message to the user that it is currently locked by user X.

I added LockAppGuid for this interesting reason...

My app uses SQL Authentication, and often times multiple users are using the same login. When my application starts, I get an AppGuid that exists for the life of the app. Now if multiple users log in with the same credentials, the system knows and will not let them stomp on each other. The interesting unintended consequence of this is that I can open multiple instances of my app on the same machine to test the locking system.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mdav2]

Thanks for the ideas I'll do some more reading and take on board what you guys have suggested. I don't have the time to redesign\re-code the system at the moment so I was trying to just do an a like for like change and we will then review it to fit with our new ICT strategy. I'll update this post with how I get on when I have found a solution that works best for me.

Mark Davies
Warwickshire County Council

 

[Auguy]

George, thanks for your idea. I think I will be adding the LockAppGuid to my application.
One quick question, do you think it would be better to have one table with all of the locking data in it?
It could have fields such as: LockTableName (or Alias), Lock DateTime, LockUserName, LockAppGuid.
You've probably gone down that road already, just wondering what your thoughts are?

Auguy
Sylvania/Toledo Ohio

 

[gmmastros]

Auguy,

If you had a lock table, you would also need to know which item (row) was locked, so you would probably need to add another column for the item identifier, which (if you're lucky) would be an int for each table. Otherwise you'd be taking a trip down the proverbial rabbit hole.

Specifically, in my app I am keeping track of Students, Schools, Addresses, Buses, Drivers, Routes, etc... Each of these items can be locked. Each of these items has an integer primary key, so I could easily store the lock information in one table for these items. However, when the user modifies the address assignments for a student, the primary key is a combination of StudentId and AddressId (because students can be picked up and dropped off at different locations). Having a single table wouldn't work for me.

Your mileage may vary.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom