Concatenating fields: record

Showing posts with label record. Show all posts

Thursday, March 22, 2012

Conditions on latest record

I have a table that has records layed out as so:

Table:
fd_Id INT IDENTITY (1, 1)
fd_User VARCHAR(30)
fd_Effective DATETIME

Data could be as follows:
1 | "user1" | 6/20/2001
2 | "user2" | 6/1/2002
3 | "user2" | 6/5/2002
4 | "user2" | 6/5/2002
5 | "user2" | 2/1/2002
6 | "user3" | 9/1/2003
7 | "user3" | 10/2/2002
8 | "user4" | 1/1/2005

What I need to retrieve from that table is the SINGLE LATEST item of
each fd_User.

SELECT
MAX(FD_ID) AS 'FD_ID',
FD_USER,
MAX(FD_EFFECTIVE) AS 'FD_EFFECTIVE'
FROM F_TABLE
GROUP FD_USER|||select min(a.fd_Id) as fd_Id,
a.fd_User,
a.fd_Effective
from mytable a
inner join (select fd_User,max(fd_Effective) as fd_Effective
from mytable
group by fd_User) b on a.fd_User=b.fd_User and
a.fd_Effective=b.fd_Effective
group by a.fd_User,a.fd_Effective|||Verticon:: wrote:
> I have a table that has records layed out as so:
> Table:
> fd_Id INT IDENTITY (1, 1)
> fd_User VARCHAR(30)
> fd_Effective DATETIME
> Data could be as follows:
> 1 | "user1" | 6/20/2001
> 2 | "user2" | 6/1/2002
> 3 | "user2" | 6/5/2002
> 4 | "user2" | 6/5/2002
> 5 | "user2" | 2/1/2002
> 6 | "user3" | 9/1/2003
> 7 | "user3" | 10/2/2002
> 8 | "user4" | 1/1/2005
> What I need to retrieve from that table is the SINGLE LATEST item of
> each fd_User.
> Results:
> 1 | "user1" | 6/20/2001
> 3 | "user2" | 6/5/2002 (or 4 | "user2" | 6/5/2002) since the dates are
> the same but only 1 of them
> 6 | "user3" | 9/1/2003
> 8 | "user4" | 1/1/2005

First add the constraint that you're apparently missing:

ALTER TABLE tbl
ADD CONSTRAINT ak1_tbl
UNIQUE (fd_User, fd_Effective);

Then:

SELECT fd_Id, fd_User, fd_Effective
FROM tbl
WHERE fd_Effective =
(SELECT MAX(fd_Effective)
FROM tbl AS t
WHERE t.fd_User = tbl.fd_User);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--sql sql

Sunday, March 11, 2012

conditional split for insert or update cause dead lock on database level

I am using conditional split Checking to see if a record exists and if so update else insert. But this cause database dead lock any one has suggestion?

Thanks

Don't try and insert and update a table from the same data flow.

-Jamie

|||

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

aproaching Before:

conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords, OLE DB Destination was used to insert newRecords

aproaching Now:

Conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords,

Merge Join is used to left outer join newRecords with output of OLE DB Command ( this leave only newRecords is availible in output, but still wait for db update command finish), then

OLE DB Destination was used to insert output from merge join.

|||

Jun Fan wrote:

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

Why do you need to read data twice? Just push one of the data paths into a raw file and then insert/update/whatever that data from another dataflow.

-Jamie

|||

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

|||

Jun Fan wrote:

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

Have you tried raw files? They're lightning fast.

By the way, merge join does not ensure anything. It slows up one datapath, sure, but that in no way guarantees that you will prevent your locking problem.

-Jamie

|||

If performance is a concern, I'm suprised that using the OLEDB Command is OK, as it tends to be pretty slow. I have had much better success using a Conditional Split to direct new rows (Inserts) to an OLEDB Destination that writes directly to the target table, and directs the update rows to a permanent temp table. Then I use an Execute SQL Task to issue a batch Update statement after the data flow. During performance testing in the environments I work in, this has proven to be the fastest approach.

This is a pattern that many of the regular posters on this forum use very successfully.

conditional split for insert or update cause dead lock on database level

I am using conditional split Checking to see if a record exists and if so update else insert. But this cause database dead lock any one has suggestion?

Thanks

Don't try and insert and update a table from the same data flow.

-Jamie

|||

aproaching Before:

conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords, OLE DB Destination was used to insert newRecords

aproaching Now:

Conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords,

Merge Join is used to left outer join newRecords with output of OLE DB Command ( this leave only newRecords is availible in output, but still wait for db update command finish), then

OLE DB Destination was used to insert output from merge join.

|||

Jun Fan wrote:

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

Why do you need to read data twice? Just push one of the data paths into a raw file and then insert/update/whatever that data from another dataflow.

-Jamie

|||

Thanks again!

Jun Fan

|||

Jun Fan wrote:

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

Have you tried raw files? They're lightning fast.

By the way, merge join does not ensure anything. It slows up one datapath, sure, but that in no way guarantees that you will prevent your locking problem.

-Jamie

|||

This is a pattern that many of the regular posters on this forum use very successfully.

Friday, February 17, 2012

Concurrent Access - New Record, Primary Key problem....

Can someone explain what happens when two users concurrently attempt to
create a new record in a table with an autonumber primary key? For example,
user 1 creates a new record and manipulates it within a transaction making
use (perhaps) of the @.@.IDENTITY value when creating other, related records.
Before this transaction is complete, user 2 creates a new record and does
the same thing. Presumably they will both have the same @.@.IDENTITY? If
this is the case, how is it possible to manage such a situation?

Thanks."Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:csgov0$4e6$1$830fa17d@.news.demon.co.uk...
> Can someone explain what happens when two users concurrently attempt to
> create a new record in a table with an autonumber primary key? For
> example, user 1 creates a new record and manipulates it within a
> transaction making use (perhaps) of the @.@.IDENTITY value when creating
> other, related records. Before this transaction is complete, user 2
> creates a new record and does the same thing. Presumably they will both
> have the same @.@.IDENTITY? If this is the case, how is it possible to
> manage such a situation?
> Thanks.

No, each session will have a different value, so there's no problem with
concurrency - check out SCOPE_IDENTITY(), IDENT_CURRENT() and @.@.IDENTITY in
Books Online.

Note that just defining a column as an identity column is not enough to
guarantee uniqueness - you can still create duplicates manually (see SET
IDENTITY_INSERT in BOL), so if you want to use the column as a PK, make sure
it is declared as a PK when you create the table.

Simon|||Ok that simplifies things somewhat. Yes, the columns in question are both
Identity and Primary Key.

Thanks very much for your reply.

Robin

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41ebea66$1_3@.news.bluewin.ch...
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:csgov0$4e6$1$830fa17d@.news.demon.co.uk...
>>
>> Can someone explain what happens when two users concurrently attempt to
>> create a new record in a table with an autonumber primary key? For
>> example, user 1 creates a new record and manipulates it within a
>> transaction making use (perhaps) of the @.@.IDENTITY value when creating
>> other, related records. Before this transaction is complete, user 2
>> creates a new record and does the same thing. Presumably they will both
>> have the same @.@.IDENTITY? If this is the case, how is it possible to
>> manage such a situation?
>>
>> Thanks.
>>
> No, each session will have a different value, so there's no problem with
> concurrency - check out SCOPE_IDENTITY(), IDENT_CURRENT() and @.@.IDENTITY
> in Books Online.
> Note that just defining a column as an identity column is not enough to
> guarantee uniqueness - you can still create duplicates manually (see SET
> IDENTITY_INSERT in BOL), so if you want to use the column as a PK, make
> sure it is declared as a PK when you create the table.
> Simon

Concurrency question

Suppose process A is updating record #1 in table T.
By default, can other processes read record #1 while the updating is in progress ??
If the answer is Yes, then which value can they see - the old one or the new one ?
Thank you in advance.Check this...

http://www.sql-server-performance.com/at_sql_locking.asp

Concurrency problems

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
> --
>

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
>
>

Tuesday, February 14, 2012

Concurrency problem

User A and User B modify the same record, UserB save first.
As User A save it , I will get the concurrency error.
I know I can use
Try
... dsTable.udpate()
catch err As DbCurrency
messagebox.show("UpdateFailed ")
end try
However, How can I let User A know "User B save the same record already ",
and ask USER A whether overwrite it or not , If User A press "Yes" , his
record should saved correctly.
Does anyone know how to do '
Thanks a lot.Sorry, I should place this post in vb.net
"Agnes" <agnes@.dynamictech.com.hk> glsD:etbwTeMTFHA.1044@.TK2MSFTNGP10.phx.gbl...[
color=darkred]
> User A and User B modify the same record, UserB save first.
> As User A save it , I will get the concurrency error.
> I know I can use
> Try
> ... dsTable.udpate()
> catch err As DbCurrency
> messagebox.show("UpdateFailed ")
> end try
> However, How can I let User A know "User B save the same record already ",
> and ask USER A whether overwrite it or not , If User A press "Yes" , his
> record should saved correctly.
> Does anyone know how to do '
> Thanks a lot.
>[/color]|||Firstly, this probably isn't the forum for this. You check out a VB.NET foru
m
from the looks of your post. Secondly, it appears you are using a
DataSet/DataTable and DataAdapter together to do your updates (something you
should mention when you post to the other forum).

> However, How can I let User A know "User B save the same record already ",
and
> ask USER A whether overwrite it or not , If User A press "Yes" , his recor
d
> should saved correctly.
That all depends on what type of information you are trying to tell UserA. B
y
far the simplest solution is to tell UserA that *someone*, not UserB
specifically, wrote to that row. It technically should not matter who it was
the
wrote to that row before UserA only that someone did. However, if for some
reason that is important, then you need to add some logging capabilities to
the
table or to the database in general. That would either take the form of colu
mns
that are populated with the user that last modified the row or a logging tab
le
that is appended whenever a row in this table is modified. With logging
capabilities in place and this concurrency scenario arises, you'll have to q
uery
for the row again (or the log history) and display the username of the last
user
that modified the row.
HTH
Thomas
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:etbwTeMTFHA.1044@.TK2MSFTNGP10.phx.gbl...
> User A and User B modify the same record, UserB save first.
> As User A save it , I will get the concurrency error.
> I know I can use
> Try
> ... dsTable.udpate()
> catch err As DbCurrency
> messagebox.show("UpdateFailed ")
> end try
> However, How can I let User A know "User B save the same record already ",
and
> ask USER A whether overwrite it or not , If User A press "Yes" , his recor
d
> should saved correctly.
> Does anyone know how to do '
> Thanks a lot.
>|||How do you know that two users have modified the same row? Do you have data
stored in the row or a log that tells you this? If you are just using a
rowversion(timestamp) column then you really cannot tell them who overwrote
it.
You might consider adding a column or two to your table to tell who last
updated the row, then when the timestamp mismatches, you can get the current
row by primary key (assuming the key is not updatable) and then go fetch the
modified row. Show the user what the other user changed, and who modified
it, and they will be awfully happy.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:etbwTeMTFHA.1044@.TK2MSFTNGP10.phx.gbl...
> User A and User B modify the same record, UserB save first.
> As User A save it , I will get the concurrency error.
> I know I can use
> Try
> ... dsTable.udpate()
> catch err As DbCurrency
> messagebox.show("UpdateFailed ")
> end try
> However, How can I let User A know "User B save the same record already ",
> and ask USER A whether overwrite it or not , If User A press "Yes" , his
> record should saved correctly.
> Does anyone know how to do '
> Thanks a lot.
>

Sunday, February 12, 2012

Concatinating two field and insert the result

Hii,
I need to concatinate two field and insert the result into each record. So far I managed to display the concatination but how do I insert it?
use northwind

select city, region,([city]+ +[region]) as uniqe
from customers
where region is not null
The resulting records in Quary
Anchorage AK AnchorageAK
Tsawassen BC TsawassenBC
Vancouver BC VancouverBC
San Francisco CA San FranciscoCA

Try it like this:

UPDATE
Customers
SET
city = city + ' ' + region
WHERE
region IS NOT NULL

Concatenation Isssue (SELECT QUERY)

here is the sample data. I want a query which can fetch me a single record that can concatenate the value in the 3rd column for the same value in 1st column. do let me know if any understanding issue is there.

XZZZZZQPD2X2NF0WIYPHUFQHB5OLU515 2 arrier and DeltaV Controller is
XZZZZZQPD2X2NF0WIYPHUFQHB5OLU515 3 nets and field equipment interface.
XZZZZZQPD2X2NF0WIYPHUFQHB5OLU515 1 This is the quote for RS3 migration

Thanks,
Rahul Jhasomething like SUM() for varchar data type.|||avast, you should be doing this in your application layer, me hearty

:)|||have just gt a code. wanted to share this with others, and looking forward for the comment from the forum.

-- Prepare sample data
DECLARE @.Sample TABLE (ID INT, Code VARCHAR(3))

INSERT @.Sample
SELECT 290780, 'LT' UNION ALL
SELECT 290780, 'AY' UNION ALL
SELECT 290781, 'ILS' UNION ALL
SELECT 290780, 'AY'

SELECT * FROM @.Sample

-- Show the expected output
SELECT DISTINCT s1.ID,
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE FROM @.Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @.Sample AS s1
ORDER BY s1.ID

SELECT DISTINCT s1.ID,
STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @.Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @.Sample AS s1
ORDER BY s1.ID

SELECT DISTINCT s1.ID,
STUFF((SELECT ',' + s2.CODE FROM @.Sample AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS CODES
FROM @.Sample AS s1
ORDER BY s1.ID|||but my database is 2000. not 2005. and in 2000 the above code will not work.|||Why not do it in the presentation layer/|||This here DBA needs to walk the plank.|||Arggggghhhh

shiver me timbers|||Your wood is cold..?
I just don't even want to know :p

...seriously, what doesthat phrase even mean?|||...seriously, what doesthat phrase even mean?haaaarr, ye be a pitiful excuse for a young pirate, me lad

http://en.wikipedia.org/wiki/Shiver_my_timbers|||hahaha its true its Pirate day today :) aaaaaarrrrrrr|||Arggggghhhh

shiver me timbers

Brett, Opie and Anthony fan ?

They had pirate talk today.|||buffett

He mentioned it last night at his concert at MSG|||buffett

He mentioned it last night at his concert at MSG

Rush Fan ? they played Monday night at MSG.|||Rush Fan ? they played Monday night at MSG.

In another lifetime|||Original Quote Posted By MCrowley.....
Why not do it in the presentation layer/

No I can't do this at the presentation layer. This issue has come duriong the data migration phase. It has to be done at the DB side only.........

Thanks,
Rahul Jha|||Why does it have to be during data migration?
Chances are that you are de-normalising your data by concatenation.|||Why does it have to be during data migration?
Chances are that you are de-normalising your data by concatenation.

Actaully m normalising the DB. currently it's in De-normalised state......

Thanks,
Rahul Jha|||Ha!

Anyhow, have you done any googling? http://www.google.co.uk/search?hl=en&q=how+to+concatenate+in+SQL+msdn2&meta=|||Chances are that you are de-normalising your data by concatenation.that's a bit tentative, isn't it george?

for sure it's denormalizing the data

:)|||I can think of one single example where concatenation (ok, it's not really concatenation, but you can't blame a guy for trying!) would not cause denormalisation.

DateField + TimeField

Bleugh.|||You can try this out...

"SELECT THIRD_COLUMN+FIRST_COLUMN FROM TABLE"

...it works if both are varchar type columns.
if you wanna add some special character inbetween ...

THIRD_COLUMN+'-'+FIRST_COLUMN will solve your purpose.|||I can think of one single example where concatenation (ok, it's not really concatenation, but you can't blame a guy for trying!) would not cause denormalisation.

DateField + TimeField

Bleugh.actually, that is "really concatenation" :)

what he wants to do is aggregation (a column operation over several rows)

it's a reasonable request

in MySQL, the GROUP_CONCAT aggregate function performs exactly this operation, with options for the separator and sequence of terms|||Ha!

Anyhow, have you done any googling? http://www.google.co.uk/search?hl=en&q=how+to+concatenate+in+SQL+msdn2&meta=

How is it gonna help Georgy by any chance...........?? Have a look again on the query............. And if the link can help me to qrite the query then kindly guide me thru.........

Thanks,
Rahul Jha|||what he wants to do is aggregation (a column operation over several rows)

ur rgt.......

Thanks,
Rahul Jha|||start here:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html|||ur rgt.......

Thanks,
Rahul Jha

I love those phone commercials

omg wtf lol roflmao|||buffett

He mentioned it last night at his concert at MSG

You went all the way from the Channel Islands to Madison Square Gardens?! Wow, you are a serious Jimmy Buffett fan!! :D|||ur rgt.......OMG, is there a new instance of The Great Bangalore Alphabet Famine of 1978 (www.nevermind-Im-just-kidding) going on?

Horrors.

Fortunately, relief packages of 1000 letters are available on a first-come-first-served basis at the CIIL (http://www.ciil.org/)|||he is debugging his app on a cell phone while driving

using only his thumb

actually, i will cut him a lot of slack because of this

:cool:

Thursday, March 22, 2012

Conditions on latest record

Sunday, March 11, 2012

conditional split for insert or update cause dead lock on database level

conditional split for insert or update cause dead lock on database level

Friday, February 17, 2012

Concurrent Access - New Record, Primary Key problem....

Concurrency question

Concurrency problems

Concurrency problems

Tuesday, February 14, 2012

Concurrency problem

Sunday, February 12, 2012

Concatinating two field and insert the result

Concatenation Isssue (SELECT QUERY)

Concatenating fields

Blog Archive

About Me