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.
Leila
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.
|||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[vbcol=seagreen]
> 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...
editing[vbcol=seagreen]
> that.
that[vbcol=seagreen]
> the
Whereas[vbcol=seagreen]
can I[vbcol=seagreen]
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[vbcol=seagreen]
> 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...
> that.
> the
Whereas
>
|||<myelton1@.Lincare.com> wrote in message
news:1103302784.609659.76270@.z14g2000cwz.googlegro ups.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
|||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.googlegr oups.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.
>
|||"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...
>
> marked
>
> that
>
> Whereas
>
>
|||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...
> record)
> the
>
> 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
> --
>