Access / SQL Server locking procedure

[juddymar58]

Hi, I'm currently investigating moving an access backend to SQL Server. I'm new to sql server so please bear with me.
I have my head around how I'm going to do things but would just like peoples opinions on the procedure I'm looking at creating to manage the locking and if it will work.
Firstly how I want to manage the locking:
A user will have a form open which they can view a record. There will be an edit button, when the edit button is clicked I will call the procedure to check if the current record is locked, if it isn't locked it will be locked by the current user.
If it is locked by another user a check will performed to see how long ago it was locked, it it was locked more than 10 minutes ago then the user will be able to unlock it and lock it to themselves.
If it is locked by another user less than 10 minutes ago the user will receive a message stating it is "locked by user xxxx until date time" please try at this time.

If I'm the user and lock a record for more than 10 minutes and someone else edits it after 10 minutes. When I click save it will perform a check to see if the lockedby date and time has changed and display a message to the user that it has changed you will need to refresh the screen and re-enter your changes (won't be a common occurance).

To do this I'm going to do is create a dynamic stored procedure which accepts the following variables:
TABLENAME
IDFIELDNAME
IDFIELDVALUE
USERNAME
DATETIME


From the access application I will use a pass through query to call it. What it will do is select the record based on the variables passed in eg,
EXEC('SELECT ' + @tablename + '.fldLockedBy,'+ @tablename + '.fldLockedTime FROM ' + @tablename + ' WHERE ' + @IDFieldName + '=' + @IDFieldValue)

from this query in the procedure i will be able to see if fldlockedby and fldlockedtime are populated if they arent I will just update the record with the username and datetime passed in.
If locked by another user it a check will be performed if the date time is more than 10 minutes from the current time, if it is I will update fldlockedby and fldlockeddatetime with the current user.
If it is locked by another user by less than 10 minutes the record wont be updated.

The procedure will then return a string with:
"Locked - David - #1/12/2012 10:00:00am#" - when successfully locked
"Unlocked - David - #1/12/2012 10:00:00am#" - when someone else locked it more than 10 minutes ago and we have locked it to ourselves
"Restricted - Fred - #1/12/2012 10:00:00am#" - someone else locked it less than 10 minutes ago and the time that we can unlock it at.

From what is returned I will be able to manage the messages to the user in access.

Am I on the right track here using a dynamic procedure (passing in tables etc) as I would like to use the same procedure for multiple forms. Or am I completely wrong and there is an easier more efficient way to do it.

I will need another procedure for the save which shouldn't be too difficult but will investigate this later (if what I have proposed is viable.)

Thanks
Justin

 

[RTag]

To detect changes in SQLServer you can create a TIMESTAMP column.

http://technet.microsoft.com/en-us/library/ms182776.aspx

I am not sure that locking as you described it is a good idea.

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[BuilderSpec]

Hi

If you suspect that the conflict of locking won't be common...


Don't write any code to perform application level locking , instead where the code performs the update to the record simply trap the error. If the error implies the record is locked elsewhere just put out a message to the user saying try again later.



Hope this helps!

Regards

BuilderSpec

 

[gmmastros]

I implemented a similar locking process in my application. Here's how mine works.

I added LockedBy, LockDateTime and LockGUID as columns to the table. Locked by is the user name loged in to may application. LockDateTime is a DateTime column indicating when the lock was acquired/refreshed. LockGUID is a globally unique identifier passed from the application.

I determined that LockGUID was necessary because some of my customers are very lazy about security and allow everyone in an organization to use the same username and password. So... when my app starts up, it creates a GUID and keeps it in memory for the life of the app. So... even if the app is started twice on a single computer and logged in with the same user, only one of them will be able to lock a record.

I set the lock time out to be 2 minutes. This means, if someone attempts to acquire a lock and the LockDateTime is more than 2 minutes old, the lock CAN be acquired by another user. I then put a timer on my form that fires every 1 minute. This time reaquires the lock for the user that edited the record. This allows for indefinite locks while the application is running, but also allows for a relatively short timeout in case the app crashes or the pc loses power. When the form is closed, I unlock the record (setting the columns to NULL).

One thing to be careful of... you should be checking the lock times at the server because it is possible for each client to have a different (although probably similar) time. It's not uncommon for times on various computers to be off by several minutes. By using the server's date/time, all of these problems can be avoided.

-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