Lock Records

  • Thread starter Thread starter scorpion53061
  • Start date Start date
S

scorpion53061

I apologize for the cross post but I am using ADO.NET methods and such to
access SQL and I am thinking perhaps both sides will have valuable input for
this.



I have two tables one called QUOTEINFO and one called QUOTEITEMS. One
contains the specifics of the quote and the others contain the actual items
in the quote.



If someone calls up a quote I need to somehow find a way to prevent another
user from modifying the same quote while the other user has it.



I thought about an additional column such as "LOCKED" in QUOTEINFO that
would if the other user attempted to modify the record it would not allow
it. Changing the LOCKED value would be easy enough when they first enter. I
worry though the user may not communicate back to the database though that
they are not finished. (program crash and such.)



Also, once the locking and unlocking is set, I would like to find a way to
record the username of the person locking the quote so I can tell the other
user who is locked out who has locked the quote. That after the lock issue
is resolved will probably be pretty easy.



If it helps, I own the "The Guru's Guide To Transact-SQL" by Ken Henderson
and the ADO.NET book by Sceppa if you know of references within them that
may help.



Sorry to dump all of this on you guys. I just don't know how to handle this.



kelly



Kelly
 
SELECT <column lists> FROM Table (UPDLOCK)
This lock hint will not block others from reading the data , it ensures
that data has not changed since you last read it

Also use BEGIN TRAN
COMMIT to wrap transactions
 
Would you mind giving a little more detail on this on how to go about doing
this? What you are saying sounds promising.
 
use a timestamp/rowversion field, retrieve this with the rest of the record,
when you update use the timestamp value in the where clause, if the record
has been changed by someone else the update fails ( you refresh the data and
present it to the user again for them to re-edit ), if the update works
no-one touched the record since the first user picked it up, thus you are
safe to update it. A timestamp/rowversion field will automatically populate
(on insert) and automatically change its value on update.
 
Hi
Start with this article
http://www.sql-server-performance.com/at_sql_locking.asp

Well, try do soem searching on internet about such kind of issues

Also , you can add to the table a column with ROWVERSION (TIMESTAMP)
datatype
Now , when the user calls for the data save athe value of this column on the
client and compare the value with the value that in the database just
before the user wants to update the row. If the value on the client and a
value in the database are different raise the message that the row was
update by someone

I know you are looking for locks and lock by whom it is worth to take a look
on above example
 
Kelly,

What you want to do is pessimistic concurrency.

It is not standard in ADONET because that is the main difference between
connected and disconnected. Have a look in this newsgroup for it (not that
terrible much messages)

http://groups-beta.google.com/group...oncurrency&qt_g=1&searchnow=Search+this+group

When you real find it important, and it is ASPNET with what you are busy
with, than I would look as well (when you find all the methods proposed with
ADODB for that to difficult) for ADODB where it was more or less the
standard behaviour. Although I would avoid it as much as possible.

I hope this helps,

Cor
 
What you are talking about is a pattern called Offline Optimistic Concurrency
(Fowler). The best way is to maintain the offline nature of data retrieval (get
data, disconnect) and instead check whether the data has changed since you last
retrieved it (As Mark suggested). For this you can use something like timestamps
or complicated updates statements where you check previously retrieved value
against the same values in the database.

If you need an indicator to the users that someone has the quote "checked out"
ala a SCM program like SourceSafe. Then this will not work. For that I would
recommend using the Offline Pessimistic Lock (Fowler) pattern. In this
situation, you set a flag on the database when you "open" the quote. When
someone else reads that row, they get the data read only. The problem with this
scenario is that it gets complicated having to deal with "lock and walk away"
scenario. The "lock and walk away" is more difficult in that it requires the
ability of an administrator to come in remove the lock and/or lock leases that
expire if not renewed after a certain time.

As you can see, this is a very complicated route to take and I would only take
if absolutely necessary. If there are ways to code around the possibility of two
people working on the same quote, then do it. It will be easier and faster to
develop in the long run.


Thomas
 
Kelly

Some corrections on what I wrote.
When you real find it important, and it is ASPNET with what you are busy
with, than I would look as well (when you find all the methods proposed
with ADODB for that to difficult)
Click to expand...

ADONET for that to difficult

It are not that terrible many messages
 
Back
Top