Dealing with multi-users on SQL

[MartinS]

Before I start trawling through various forums, thought I'd ask here first.
I have been working on a Visual Studio ASP .NET Web Application that relies on data held on our SQL2005 servers. Now, the problem has come to light that there may be times that different users attempt to access the same data record and ultimately, one user's data can over-ride anothers. Does anyone know if it's possible (read "easy") to limit access to one user at a time, and also deal with time-outs/system crashes? My initial idea of a marker on the record is fine as long as the system does not time out or crash, and we really don't want to be going into the data and resetting records to be open.
Anyone out there able to help?
Thanks in advance
Martin

[RonProwse]

this is best done on the SQL server itsself, ask your dba for some advise about this (simlar to a travel agents flight booking system), there are loads of models out there on MSDN that cover this, personally i would try to put the logic in the DB layer not the client. You are still going to need to write the rules for who wins, but if you place the updates inside a transaction you can catch the failure and let the user know that it did not work, at this point you could check to see if the row had changed, to do this add a versionID column to the destination table and write a trigger to increase it when it updated.

Ron

[Conor]

The question is a bit vague at the moment as it depends on whether they should be accessing the same exact data etc.

I think what you are talking about is row level locking , i.e. getting a 'locked for update flag' on the record which would allow you to manage a 'this record is already being edited' message to subsequent users.

I could do it in Oracle so I assume it would work in your MS world as well

[MartinS]

Ron
OK, thanks for the advice. I shalll chat with the dba and go from there.
Thanks again
Martin

[Garf]

yes, its perfectly possible to lock specific rows in a table. Obviously whatever application you are putting on the front end will need to be able to inform the users that ther row is locked for editing rather than giving them the standard SQL message, or worse delivering an error. SQL HOLDLOCK does this anyway, the conflictions are probably happening in the front end rather than the back end. In terms of crashes etc, if you rollback individual update transactions then SQL shoudl take care of itself.

You're also going to have to bear dependants in mind. It's no good locking one row if a process in your application can update several tables at once

Wait till you have to do field level locking. That's a riot.

[MartinS]

Thanks guys.
To explain in a bit more detail - the first few pages only work with one table each, but lucky for me, mine deals with seven! So the confusion is ensuring that all rows for the current record are locked once a user enters the system, and yes, what I'd like is a 'record already locked' type message. My main concern as already stated, is the issue with what happens if (read "when") the system crashes or times out (our users are renonwned for leaving things open and walking away for a n hour or two and expecting to be able to carry on from where they left off!) - the marked records would still be recorded as locked and it would need one of us to deal with it, or I guess I could write some thing for 'admin' users on the app side that listed any locked records and allowed one or all to be set as unlocked.....
Conor - yes, they could well be accessing the same data, as it's all information based on companies/pension schemes and once you've opened a scheme record all the data for that scheme is active.
I will talk to dba, but just wanted to make sure I'd explored all options before going back with a solution.
Thanks again
Martin

[Garf]

even if you have seven tables to lock, there must be an identifier on each of the tables that will allow you to write something to link them all together to be locked.

If you lock the row as part of the transaction, it will rollback the lock if the application crashes. This is just an idea, as our solution would be to stop the bloody thing from crashing.

[Conor]

Originally Posted by Garf

even if you have seven tables to lock, there must be an identifier on each of the tables that will allow you to write something to link them all together to be locked.

If you lock the row as part of the transaction, it will rollback the lock if the application crashes. This is just an idea, as our solution would be to stop the bloody thing from crashing.

What he said

[MartinS]

Originally Posted by Garf

even if you have seven tables to lock, there must be an identifier on each of the tables that will allow you to write something to link them all together to be locked.

Yes, there is an ID on each table. So, is that lock done by using HOLDLOCK?

Originally Posted by Garf

If you lock the row as part of the transaction, it will rollback the lock if the application crashes.

There is just the issue with people walking away from the PC and it timing out before they come back, which causes the app to redirect to the entry page, but if you say that it will get unlocked, then all well and good.

Originally Posted by Garf

This is just an idea, as our solution would be to stop the bloody thing from crashing.

Yes, agreed
Thanks

[waterside53]

Originally Posted by MartinS

So, is that lock done by using HOLDLOCK?

No, you don't need to use HOLDLOCK. Normally all you need to so is issue a BEGIN TRANSACTION before you start the set of changes and COMMIT TRANSACTION at the end and the server will perform the necessary locking automatically.

HOLDLOCK is a "hint" that you can use as part a SQL statement to override the current default locking behaviour. If you want to control the fine detail how the locking works, you should first look up "SET TRANSACTION ISOLATION LEVEL" in books online.

Tony