Lock Records

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
KellySELECT <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
"scorpion53061" <scorpion_53061@.nospamhereyahoo.com> wrote in message
news:eAEaA7WUFHA.928@.TK2MSFTNGP15.phx.gbl...
> 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
>|||Would you mind giving a little more detail on this on how to go about doing
this? What you are saying sounds promising.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:urwb7AXUFHA.3544@.TK2MSFTNGP12.phx.gbl...
> 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
>
> "scorpion53061" <scorpion_53061@.nospamhereyahoo.com> wrote in message
> news:eAEaA7WUFHA.928@.TK2MSFTNGP15.phx.gbl...
> for
> items
> another
> I
> other
> this.
>|||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.
"scorpion53061" <scorpion_53061@.nospamhereyahoo.com> wrote in message
news:eAEaA7WUFHA.928@.TK2MSFTNGP15.phx.gbl...
>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
>|||Hi
Start with this article
http://www.sql-server-performance.c...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
"scorpion53061" <scorpion_53061@.nospamhereyahoo.com> wrote in message
news:uYlBoFXUFHA.4092@.TK2MSFTNGP12.phx.gbl...
> Would you mind giving a little more detail on this on how to go about
doing
> this? What you are saying sounds promising.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:urwb7AXUFHA.3544@.TK2MSFTNGP12.phx.gbl...
to
input
allow
enter.
that
>|||See if this helps:
Broadcasting Messages to Multiple Clients
http://msdn.microsoft.com/vbasic/us...net06082004.asp
AMB
"scorpion53061" wrote:

> 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 f
or
> 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 item
s
> in the quote.
>
> If someone calls up a quote I need to somehow find a way to prevent anothe
r
> 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 othe
r
> 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 thi
s.
>
> kelly
>
> Kelly
>
>|||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...arch+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 Concurrenc
y
(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 l
ast
retrieved it (As Mark suggested). For this you can use something like timest
amps
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 ou
t"
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 t
his
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 th
at
expire if not renewed after a certain time.
As you can see, this is a very complicated route to take and I would only ta
ke
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 t
o
develop in the long run.
Thomas
"scorpion53061" <scorpion_53061@.nospamhereyahoo.com> wrote in message
news:eAEaA7WUFHA.928@.TK2MSFTNGP15.phx.gbl...
>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 fo
r
>this.
>
> I have two tables one called QUOTEINFO and one called QUOTEITEMS. One cont
ains
> 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 anothe
r
> user from modifying the same quote while the other user has it.
>
> I thought about an additional column such as "LOCKED" in QUOTEINFO that wo
uld
> 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 wo
rry
> 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 othe
r
> 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 thi
s.
>
> kelly
>
> Kelly
>|||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)
ADONET for that to difficult
It are not that terrible many messages