lock records for update: how? options?

  • Thread starter Thread starter feng
  • Start date Start date
F

feng

Hi,

I know ADO.Net recommand using disconnected update
(optimistic concurrency) for good reasons, but it's just
not an option for us. 99% of our client would rather
seeing "record locked" kind of message up front when they
load records, than being told "updating failed" after they
spend all the time entering the data.

So here I am spending a lot of time trying to find a good
way to do the "read with lock" type of opration. I know
you can do some thing like that, at the query level, if
you are running some late version of SQL server, but what
if I can't use those DB specific features? Is there a
recommanded way of doing this at ADO.Net level"? What
about ADO.Net's transaction?

If someone can point me to a right direction that would be
highly appreciated!

Thanks

Feng
 
Hi feng,

I would suggest you to reason the client.
Example: a guy opens some data for editing and goes eating lunch. Now, the
whole system might be locked because of this until he eats all the lunch...
 
I would be careful with all these locks, as what tends to happen is that a
whole page gets locked, and then you can't even access other records that
just happen to be located on the same page.
Keeping transactions open just for the locking will also cause these sorts
of problems - and trust me, you do not want to be dealing with them.

One way to "lock" a record, is in a transaction to change some field in the
database to the name of the user that locked it if the field is empty (or
something like this). Using a transaction should mean that only the first
user gets the update in, and the second fails because now the first user
locked the record.

Then you would "unlock" it by clearing out the field.

Of course as someone else mentioned, if someone opens a record and leaves,
well, then the record will be unavailable, and you will run into all sorts
of problems. So I would also recommend not using this approach, but if you
have to, I would avoid using database locking mechanisms to accomplish this.
 
Back
Top