record locking in n-tier applications
-
We're planning to develop an n-tier application at the moment using .NET framework and sql server technologies.
How should we manage locking in such a disconnected environment? If one user has selected a record for editing, all other users should be prevented from editing this record at this time. We don't want to tell the user "your record has been changed by anyone else" when he has completed his work. So there must be another solution. As an n-tier application has no durable connection to the database, the database locking techniques will not be useful.
I think everyone who develops n-tier applications has this problem. What are possible solutions?
-
Use a semaphore or flag on each record to note that it's locked, or better yet, the FK of the user ID that actually has it locked. So retrieve a record and mark it as "locked". When someone else goes to retrieve it, they'll know who is locking that record.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
The method that Steve just described is the so-called Flag method. It works when the table involved is not large or the amount of users (concurrent) is not big. When the scale goes up then you will have to pretty much use the Locks table mechanism.
With the Locks table you just keep one table with TableName and PK of that row and create a Unique constraint on both columns. With that in mind every time a user wants to access a record it will try to inserted in this table first and if it is successful then you procceed otherwise inform client that is in use. at the end the client app will remove the record from this table.
The reasoning behind this strategy is that the locks table remains small comparetively to the rest of the tables so speed and concurrency issues are minimized.
HTH
* Noel -
Two questions regarding these techniques:
1) Performance: are systems of these kind fast engough? Do you have such systems running in production environment?
2) Human factor: is there any way to automate the lock table strategy? IM shure some programmer will forget to set some locks if it isn't an automated feature. Problem ist that you have multiple records in mutiple tables to lock at some actions.
-
Markus,
1. By FAR when the transactional tables are large (+10 millions) and the number of users is large the Lockings are reduced DRAMATICALLY and it wont contend with people trying to read on those. Besides the exclusive locks will only occur on a small table (fast).Yes I have implemeted that as a Consultant a couple of times successfuly.
2. U are at a point in which the enviroment will determine the feasibility of this. It is very easy if your middle tier DAL is GENERATED (through templates, tools, scripts or whatever other mechanism) but if all is hand coded you are pretty much depending on coding standars an enforment of those (you will still have the same problem with the Flag method though 
The other apparent thing that I didn't mentioned is that this will avoid modification of all tables, you have a general mechanism which is portable(RDBMS independent), flexible (Could be applied to some tables not all) and has been proven on intense OLTP scenarios.
* Noel -
OK. I think the lock table method is a good one. I considered it before this post but I wanted to heare some opinions on it before I make my descission.
Which tools do you use for generating the middle tier? Of cause I will have the same problem with the other approach. The only way to avoid this ist using other strategies such as optimistic locking ("The record you edited has been changed by another user ...").
So: which are the best practices for developing such applications?
-
The First questions are:
Are you expecting High Volume?
Are you expecting a lot of users concurrently?
Do you have appropriate hardware/budget?
etc...
About the tools
Some People use ApexSQL, some TierDeveloper, some Codesmith, other simply use sql scripts based on the schema with template files, etc. I didn't mentioned in any particular order you will have to do your homework and decide which may be more appropriate for your scenario/developers/standards
For best Practices look in the MS website for Patterns and Practices you will be able to find a wealth of recomendations there.
Remember, If the system is not expected to grow it may be ok to use the Flag method. At low levels of concurrency use the easier to implement.
HTH
* Noel -
We've used the "Locks" table method to great success over the past few years, albeit in relatively small (~10 GB) databases with user populations in the dozens. Our application code for it sits in a middle tier auto-generated by tools developed in-house.
I've seen some large systems (older versions of Great Plains Dynamics, for instance) use the Flag method, however.
I like the idea of the Locks table for maintenance and administration, and it leaves your data model cleaner & smaller by not requiring a lock flag on each table.
If performance is a concern, you can always try pinning the lock table in memory.
-
ok,
all this disscussion was really great, but what is the final outcome, if some 1 asks me how will u solve this problem
in an interview what should i answer him ??
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
Hi i am trying to do do he same thing but please some give me example that if i query on table then how can i lock the table or record from other user?
Thanks
-
To Use This method the access to the table has to be allowed only through stored procedures, then on the sp you will check for the Lock.
Cheers,
* Noel
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply