Concurrency problems

Hi all,
Suppose that client A and client B read a record and B begins editing that.
Meanwhile A attempts to delete the record. Our project's rules says that the
record must not be deleted while it is being edited by other user. Whereas
our clients are disconnected, client B cannot lock the record. How can I
solve this problem?
Any help would be greatly appreciated.
LeilaI'm not sure what you mean by 'our clients are disconnected' but you
could throw the Primary Key value of the row being edited into a table.
A "this row is locked" table of sorts. Then any other user reading
that row you require your application to check your Lock table for the
Primary key value, if it is found then you return a message saying "you
can't delete this record, it's being edited by another user." Or
something like that, we've done that for our OLTP system in the past.|||One way to solve this is to never delete a row. Add a column called
'Visible' that will act as a boolean for the UI to display or not display
the row. When a user 'deletes' a row, the row should really just be marked
as not visible. Then you can implement some logic (via a trigger) such that
if a row is updated when it's marked 'not visible', it will be marked
'visible' again.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Leila" <Leilas@.hotpop.com> wrote in message
news:eU2lWsE5EHA.1596@.tk2msftngp13.phx.gbl...
> Hi all,
> Suppose that client A and client B read a record and B begins editing
that.
> Meanwhile A attempts to delete the record. Our project's rules says that
the
> record must not be deleted while it is being edited by other user. Whereas
> our clients are disconnected, client B cannot lock the record. How can I
> solve this problem?
> Any help would be greatly appreciated.
> Leila
>|||Adam Machanic wrote:
> One way to solve this is to never delete a row. Add a column called
> 'Visible' that will act as a boolean for the UI to display or not
display
> the row. When a user 'deletes' a row, the row should really just be
marked
> as not visible. Then you can implement some logic (via a trigger)
such that
> if a row is updated when it's marked 'not visible', it will be marked
> 'visible' again.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:eU2lWsE5EHA.1596@.tk2msftngp13.phx.gbl...
> > Hi all,
> > Suppose that client A and client B read a record and B begins
editing
> that.
> > Meanwhile A attempts to delete the record. Our project's rules says
that
> the
> > record must not be deleted while it is being edited by other user.
Whereas
> > our clients are disconnected, client B cannot lock the record. How
can I
> > solve this problem?
> > Any help would be greatly appreciated.
> > Leila
> >
> >
Adam,
I have one question about this method, and I'm not questioning the
validity of this solution so please don't take my question the wrong
way. My question is about spliting the table by using the Visible bit
and querying on the table later. Wouldn't you have to always use that
bit on your Selects and therefore not having the most effecient "index"
of sorts to use when you are retrieving data? I'm very interested in
hearing your opinion on this one, we've had developers in the past rely
on an "Active" bit for rows in certain tables we use. For instance, an
operation location around the country, making it accesible to the
application via the active bit. I don't particularly like doing this
and have advised not doing it. Thanks for your time.
Mark|||Thanks Adam,
But how the visibility helps me? Should client B(who is editing the record)
mark the record as invisible? What if client B crashes while editing and the
record remains invisible in table?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#t8hQQF5EHA.1976@.TK2MSFTNGP09.phx.gbl...
> One way to solve this is to never delete a row. Add a column called
> 'Visible' that will act as a boolean for the UI to display or not display
> the row. When a user 'deletes' a row, the row should really just be
marked
> as not visible. Then you can implement some logic (via a trigger) such
that
> if a row is updated when it's marked 'not visible', it will be marked
> 'visible' again.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:eU2lWsE5EHA.1596@.tk2msftngp13.phx.gbl...
> > Hi all,
> > Suppose that client A and client B read a record and B begins editing
> that.
> > Meanwhile A attempts to delete the record. Our project's rules says that
> the
> > record must not be deleted while it is being edited by other user.
Whereas
> > our clients are disconnected, client B cannot lock the record. How can I
> > solve this problem?
> > Any help would be greatly appreciated.
> > Leila
> >
> >
>|||Thanks,
Actually I thought about that but I don't know what to do if the client
crashes? Because the PK remains in that table and no longer will be
deleted..
<myelton1@.Lincare.com> wrote in message
news:1103300244.952647.179750@.z14g2000cwz.googlegroups.com...
> I'm not sure what you mean by 'our clients are disconnected' but you
> could throw the Primary Key value of the row being edited into a table.
> A "this row is locked" table of sorts. Then any other user reading
> that row you require your application to check your Lock table for the
> Primary key value, if it is found then you return a message saying "you
> can't delete this record, it's being edited by another user." Or
> something like that, we've done that for our OLTP system in the past.
>|||<myelton1@.Lincare.com> wrote in message
news:1103302784.609659.76270@.z14g2000cwz.googlegroups.com...
> way. My question is about spliting the table by using the Visible bit
> and querying on the table later. Wouldn't you have to always use that
> bit on your Selects and therefore not having the most effecient "index"
> of sorts to use when you are retrieving data? I'm very interested in
> hearing your opinion on this one, we've had developers in the past rely
> on an "Active" bit for rows in certain tables we use. For instance, an
> operation location around the country, making it accesible to the
> application via the active bit. I don't particularly like doing this
> and have advised not doing it. Thanks for your time.
Yes, you would always have to use that column in your selects. Note, it
doesn't necessarily have to be a BIT. A lot of developers prefer CHAR(1)
NOT NULL CHECK (Visible IN 'Y', 'N'). Whether it will cause problems? It
depends on how many deleted columns there are, how selective the rest of the
columns in the queries are, etc. I probably wouldn't even bother adding it
to any indexes (except maybe covering indexes), as SQL Server can seek using
the keys from the queries and then filter the rows where Visible = 'N' quite
easily. Again, though, it depends. As always, test heavily :)
Another option is to store PKs from deleted rows in another table and
then you can query like:
SELECT *
FROM YourTable
WHERE NOT EXISTS
(SELECT *
FROM YourTableDeletedRows T1
WHERE T1.PK = YourTable.PK)
I don't know how that will perform, but it may solve the issue if you're
getting index scans due to the 'boolean' column.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||"Leila" <Leilas@.hotpop.com> wrote in message
news:ukmR7uF5EHA.3120@.TK2MSFTNGP12.phx.gbl...
> Thanks Adam,
> But how the visibility helps me? Should client B(who is editing the
record)
> mark the record as invisible? What if client B crashes while editing and
the
> record remains invisible in table?
ClientA is looking at the record.
ClientB is editing the record.
ClientA hits the "delete" button on the UI. This flips the "Visible"
column on the row.
ClientB submits the edits...
And now the "Visible" column gets flipped back.
You will probably also want to investigate how to periodically delete
all of the rows marked "not visible" so that your table isn't full of too
much unused legacy data.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Leila wrote:
> Thanks Adam,
> But how the visibility helps me? Should client B(who is editing the record)
> mark the record as invisible? What if client B crashes while editing and the
> record remains invisible in table?
The real problem here is that you didn't provide much real detail so
people have to make guesses.
However, you can implement date/time stamps to track when rows are
"checked out" and create an interface to override checked out rows if
there is a crash.
But, you said your clients are "disconnected". If that is indeed the
case, and both clients can have the same data locally, how do you expect
ANY kind of concurrency checking to take place? Your design doesn't
really allow for it. One solution that does come to mind is that you
create a system to manage changes/deletes each time a client reconnects
to the source data.
But, back to your lack of problem description. When you say that A
cannot delete while B is editing, is that ONLY during the exact time
that B is editing? Honestly, why does it matter? If you're going to let
A delete the data anyway, you're not gaining much by adding in this
check. In fact, you should create a system so that if A does in fact
delete a row while B is editing it, and B then saves the data, they are
notified that the data was deleted by another client and then allow be
to with discard their data (and thus totally deleting the data) or
optionally read the data back to the database. Imperfect solution but it
sounds like you have an imperfect design :D
You may want to post some additional information about the rules that
govern the whole deletion/editing process. Like, how often do the client
connect to the database to sync their data sets? What happens when two
clients edit the same data, who takes precedence? How do you handle
duplicate data? etc.
Zach
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:#t8hQQF5EHA.1976@.TK2MSFTNGP09.phx.gbl...
>>One way to solve this is to never delete a row. Add a column called
>>'Visible' that will act as a boolean for the UI to display or not display
>>the row. When a user 'deletes' a row, the row should really just be
> marked
>>as not visible. Then you can implement some logic (via a trigger) such
> that
>>if a row is updated when it's marked 'not visible', it will be marked
>>'visible' again.
>>--
>>Adam Machanic
>>SQL Server MVP
>>http://www.sqljunkies.com/weblog/amachanic
>>--
>>
>>"Leila" <Leilas@.hotpop.com> wrote in message
>>news:eU2lWsE5EHA.1596@.tk2msftngp13.phx.gbl...
>>Hi all,
>>Suppose that client A and client B read a record and B begins editing
>>that.
>>Meanwhile A attempts to delete the record. Our project's rules says that
>>the
>>record must not be deleted while it is being edited by other user.
> Whereas
>>our clients are disconnected, client B cannot lock the record. How can I
>>solve this problem?
>>Any help would be greatly appreciated.
>>Leila
>>
>>
>|||If client B crashes, it cannot submit the changes and make the record
visible again. I mean the record will remain invisible
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O5#E6xF5EHA.1976@.TK2MSFTNGP09.phx.gbl...
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:ukmR7uF5EHA.3120@.TK2MSFTNGP12.phx.gbl...
> > Thanks Adam,
> > But how the visibility helps me? Should client B(who is editing the
> record)
> > mark the record as invisible? What if client B crashes while editing and
> the
> > record remains invisible in table?
>
> ClientA is looking at the record.
> ClientB is editing the record.
> ClientA hits the "delete" button on the UI. This flips the "Visible"
> column on the row.
> ClientB submits the edits...
> And now the "Visible" column gets flipped back.
> You will probably also want to investigate how to periodically delete
> all of the rows marked "not visible" so that your table isn't full of too
> much unused legacy data.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||Thanks Zach,
Actually each record has lots of details and the poor user must take time
and accuracy to edit these particular records. This is why we need to
protect the row while it's being edited. The logics and rules have a lot of
details to be explained here :(
Please just focus on this problem: client A must be prevented from deleting
the record which is being edited!
"Zach Wells" <zwells@.ain_removethis.com> wrote in message
news:OfNLJ3F5EHA.208@.TK2MSFTNGP12.phx.gbl...
> Leila wrote:
> > Thanks Adam,
> > But how the visibility helps me? Should client B(who is editing the
record)
> > mark the record as invisible? What if client B crashes while editing and
the
> > record remains invisible in table?
> The real problem here is that you didn't provide much real detail so
> people have to make guesses.
> However, you can implement date/time stamps to track when rows are
> "checked out" and create an interface to override checked out rows if
> there is a crash.
> But, you said your clients are "disconnected". If that is indeed the
> case, and both clients can have the same data locally, how do you expect
> ANY kind of concurrency checking to take place? Your design doesn't
> really allow for it. One solution that does come to mind is that you
> create a system to manage changes/deletes each time a client reconnects
> to the source data.
> But, back to your lack of problem description. When you say that A
> cannot delete while B is editing, is that ONLY during the exact time
> that B is editing? Honestly, why does it matter? If you're going to let
> A delete the data anyway, you're not gaining much by adding in this
> check. In fact, you should create a system so that if A does in fact
> delete a row while B is editing it, and B then saves the data, they are
> notified that the data was deleted by another client and then allow be
> to with discard their data (and thus totally deleting the data) or
> optionally read the data back to the database. Imperfect solution but it
> sounds like you have an imperfect design :D
> You may want to post some additional information about the rules that
> govern the whole deletion/editing process. Like, how often do the client
> connect to the database to sync their data sets? What happens when two
> clients edit the same data, who takes precedence? How do you handle
> duplicate data? etc.
> Zach
> >
> > "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> > news:#t8hQQF5EHA.1976@.TK2MSFTNGP09.phx.gbl...
> >
> >>One way to solve this is to never delete a row. Add a column called
> >>'Visible' that will act as a boolean for the UI to display or not
display
> >>the row. When a user 'deletes' a row, the row should really just be
> >
> > marked
> >
> >>as not visible. Then you can implement some logic (via a trigger) such
> >
> > that
> >
> >>if a row is updated when it's marked 'not visible', it will be marked
> >>'visible' again.
> >>
> >>--
> >>Adam Machanic
> >>SQL Server MVP
> >>http://www.sqljunkies.com/weblog/amachanic
> >>--
> >>
> >>
> >>"Leila" <Leilas@.hotpop.com> wrote in message
> >>news:eU2lWsE5EHA.1596@.tk2msftngp13.phx.gbl...
> >>
> >>Hi all,
> >>Suppose that client A and client B read a record and B begins editing
> >>
> >>that.
> >>
> >>Meanwhile A attempts to delete the record. Our project's rules says
that
> >>
> >>the
> >>
> >>record must not be deleted while it is being edited by other user.
> >
> > Whereas
> >
> >>our clients are disconnected, client B cannot lock the record. How can
I
> >>solve this problem?
> >>Any help would be greatly appreciated.
> >>Leila
> >>
> >>
> >>
> >>
> >
> >|||"Leila" <Leilas@.hotpop.com> wrote in message
news:Os6KBVG5EHA.1296@.TK2MSFTNGP10.phx.gbl...
> If client B crashes, it cannot submit the changes and make the record
> visible again. I mean the record will remain invisible
>
That's correct, and in that case the DBA will have to get involved and fix
it.
No scheme is perfect, unfortunately.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||"Leila" <Leilas@.hotpop.com> wrote in message
news:utxzFVG5EHA.1296@.TK2MSFTNGP10.phx.gbl...
> Please just focus on this problem: client A must be prevented from
deleting
> the record which is being edited!
Search Google and the archives of this group for "optimistic locking"
(my preference, always) and "pessimistic locking".
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Leila wrote:
> Thanks Zach,
> Actually each record has lots of details and the poor user must take
> time and accuracy to edit these particular records. This is why we
> need to protect the row while it's being edited. The logics and rules
> have a lot of details to be explained here :(
> Please just focus on this problem: client A must be prevented from
> deleting the record which is being edited!
>
Coming in late here, but, you might also consider using a partitioned
view: Two tables: one for ready rows and one for rows being edited. This
will eliminate any query hit when accessing the "ready" table as it is
separate from the editing table. Your application could be designed to
show the edited rows and "unlock" them if someone requests the
application do so. Maybe only by users with certain rights. So if a
client crashes before the edit is complete, the row can be recovered
without much intervention.
--
David Gugick
Imceda Software
www.imceda.com|||Can this be a solution? suppose that:
client A will attempt to delete the row. It finds out that the row has been
marked (client B is editing). It sends a message to B to get a confirmation.
If B confirms that it is editing the row, delete will fail unless it will be
successful.
But I'm not sure that if COM or Notification Services can help for this
communication.
What is your idea?
Thanks!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eBivEgG5EHA.2580@.TK2MSFTNGP10.phx.gbl...
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:Os6KBVG5EHA.1296@.TK2MSFTNGP10.phx.gbl...
> > If client B crashes, it cannot submit the changes and make the record
> > visible again. I mean the record will remain invisible
> >
> That's correct, and in that case the DBA will have to get involved and fix
> it.
> No scheme is perfect, unfortunately.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:efspn6G5EHA.2428@.TK2MSFTNGP14.phx.gbl...
> Can this be a solution? suppose that:
> client A will attempt to delete the row. It finds out that the row has
been
> marked (client B is editing). It sends a message to B to get a
confirmation.
> If B confirms that it is editing the row, delete will fail unless it will
be
> successful.
> But I'm not sure that if COM or Notification Services can help for this
> communication.
> What is your idea?
> Thanks!
>
Again, I don't think the database should have any idea what the UI is
doing (so it shouldn't know that ClientB is editing). In my opinion, loose
coupling between databases and applications is very important. Giving the
database knowledge of what the UI is doing very tightly couples them.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Interesting idea!
But if these communication are done only between application, does it still
mean that DB is aware of UI?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#eZ8q9G5EHA.924@.TK2MSFTNGP14.phx.gbl...
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:efspn6G5EHA.2428@.TK2MSFTNGP14.phx.gbl...
> > Can this be a solution? suppose that:
> > client A will attempt to delete the row. It finds out that the row has
> been
> > marked (client B is editing). It sends a message to B to get a
> confirmation.
> > If B confirms that it is editing the row, delete will fail unless it
will
> be
> > successful.
> > But I'm not sure that if COM or Notification Services can help for this
> > communication.
> > What is your idea?
> > Thanks!
> >
> Again, I don't think the database should have any idea what the UI is
> doing (so it shouldn't know that ClientB is editing). In my opinion,
loose
> coupling between databases and applications is very important. Giving the
> database knowledge of what the UI is doing very tightly couples them.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||On Fri, 17 Dec 2004 18:00:23 +0330, "Leila" <Leilas@.hotpop.com> wrote:
>Suppose that client A and client B read a record and B begins editing that.
>Meanwhile A attempts to delete the record. Our project's rules says that the
>record must not be deleted while it is being edited by other user. Whereas
>our clients are disconnected, client B cannot lock the record. How can I
>solve this problem?
What you are talking about is pessimistic locking. This is currently
not fashionable, instead, people prefer to do optimistic locking,
which assumes that the collisions will occur so rarely, that they are
hardly worth worrying about - they are still detected and handled, but
basically by letting the *second* client have his way with the record,
and giving a "sorry" message to the first client.
BOL suggests you do pessimistic locking by putting a field into a
record and using it to indicate when the record is locked. This is
really crude, but it does work even in the stateless-client
(disconnected) environments everyone has these days. If you have rich
(thick, smart, whatever) clients only who keep connections open,
SQLServer *does* support pessimistic locking, though it's a bit tricky
and indifferently documented.
BEGIN TRANSACTION
SELECT pk FROM mytable with (updlock)
...
will keep a record locked from deletion as long as the client keeps
his connection alive - thirty seconds, thirty hours, whatever. But
you really don't want a lot of this going on, for one thing it will
block table scans at a read-commited (default) isolation level.
Or, instead of lock fields in a record, you can implement a separate
lock table that lists locked tablename and PK. This is probably
better, but still a lot of work.
Note that you get additional options if you run in Yukon (or Oracle).
Good luck!
J.|||Thanks David,
How will be the recovery process? Can it be an automatic task?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OxZKf1G5EHA.2664@.TK2MSFTNGP10.phx.gbl...
> Leila wrote:
> > Thanks Zach,
> > Actually each record has lots of details and the poor user must take
> > time and accuracy to edit these particular records. This is why we
> > need to protect the row while it's being edited. The logics and rules
> > have a lot of details to be explained here :(
> > Please just focus on this problem: client A must be prevented from
> > deleting the record which is being edited!
> >
> >
> Coming in late here, but, you might also consider using a partitioned
> view: Two tables: one for ready rows and one for rows being edited. This
> will eliminate any query hit when accessing the "ready" table as it is
> separate from the editing table. Your application could be designed to
> show the edited rows and "unlock" them if someone requests the
> application do so. Maybe only by users with certain rights. So if a
> client crashes before the edit is complete, the row can be recovered
> without much intervention.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Thanks J!
I cannot use lock hints because the connection is closed after reading the
row.
Storing the PK in separate table is good idea but if the client which has
done this, crashes, then it cannot submit the changes, therefore the row
remains locked actually.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:khd6s09cqnuegm95o1adbsg6449l9efdvd@.4ax.com...
> On Fri, 17 Dec 2004 18:00:23 +0330, "Leila" <Leilas@.hotpop.com> wrote:
> >Suppose that client A and client B read a record and B begins editing
that.
> >Meanwhile A attempts to delete the record. Our project's rules says that
the
> >record must not be deleted while it is being edited by other user.
Whereas
> >our clients are disconnected, client B cannot lock the record. How can I
> >solve this problem?
> What you are talking about is pessimistic locking. This is currently
> not fashionable, instead, people prefer to do optimistic locking,
> which assumes that the collisions will occur so rarely, that they are
> hardly worth worrying about - they are still detected and handled, but
> basically by letting the *second* client have his way with the record,
> and giving a "sorry" message to the first client.
> BOL suggests you do pessimistic locking by putting a field into a
> record and using it to indicate when the record is locked. This is
> really crude, but it does work even in the stateless-client
> (disconnected) environments everyone has these days. If you have rich
> (thick, smart, whatever) clients only who keep connections open,
> SQLServer *does* support pessimistic locking, though it's a bit tricky
> and indifferently documented.
> BEGIN TRANSACTION
> SELECT pk FROM mytable with (updlock)
> ...
> will keep a record locked from deletion as long as the client keeps
> his connection alive - thirty seconds, thirty hours, whatever. But
> you really don't want a lot of this going on, for one thing it will
> block table scans at a read-commited (default) isolation level.
> Or, instead of lock fields in a record, you can implement a separate
> lock table that lists locked tablename and PK. This is probably
> better, but still a lot of work.
> Note that you get additional options if you run in Yukon (or Oracle).
> Good luck!
> J.
>|||Thanks J!
I cannot use lock hints because the connection is closed after reading the
row.
Storing the PK in separate table is good idea but if the client which has
done this, crashes, then it cannot submit the changes, therefore the row
remains locked actually.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:khd6s09cqnuegm95o1adbsg6449l9efdvd@.4ax.com...
> On Fri, 17 Dec 2004 18:00:23 +0330, "Leila" <Leilas@.hotpop.com> wrote:
> >Suppose that client A and client B read a record and B begins editing
that.
> >Meanwhile A attempts to delete the record. Our project's rules says that
the
> >record must not be deleted while it is being edited by other user.
Whereas
> >our clients are disconnected, client B cannot lock the record. How can I
> >solve this problem?
> What you are talking about is pessimistic locking. This is currently
> not fashionable, instead, people prefer to do optimistic locking,
> which assumes that the collisions will occur so rarely, that they are
> hardly worth worrying about - they are still detected and handled, but
> basically by letting the *second* client have his way with the record,
> and giving a "sorry" message to the first client.
> BOL suggests you do pessimistic locking by putting a field into a
> record and using it to indicate when the record is locked. This is
> really crude, but it does work even in the stateless-client
> (disconnected) environments everyone has these days. If you have rich
> (thick, smart, whatever) clients only who keep connections open,
> SQLServer *does* support pessimistic locking, though it's a bit tricky
> and indifferently documented.
> BEGIN TRANSACTION
> SELECT pk FROM mytable with (updlock)
> ...
> will keep a record locked from deletion as long as the client keeps
> his connection alive - thirty seconds, thirty hours, whatever. But
> you really don't want a lot of this going on, for one thing it will
> block table scans at a read-commited (default) isolation level.
> Or, instead of lock fields in a record, you can implement a separate
> lock table that lists locked tablename and PK. This is probably
> better, but still a lot of work.
> Note that you get additional options if you run in Yukon (or Oracle).
> Good luck!
> J.
>|||Leila,
I'm coming in late, too, but I notice that you have said some things won't
work (or need more work) because they don't handle this situation:
Client X is off-line editing row R. [Business requirements say R may
not be deleted at this point.]
Client X crashes (or perhaps keeps R open for years without crashing?).
This is definitely a situation you need to address. Do the business
requirements address it at all?
(If the business requirements are incomplete, or include "clients never
crash", and "clients never take forever to finish editing", you could
suggest some rules yourself, or you can wait until the first complaint
about a locked row following a crash, at which point someone
might realize there need to be rules about this.)
Can you find out what is supposed to happen in this situation?
Steve Kass
Drew University
Leila wrote:
>Thanks J!
>I cannot use lock hints because the connection is closed after reading the
>row.
>Storing the PK in separate table is good idea but if the client which has
>done this, crashes, then it cannot submit the changes, therefore the row
>remains locked actually.
>"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
>news:khd6s09cqnuegm95o1adbsg6449l9efdvd@.4ax.com...
>
>>On Fri, 17 Dec 2004 18:00:23 +0330, "Leila" <Leilas@.hotpop.com> wrote:
>>
>>Suppose that client A and client B read a record and B begins editing
>>
>that.
>
>>Meanwhile A attempts to delete the record. Our project's rules says that
>>
>the
>
>>record must not be deleted while it is being edited by other user.
>>
>Whereas
>
>>our clients are disconnected, client B cannot lock the record. How can I
>>solve this problem?
>>
>>What you are talking about is pessimistic locking. This is currently
>>not fashionable, instead, people prefer to do optimistic locking,
>>which assumes that the collisions will occur so rarely, that they are
>>hardly worth worrying about - they are still detected and handled, but
>>basically by letting the *second* client have his way with the record,
>>and giving a "sorry" message to the first client.
>>BOL suggests you do pessimistic locking by putting a field into a
>>record and using it to indicate when the record is locked. This is
>>really crude, but it does work even in the stateless-client
>>(disconnected) environments everyone has these days. If you have rich
>>(thick, smart, whatever) clients only who keep connections open,
>>SQLServer *does* support pessimistic locking, though it's a bit tricky
>>and indifferently documented.
>>BEGIN TRANSACTION
>>SELECT pk FROM mytable with (updlock)
>>...
>>will keep a record locked from deletion as long as the client keeps
>>his connection alive - thirty seconds, thirty hours, whatever. But
>>you really don't want a lot of this going on, for one thing it will
>>block table scans at a read-commited (default) isolation level.
>>Or, instead of lock fields in a record, you can implement a separate
>>lock table that lists locked tablename and PK. This is probably
>>better, but still a lot of work.
>>Note that you get additional options if you run in Yukon (or Oracle).
>>Good luck!
>>J.
>>
>
>|||On Fri, 17 Dec 2004 23:31:27 +0330, "Leila" <Leilas@.hotpop.com> wrote:
>Thanks J!
>I cannot use lock hints because the connection is closed after reading the
>row.
>Storing the PK in separate table is good idea but if the client which has
>done this, crashes, then it cannot submit the changes, therefore the row
>remains locked actually.
Right, that's the natural problem with that approach. You simply
write a master unlocker applet for when it happens, and let only the
application supervisor have access to it. Crufty, but workable.
People built systems that way all the time, in the 1970s!
J.|||"Leila" <Leilas@.hotpop.com> wrote in message
news:uRli$uF5EHA.3120@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> Actually I thought about that but I don't know what to do if the client
> crashes? Because the PK remains in that table and no longer will be
> deleted..
Typically in cases like this you use a "deadman's switch"
Store the time the row is copied to the new table.
Then every X hours or minutes run a scheduled task that checks this table.
Any rows older than Y time are removed, with the assumption that the client
crashed, etc.
s
>
> <myelton1@.Lincare.com> wrote in message
> news:1103300244.952647.179750@.z14g2000cwz.googlegroups.com...
> > I'm not sure what you mean by 'our clients are disconnected' but you
> > could throw the Primary Key value of the row being edited into a table.
> > A "this row is locked" table of sorts. Then any other user reading
> > that row you require your application to check your Lock table for the
> > Primary key value, if it is found then you return a message saying "you
> > can't delete this record, it's being edited by another user." Or
> > something like that, we've done that for our OLTP system in the past.
> >
>|||Thanks Steve,
You mentioned that:
<Client X is off-line editing row R. [Business requirements say R may
not be deleted at this point.]>
I think I haven't realized your meaning,
How the row may not be deleted? Who prevents it?
"Steve Kass" <skass@.drew.edu> wrote in message
news:#z2eA0L5EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Leila,
> I'm coming in late, too, but I notice that you have said some things
won't
> work (or need more work) because they don't handle this situation:
> Client X is off-line editing row R. [Business requirements say R may
> not be deleted at this point.]
> Client X crashes (or perhaps keeps R open for years without crashing?).
> This is definitely a situation you need to address. Do the business
> requirements address it at all?
> (If the business requirements are incomplete, or include "clients never
> crash", and "clients never take forever to finish editing", you could
> suggest some rules yourself, or you can wait until the first complaint
> about a locked row following a crash, at which point someone
> might realize there need to be rules about this.)
> Can you find out what is supposed to happen in this situation?
> Steve Kass
> Drew University
>
> Leila wrote:
> >Thanks J!
> >I cannot use lock hints because the connection is closed after reading
the
> >row.
> >Storing the PK in separate table is good idea but if the client which has
> >done this, crashes, then it cannot submit the changes, therefore the row
> >remains locked actually.
> >
> >"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> >news:khd6s09cqnuegm95o1adbsg6449l9efdvd@.4ax.com...
> >
> >
> >>On Fri, 17 Dec 2004 18:00:23 +0330, "Leila" <Leilas@.hotpop.com> wrote:
> >>
> >>
> >>Suppose that client A and client B read a record and B begins editing
> >>
> >>
> >that.
> >
> >
> >>Meanwhile A attempts to delete the record. Our project's rules says
that
> >>
> >>
> >the
> >
> >
> >>record must not be deleted while it is being edited by other user.
> >>
> >>
> >Whereas
> >
> >
> >>our clients are disconnected, client B cannot lock the record. How can
I
> >>solve this problem?
> >>
> >>
> >>What you are talking about is pessimistic locking. This is currently
> >>not fashionable, instead, people prefer to do optimistic locking,
> >>which assumes that the collisions will occur so rarely, that they are
> >>hardly worth worrying about - they are still detected and handled, but
> >>basically by letting the *second* client have his way with the record,
> >>and giving a "sorry" message to the first client.
> >>
> >>BOL suggests you do pessimistic locking by putting a field into a
> >>record and using it to indicate when the record is locked. This is
> >>really crude, but it does work even in the stateless-client
> >>(disconnected) environments everyone has these days. If you have rich
> >>(thick, smart, whatever) clients only who keep connections open,
> >>SQLServer *does* support pessimistic locking, though it's a bit tricky
> >>and indifferently documented.
> >>
> >>BEGIN TRANSACTION
> >>SELECT pk FROM mytable with (updlock)
> >>...
> >>
> >>will keep a record locked from deletion as long as the client keeps
> >>his connection alive - thirty seconds, thirty hours, whatever. But
> >>you really don't want a lot of this going on, for one thing it will
> >>block table scans at a read-commited (default) isolation level.
> >>
> >>Or, instead of lock fields in a record, you can implement a separate
> >>lock table that lists locked tablename and PK. This is probably
> >>better, but still a lot of work.
> >>
> >>Note that you get additional options if you run in Yukon (or Oracle).
> >>
> >>Good luck!
> >>
> >>J.
> >>
> >>
> >>
> >
> >
> >
> >|||Leila wrote:
>Thanks Steve,
>You mentioned that:
><Client X is off-line editing row R. [Business requirements say R may
>not be deleted at this point.]>
>I think I haven't realized your meaning,
>How the row may not be deleted? Who prevents it?
>
Apparently I misunderstood. I thought preventing the delete was
exactly what you were trying to accomplish. In order to know how
to accomplish it, I suggested more information was needed.
SK
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:#z2eA0L5EHA.2568@.TK2MSFTNGP10.phx.gbl...
>
>>Leila,
>> I'm coming in late, too, but I notice that you have said some things
>>
>won't
>
>>work (or need more work) because they don't handle this situation:
>> Client X is off-line editing row R. [Business requirements say R may
>>not be deleted at this point.]
>> Client X crashes (or perhaps keeps R open for years without crashing?).
>>This is definitely a situation you need to address. Do the business
>>requirements address it at all?
>>(If the business requirements are incomplete, or include "clients never
>>crash", and "clients never take forever to finish editing", you could
>>suggest some rules yourself, or you can wait until the first complaint
>>about a locked row following a crash, at which point someone
>>might realize there need to be rules about this.)
>>Can you find out what is supposed to happen in this situation?
>>Steve Kass
>>Drew University
>>
>>Leila wrote:
>>
>>Thanks J!
>>I cannot use lock hints because the connection is closed after reading
>>
>the
>
>>row.
>>Storing the PK in separate table is good idea but if the client which has
>>done this, crashes, then it cannot submit the changes, therefore the row
>>remains locked actually.
>>"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
>>news:khd6s09cqnuegm95o1adbsg6449l9efdvd@.4ax.com...
>>
>>
>>On Fri, 17 Dec 2004 18:00:23 +0330, "Leila" <Leilas@.hotpop.com> wrote:
>>
>>
>>Suppose that client A and client B read a record and B begins editing
>>
>>
>>that.
>>
>>
>>Meanwhile A attempts to delete the record. Our project's rules says
>>
>that
>
>>
>>the
>>
>>
>>record must not be deleted while it is being edited by other user.
>>
>>
>>Whereas
>>
>>
>>our clients are disconnected, client B cannot lock the record. How can
>>
>I
>
>>solve this problem?
>>
>>
>>What you are talking about is pessimistic locking. This is currently
>>not fashionable, instead, people prefer to do optimistic locking,
>>which assumes that the collisions will occur so rarely, that they are
>>hardly worth worrying about - they are still detected and handled, but
>>basically by letting the *second* client have his way with the record,
>>and giving a "sorry" message to the first client.
>>BOL suggests you do pessimistic locking by putting a field into a
>>record and using it to indicate when the record is locked. This is
>>really crude, but it does work even in the stateless-client
>>(disconnected) environments everyone has these days. If you have rich
>>(thick, smart, whatever) clients only who keep connections open,
>>SQLServer *does* support pessimistic locking, though it's a bit tricky
>>and indifferently documented.
>>BEGIN TRANSACTION
>>SELECT pk FROM mytable with (updlock)
>>...
>>will keep a record locked from deletion as long as the client keeps
>>his connection alive - thirty seconds, thirty hours, whatever. But
>>you really don't want a lot of this going on, for one thing it will
>>block table scans at a read-commited (default) isolation level.
>>Or, instead of lock fields in a record, you can implement a separate
>>lock table that lists locked tablename and PK. This is probably
>>better, but still a lot of work.
>>Note that you get additional options if you run in Yukon (or Oracle).
>>Good luck!
>>J.
>>
>>
>>
>>
>
>|||You have a dual problem, i.e. there is no differences with being disconnected
and crashing, therefore you won't be able to deal efficiently with both
situations. You will need to mark your record as being edited prior to
disconnecting, then you need to have a process that cleans up those records
that were marked in edit mode and had a long time lapse (clients crashing).
Therefore you will also need to record the date/time the record was placed in
edit mode in order to perform this clean up. As far as implementation
schemes, you can think of a few ways to do that. The only lingering problem
is that you may have records that can potentially stay uneditable for a
relatively long period of time. I can suggest an AuditDate as an additional
column in your table that will be updated as soon as the client starts the
edit process, your update process will look at that date and if it is not the
same then you know someone else is editing it.
I hope this will help.
"Leila" wrote:
> Hi all,
> Suppose that client A and client B read a record and B begins editing that.
> Meanwhile A attempts to delete the record. Our project's rules says that the
> record must not be deleted while it is being edited by other user. Whereas
> our clients are disconnected, client B cannot lock the record. How can I
> solve this problem?
> Any help would be greatly appreciated.
> Leila
>
>