Hello
Thanks to all who have been helping me to fight a battel
with tsql for rownumbering of my tables. I humbly admit
defeat for today. I had to throw the towel in and whipped
up an ADO loop to number some rows in a table. That took
me all of 3 minutes, if that. I wrestled with tsql all
day long. Well, here is one more shot at tsql.
create table t (rownum int, area char(1), yearnum int,
monthnum int, areaNum int, productNum int)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 1, 1, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 1, 1, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 1, 1, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 1, 2, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 1, 2, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 1, 2, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 1, 3, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 1, 3, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 1, 3, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 2, 1, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 2, 1, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('A', 2003, 2, 1, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 2, 2, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 2, 2, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('B', 2003, 2, 2, 3)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 2, 3, 1)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 2, 3, 2)
insert into t(area, yearnum, monthnum, areanum,
productnum) values('C', 2003, 2, 3, 3)
this yields
rownum area yearnum monum areanum prodnum
NULL A 2003 1 1 1
NULL A 2003 1 1 2
NULL A 2003 1 1 3
NULL B 2003 1 2 1
NULL B 2003 1 2 2
NULL B 2003 1 2 3
NULL C 2003 1 3 1
NULL C 2003 1 3 2
NULL C 2003 1 3 3
NULL A 2003 2 1 1
NULL A 2003 2 1 2
NULL A 2003 2 1 3
NULL B 2003 2 2 1
NULL B 2003 2 2 2
NULL B 2003 2 2 3
NULL C 2003 2 3 1
NULL C 2003 2 3 2
NULL C 2003 2 3 3
I need to number these rows in the order of
yearNum, MonthNum, areaNum, productNum
select * from t order by yearnum, monthnum, areanum,
productnum
how can I number the rownum column starting at 1 using
tsql?
Thanks in advance,
RonDo:
UPDATE t
SET rownum = ( SELECT COUNT(*)
FROM t t1
WHERE CAST( t1.yearnum AS VARCHAR) +
RIGHT( '0' + CAST( t1.monthnum AS VARCHAR), 2 ) +
CAST( t1.areaNum AS VARCHAR) +
CAST( t1.productNum AS VARCHAR) <=
CAST( t.yearnum AS VARCHAR) +
RIGHT( '0' + CAST( t.monthnum AS VARCHAR), 2 ) +
CAST( t.areaNum AS VARCHAR) +
CAST( t.productNum AS VARCHAR) )
WHERE rownum IS NULL ;
Btw, do you really need a reason to have such a column in the table.
Generally, it is a good idea to do such derivations while extracting the
data from the table or to use a view with the ranking expression.
Anith|||thanks for your reply. This worked perfectly.
And to answer your question if I really need this
column? Truthfully, no. I don't really need it. Well,
for my purposes, this column facilitates reporting and ad-
hoc queries for data analysts in my dept at the
workplace. My real problem was that I did not know how
to achieve this with t-sql. I was aware of using the
self join, but I did not know that you had to cast int
values as varchars to accomplish this numerating.
I believe that my exercise for today will be very
valuable in the future.
Thanks for your help,
Ron
>--Original Message--
>Do:
>UPDATE t
> SET rownum = ( SELECT COUNT(*)
> FROM t t1
> WHERE CAST( t1.yearnum AS VARCHAR) +
> RIGHT( '0' + CAST( t1.monthnum
AS VARCHAR), 2 ) +
> CAST( t1.areaNum AS VARCHAR) +
> CAST( t1.productNum AS VARCHAR)
<=
> CAST( t.yearnum AS VARCHAR) +
> RIGHT( '0' + CAST( t.monthnum
AS VARCHAR), 2 ) +
> CAST( t.areaNum AS VARCHAR) +
> CAST( t.productNum AS VARCHAR) )
> WHERE rownum IS NULL ;
>Btw, do you really need a reason to have such a column
in the table.
>Generally, it is a good idea to do such derivations
while extracting the
>data from the table or to use a view with the ranking
expression.
>--
>Anith
>
>.
>|||One more question if I may. I hope this isn't too
ignorant sounding, but...
I noticed a semi colon at the end of your code. Does
Enterprise Manager have a wizard that can generate the t-
sql for something like this? Man, I sure hope so. It
would sure save me a lot of heartache.
>AS VARCHAR), 2 ) +
VARCHAR)
><=
>AS VARCHAR), 2 ) +
VARCHAR) )
>in the table.
>while extracting the
>expression.
>.
>|||>> Does Enterprise Manager have a wizard that can generate the t-sql for
No, they are optional. You need not worry about it.
Btw, for the problem you posted, instead of concatenating the columns you
could do a series of OR-ed correlations like:
WHERE t1.yearnum <= t.yearnum
OR ( t1.yearnum = t.yearnum
AND t1.t1.monthnum <= t.t1.monthnum )
OR ( t1.yearnum = t.yearnum
AND t1.monthnum = t.t1.monthnum
AND t1.areaNum <= t.areaNum )
OR ( t1.yearnum = t.yearnum
AND t1.monthnum = t.t1.monthnum
AND t1.areaNum = t.areaNum
AND t1.productNum <= t.productNum )
Anith|||Thanks again for this explanation. I guess I will just
have to keep experimenting. This other method did work.
>--Original Message--
generate the t-sql for
>No, they are optional. You need not worry about it.
>Btw, for the problem you posted, instead of
concatenating the columns you
>could do a series of OR-ed correlations like:
>WHERE t1.yearnum <= t.yearnum
> OR ( t1.yearnum = t.yearnum
> AND t1.t1.monthnum <= t.t1.monthnum )
> OR ( t1.yearnum = t.yearnum
> AND t1.monthnum = t.t1.monthnum
> AND t1.areaNum <= t.areaNum )
> OR ( t1.yearnum = t.yearnum
> AND t1.monthnum = t.t1.monthnum
> AND t1.areaNum = t.areaNum
> AND t1.productNum <= t.productNum )
>--
>Anith
>
>.
>
Showing posts with label ado. Show all posts
Showing posts with label ado. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Row-level locking & concurrent access
I have a VB6 rich client application that accesses SQL Server 2000
using ADO. The database has a table of customers. The application has
a form that shows customer details, and allows the user to navigate the
to next/previous customer in the table.
When user A is the first to open customer 1's details, I want them to
"take ownership" of that record, such that only user A can make changes
to the record. When user B navigates to customer 1, I want user B to
be able to view the customer's details, but not be able to change them.
However, if user B navigates to customer 2, whose record is on the same
data page as customer 1, I do want user B to be able to take ownership
of customer 2's record, and be able to make changes.
In short, I want row-level locking on the customers table. I was
relying on the client updating a customer record to indicate they have
ownership, but ADO caches changes for (I believe) 5 seconds, so if two
users view the same customer within five secs of each other, the second
user cannot ascertain that the first user has taken ownership.
How do I implement row-level locking? Or is there another way?Davy
UPDATE statement creates an exclusive lock on the row so if the another user
wants to change/update the same row he/she will be locked, however what if
you have ( you probably have ) more details for the customer , so in that
case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored procedure
which makes updating
Also take a look at transaction isolation levels:
a.. READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>I have a VB6 rich client application that accesses SQL Server 2000
> using ADO. The database has a table of customers. The application has
> a form that shows customer details, and allows the user to navigate the
> to next/previous customer in the table.
> When user A is the first to open customer 1's details, I want them to
> "take ownership" of that record, such that only user A can make changes
> to the record. When user B navigates to customer 1, I want user B to
> be able to view the customer's details, but not be able to change them.
> However, if user B navigates to customer 2, whose record is on the same
> data page as customer 1, I do want user B to be able to take ownership
> of customer 2's record, and be able to make changes.
> In short, I want row-level locking on the customers table. I was
> relying on the client updating a customer record to indicate they have
> ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> users view the same customer within five secs of each other, the second
> user cannot ascertain that the first user has taken ownership.
> How do I implement row-level locking? Or is there another way?
>|||Thanks, Uri. Most grateful.
I still have a problem, though. I have set up two Query Analyzer
sessions, representing User A and User B. The first connection runs
this:
begin tran
update Customers set Tel='01234 567 890' where ID=1
Without committing the transaction, this SQL does apply an exclusive
lock on the customer with ID 1.
This User B SQL works well too:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select * from Customers where ID=1
User B can read the dirty data.
However, if user B goes to customer with ID 2, this will not work:
update Customers set Name='My Customer Inc.' where ID=2
It seems that the User A transaction is not applying a row-level lock,
but rather a page lock, or even table lock. How can I force the UPDATE
in user A's session to only lock one customer row?
Uri Dimant wrote:
> Davy
> UPDATE statement creates an exclusive lock on the row so if the another user
> wants to change/update the same row he/she will be locked, however what if
> you have ( you probably have ) more details for the customer , so in that
> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored procedure
> which makes updating
> Also take a look at transaction isolation levels:
> a.. READ UNCOMMITTED
> READ COMMITTED
> REPEATABLE READ
> SERIALIZABLE
>
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >I have a VB6 rich client application that accesses SQL Server 2000
> > using ADO. The database has a table of customers. The application has
> > a form that shows customer details, and allows the user to navigate the
> > to next/previous customer in the table.
> >
> > When user A is the first to open customer 1's details, I want them to
> > "take ownership" of that record, such that only user A can make changes
> > to the record. When user B navigates to customer 1, I want user B to
> > be able to view the customer's details, but not be able to change them.
> >
> > However, if user B navigates to customer 2, whose record is on the same
> > data page as customer 1, I do want user B to be able to take ownership
> > of customer 2's record, and be able to make changes.
> >
> > In short, I want row-level locking on the customers table. I was
> > relying on the client updating a customer record to indicate they have
> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> > users view the same customer within five secs of each other, the second
> > user cannot ascertain that the first user has taken ownership.
> >
> > How do I implement row-level locking? Or is there another way?
> >|||Davy
Do you have an index on ID column?
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
> Thanks, Uri. Most grateful.
> I still have a problem, though. I have set up two Query Analyzer
> sessions, representing User A and User B. The first connection runs
> this:
> begin tran
> update Customers set Tel='01234 567 890' where ID=1
> Without committing the transaction, this SQL does apply an exclusive
> lock on the customer with ID 1.
> This User B SQL works well too:
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> select * from Customers where ID=1
> User B can read the dirty data.
> However, if user B goes to customer with ID 2, this will not work:
> update Customers set Name='My Customer Inc.' where ID=2
> It seems that the User A transaction is not applying a row-level lock,
> but rather a page lock, or even table lock. How can I force the UPDATE
> in user A's session to only lock one customer row?
>
>
> Uri Dimant wrote:
>> Davy
>> UPDATE statement creates an exclusive lock on the row so if the another
>> user
>> wants to change/update the same row he/she will be locked, however what
>> if
>> you have ( you probably have ) more details for the customer , so in
>> that
>> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
>> procedure
>> which makes updating
>> Also take a look at transaction isolation levels:
>> a.. READ UNCOMMITTED
>> READ COMMITTED
>> REPEATABLE READ
>> SERIALIZABLE
>>
>> "Davy B" <david.bridge@.itv.com> wrote in message
>> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>> >I have a VB6 rich client application that accesses SQL Server 2000
>> > using ADO. The database has a table of customers. The application has
>> > a form that shows customer details, and allows the user to navigate the
>> > to next/previous customer in the table.
>> >
>> > When user A is the first to open customer 1's details, I want them to
>> > "take ownership" of that record, such that only user A can make changes
>> > to the record. When user B navigates to customer 1, I want user B to
>> > be able to view the customer's details, but not be able to change them.
>> >
>> > However, if user B navigates to customer 2, whose record is on the same
>> > data page as customer 1, I do want user B to be able to take ownership
>> > of customer 2's record, and be able to make changes.
>> >
>> > In short, I want row-level locking on the customers table. I was
>> > relying on the client updating a customer record to indicate they have
>> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
>> > users view the same customer within five secs of each other, the second
>> > user cannot ascertain that the first user has taken ownership.
>> >
>> > How do I implement row-level locking? Or is there another way?
>> >
>|||Yes. It's the primary key. Is there an implication?
Uri Dimant wrote:
> Davy
> Do you have an index on ID column?
>
>
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
> > Thanks, Uri. Most grateful.
> >
> > I still have a problem, though. I have set up two Query Analyzer
> > sessions, representing User A and User B. The first connection runs
> > this:
> >
> > begin tran
> > update Customers set Tel='01234 567 890' where ID=1
> >
> > Without committing the transaction, this SQL does apply an exclusive
> > lock on the customer with ID 1.
> >
> > This User B SQL works well too:
> >
> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> > select * from Customers where ID=1
> >
> > User B can read the dirty data.
> >
> > However, if user B goes to customer with ID 2, this will not work:
> >
> > update Customers set Name='My Customer Inc.' where ID=2
> >
> > It seems that the User A transaction is not applying a row-level lock,
> > but rather a page lock, or even table lock. How can I force the UPDATE
> > in user A's session to only lock one customer row?
> >
> >
> >
> >
> >
> > Uri Dimant wrote:
> >> Davy
> >> UPDATE statement creates an exclusive lock on the row so if the another
> >> user
> >> wants to change/update the same row he/she will be locked, however what
> >> if
> >> you have ( you probably have ) more details for the customer , so in
> >> that
> >> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
> >> procedure
> >> which makes updating
> >>
> >> Also take a look at transaction isolation levels:
> >> a.. READ UNCOMMITTED
> >> READ COMMITTED
> >> REPEATABLE READ
> >> SERIALIZABLE
> >>
> >>
> >>
> >> "Davy B" <david.bridge@.itv.com> wrote in message
> >> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >> >I have a VB6 rich client application that accesses SQL Server 2000
> >> > using ADO. The database has a table of customers. The application has
> >> > a form that shows customer details, and allows the user to navigate the
> >> > to next/previous customer in the table.
> >> >
> >> > When user A is the first to open customer 1's details, I want them to
> >> > "take ownership" of that record, such that only user A can make changes
> >> > to the record. When user B navigates to customer 1, I want user B to
> >> > be able to view the customer's details, but not be able to change them.
> >> >
> >> > However, if user B navigates to customer 2, whose record is on the same
> >> > data page as customer 1, I do want user B to be able to take ownership
> >> > of customer 2's record, and be able to make changes.
> >> >
> >> > In short, I want row-level locking on the customers table. I was
> >> > relying on the client updating a customer record to indicate they have
> >> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> >> > users view the same customer within five secs of each other, the second
> >> > user cannot ascertain that the first user has taken ownership.
> >> >
> >> > How do I implement row-level locking? Or is there another way?
> >> >
> >|||Hi
Row level locking would be occurring at data level, but if a modification of
an index is involved, you might have blocking on the index page.
Look at the output of sp_lock to see what type of lock is causing the
problem.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
> Thanks, Uri. Most grateful.
> I still have a problem, though. I have set up two Query Analyzer
> sessions, representing User A and User B. The first connection runs
> this:
> begin tran
> update Customers set Tel='01234 567 890' where ID=1
> Without committing the transaction, this SQL does apply an exclusive
> lock on the customer with ID 1.
> This User B SQL works well too:
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> select * from Customers where ID=1
> User B can read the dirty data.
> However, if user B goes to customer with ID 2, this will not work:
> update Customers set Name='My Customer Inc.' where ID=2
> It seems that the User A transaction is not applying a row-level lock,
> but rather a page lock, or even table lock. How can I force the UPDATE
> in user A's session to only lock one customer row?
>
>
> Uri Dimant wrote:
>> Davy
>> UPDATE statement creates an exclusive lock on the row so if the another
>> user
>> wants to change/update the same row he/she will be locked, however what
>> if
>> you have ( you probably have ) more details for the customer , so in
>> that
>> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
>> procedure
>> which makes updating
>> Also take a look at transaction isolation levels:
>> a.. READ UNCOMMITTED
>> READ COMMITTED
>> REPEATABLE READ
>> SERIALIZABLE
>>
>> "Davy B" <david.bridge@.itv.com> wrote in message
>> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>> >I have a VB6 rich client application that accesses SQL Server 2000
>> > using ADO. The database has a table of customers. The application has
>> > a form that shows customer details, and allows the user to navigate the
>> > to next/previous customer in the table.
>> >
>> > When user A is the first to open customer 1's details, I want them to
>> > "take ownership" of that record, such that only user A can make changes
>> > to the record. When user B navigates to customer 1, I want user B to
>> > be able to view the customer's details, but not be able to change them.
>> >
>> > However, if user B navigates to customer 2, whose record is on the same
>> > data page as customer 1, I do want user B to be able to take ownership
>> > of customer 2's record, and be able to make changes.
>> >
>> > In short, I want row-level locking on the customers table. I was
>> > relying on the client updating a customer record to indicate they have
>> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
>> > users view the same customer within five secs of each other, the second
>> > user cannot ascertain that the first user has taken ownership.
>> >
>> > How do I implement row-level locking? Or is there another way?
>> >
>|||You've described a 'pessimistic concurrency' technique. Here's an
description from the SQL Server 2005 Books Online:
<Excerpt
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/829c973e-b1d0-422c-b951-43c5968e95eb.htm">
Concurrency control theory has two classifications for the methods of
instituting concurrency control:
Pessimistic concurrency control
A system of locks prevents users from modifying data in a way that affects
other users. After a user performs an action that causes a lock to be
applied, other users cannot perform actions that would conflict with the
lock until the owner releases it. This is called pessimistic control because
it is mainly used in environments where there is high contention for data,
where the cost of protecting data with locks is less than the cost of
rolling back transactions if concurrency conflicts occur.
Optimistic concurrency control
In optimistic concurrency control, users do not lock data when they read it.
When a user updates data, the system checks to see if another user changed
the data after it was read. If another user updated the data, an error is
raised. Typically, the user receiving the error rolls back the transaction
and starts over. This is called optimistic because it is mainly used in
environments where there is low contention for data, and where the cost of
occasionally rolling back a transaction outweighs the costs of locking data
when read.
</Excerpt>
Although you could use SQL Server's row locking to implement pessimistic
concurrency, that would be especially bad for your application because it
would require that you keep the transaction open while waiting for user
action. Consider that the user may go to lunch or take the afternoon off
while viewing customer data. A cardinal rule is that one should never keep
a transaction open while waiting for user action.
I suggest you consider using an optimistic concurrency approach instead.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>I have a VB6 rich client application that accesses SQL Server 2000
> using ADO. The database has a table of customers. The application has
> a form that shows customer details, and allows the user to navigate the
> to next/previous customer in the table.
> When user A is the first to open customer 1's details, I want them to
> "take ownership" of that record, such that only user A can make changes
> to the record. When user B navigates to customer 1, I want user B to
> be able to view the customer's details, but not be able to change them.
> However, if user B navigates to customer 2, whose record is on the same
> data page as customer 1, I do want user B to be able to take ownership
> of customer 2's record, and be able to make changes.
> In short, I want row-level locking on the customers table. I was
> relying on the client updating a customer record to indicate they have
> ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> users view the same customer within five secs of each other, the second
> user cannot ascertain that the first user has taken ownership.
> How do I implement row-level locking? Or is there another way?
>|||Dave
I did just testing and it worked for me just fine, perhaps I missunderstood
you.
--Open connection1 (userA)
--Run
create table t (c1 int primary key,c2 char(1))
insert into t values (1,'a')
insert into t values (2,'b')
begin tran
update t set c2='c' where c1=1
--rollback tran
--Open connection2 (userB)
--Run
select * from t where c1 =2
update t set c2='t' where c1=2
--After I ssued ROLLBACK at connection1 i see
c1 c2
-- --
1 a
2 t
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149594164.589487.34150@.c74g2000cwc.googlegroups.com...
> Yes. It's the primary key. Is there an implication?
>
> Uri Dimant wrote:
>> Davy
>> Do you have an index on ID column?
>>
>>
>> "Davy B" <david.bridge@.itv.com> wrote in message
>> news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
>> > Thanks, Uri. Most grateful.
>> >
>> > I still have a problem, though. I have set up two Query Analyzer
>> > sessions, representing User A and User B. The first connection runs
>> > this:
>> >
>> > begin tran
>> > update Customers set Tel='01234 567 890' where ID=1
>> >
>> > Without committing the transaction, this SQL does apply an exclusive
>> > lock on the customer with ID 1.
>> >
>> > This User B SQL works well too:
>> >
>> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> > select * from Customers where ID=1
>> >
>> > User B can read the dirty data.
>> >
>> > However, if user B goes to customer with ID 2, this will not work:
>> >
>> > update Customers set Name='My Customer Inc.' where ID=2
>> >
>> > It seems that the User A transaction is not applying a row-level lock,
>> > but rather a page lock, or even table lock. How can I force the UPDATE
>> > in user A's session to only lock one customer row?
>> >
>> >
>> >
>> >
>> >
>> > Uri Dimant wrote:
>> >> Davy
>> >> UPDATE statement creates an exclusive lock on the row so if the
>> >> another
>> >> user
>> >> wants to change/update the same row he/she will be locked, however
>> >> what
>> >> if
>> >> you have ( you probably have ) more details for the customer , so in
>> >> that
>> >> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
>> >> procedure
>> >> which makes updating
>> >>
>> >> Also take a look at transaction isolation levels:
>> >> a.. READ UNCOMMITTED
>> >> READ COMMITTED
>> >> REPEATABLE READ
>> >> SERIALIZABLE
>> >>
>> >>
>> >>
>> >> "Davy B" <david.bridge@.itv.com> wrote in message
>> >> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>> >> >I have a VB6 rich client application that accesses SQL Server 2000
>> >> > using ADO. The database has a table of customers. The application
>> >> > has
>> >> > a form that shows customer details, and allows the user to navigate
>> >> > the
>> >> > to next/previous customer in the table.
>> >> >
>> >> > When user A is the first to open customer 1's details, I want them
>> >> > to
>> >> > "take ownership" of that record, such that only user A can make
>> >> > changes
>> >> > to the record. When user B navigates to customer 1, I want user B
>> >> > to
>> >> > be able to view the customer's details, but not be able to change
>> >> > them.
>> >> >
>> >> > However, if user B navigates to customer 2, whose record is on the
>> >> > same
>> >> > data page as customer 1, I do want user B to be able to take
>> >> > ownership
>> >> > of customer 2's record, and be able to make changes.
>> >> >
>> >> > In short, I want row-level locking on the customers table. I was
>> >> > relying on the client updating a customer record to indicate they
>> >> > have
>> >> > ownership, but ADO caches changes for (I believe) 5 seconds, so if
>> >> > two
>> >> > users view the same customer within five secs of each other, the
>> >> > second
>> >> > user cannot ascertain that the first user has taken ownership.
>> >> >
>> >> > How do I implement row-level locking? Or is there another way?
>> >> >
>> >
>|||Uri,
Yeah, that's my problem. I want user B to be able to save a change on
the second record, even though user A has got the first record locked.
I don't want user B to have to wait for user A to commit or roll back
their transaction.
I have used sp_lock, and established that the first update establishes
three locks if type TAB, PAG and KEY. I am starting to guess that the
behaviour I want is not possible - since the table has a primary key,
which is effectively a unique index, allowing two rows to be updated by
two users could compromise the index, so SQL Server prevents it.
If I continue with my current design, which is optimistic, I wonder
whether I could use files to indicate ownership? If all users have
access to a shared folder, the client application could open a WSH
textstream called C1.txt (where 1 = row ID), and leave it open. Anyone
who wants ownership of record 1 could attempt to create C1.txt - this
will fail if another user already has that file open. I'm thinking
laterally now?
Davy
Uri Dimant wrote:
> Dave
> I did just testing and it worked for me just fine, perhaps I missunderstood
> you.
> --Open connection1 (userA)
> --Run
> create table t (c1 int primary key,c2 char(1))
> insert into t values (1,'a')
> insert into t values (2,'b')
> begin tran
> update t set c2='c' where c1=1
> --rollback tran
> --Open connection2 (userB)
> --Run
> select * from t where c1 =2
> update t set c2='t' where c1=2
> --After I ssued ROLLBACK at connection1 i see
> c1 c2
> -- --
> 1 a
> 2 t
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149594164.589487.34150@.c74g2000cwc.googlegroups.com...
> > Yes. It's the primary key. Is there an implication?
> >
> >
> > Uri Dimant wrote:
> >> Davy
> >> Do you have an index on ID column?
> >>
> >>
> >>
> >>
> >>
> >> "Davy B" <david.bridge@.itv.com> wrote in message
> >> news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
> >> > Thanks, Uri. Most grateful.
> >> >
> >> > I still have a problem, though. I have set up two Query Analyzer
> >> > sessions, representing User A and User B. The first connection runs
> >> > this:
> >> >
> >> > begin tran
> >> > update Customers set Tel='01234 567 890' where ID=1
> >> >
> >> > Without committing the transaction, this SQL does apply an exclusive
> >> > lock on the customer with ID 1.
> >> >
> >> > This User B SQL works well too:
> >> >
> >> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> >> > select * from Customers where ID=1
> >> >
> >> > User B can read the dirty data.
> >> >
> >> > However, if user B goes to customer with ID 2, this will not work:
> >> >
> >> > update Customers set Name='My Customer Inc.' where ID=2
> >> >
> >> > It seems that the User A transaction is not applying a row-level lock,
> >> > but rather a page lock, or even table lock. How can I force the UPDATE
> >> > in user A's session to only lock one customer row?
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > Uri Dimant wrote:
> >> >> Davy
> >> >> UPDATE statement creates an exclusive lock on the row so if the
> >> >> another
> >> >> user
> >> >> wants to change/update the same row he/she will be locked, however
> >> >> what
> >> >> if
> >> >> you have ( you probably have ) more details for the customer , so in
> >> >> that
> >> >> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
> >> >> procedure
> >> >> which makes updating
> >> >>
> >> >> Also take a look at transaction isolation levels:
> >> >> a.. READ UNCOMMITTED
> >> >> READ COMMITTED
> >> >> REPEATABLE READ
> >> >> SERIALIZABLE
> >> >>
> >> >>
> >> >>
> >> >> "Davy B" <david.bridge@.itv.com> wrote in message
> >> >> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >> >> >I have a VB6 rich client application that accesses SQL Server 2000
> >> >> > using ADO. The database has a table of customers. The application
> >> >> > has
> >> >> > a form that shows customer details, and allows the user to navigate
> >> >> > the
> >> >> > to next/previous customer in the table.
> >> >> >
> >> >> > When user A is the first to open customer 1's details, I want them
> >> >> > to
> >> >> > "take ownership" of that record, such that only user A can make
> >> >> > changes
> >> >> > to the record. When user B navigates to customer 1, I want user B
> >> >> > to
> >> >> > be able to view the customer's details, but not be able to change
> >> >> > them.
> >> >> >
> >> >> > However, if user B navigates to customer 2, whose record is on the
> >> >> > same
> >> >> > data page as customer 1, I do want user B to be able to take
> >> >> > ownership
> >> >> > of customer 2's record, and be able to make changes.
> >> >> >
> >> >> > In short, I want row-level locking on the customers table. I was
> >> >> > relying on the client updating a customer record to indicate they
> >> >> > have
> >> >> > ownership, but ADO caches changes for (I believe) 5 seconds, so if
> >> >> > two
> >> >> > users view the same customer within five secs of each other, the
> >> >> > second
> >> >> > user cannot ascertain that the first user has taken ownership.
> >> >> >
> >> >> > How do I implement row-level locking? Or is there another way?
> >> >> >
> >> >
> >|||Thanks for getting involved, Mike.
It now seems clear that if SQL Server was to allow two users to lock
and update different rows in the same table, the primary key could get
compromised. So it must be preventing me from achieving a row-level
lock in the interest of maintaining integrity.
My current client design is optimistic, but the users are now having
problems whereby two of them do occasionally open the same record. The
first makes changes which get overwritten by the second. When I change
the design to pessimistic, only the first user who gets an exclusive
lock is able to save changes to any record in that same table. The
others can do a dirty read, but it's effectively view-only. I am now
looking at a design that retains optimism, and uses file locks - if
user A opens record 123, they attempt to create a file called 123.txt
in a shared folder. The file creation will fail if another user has
already opened record 123.
Maybe there's a better way?
Davy
Michael Epprecht [MSFT] wrote:
> Hi
> Row level locking would be occurring at data level, but if a modification of
> an index is involved, you might have blocking on the index page.
> Look at the output of sp_lock to see what type of lock is causing the
> problem.
> Regards
> --
> Mike
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
> > Thanks, Uri. Most grateful.
> >
> > I still have a problem, though. I have set up two Query Analyzer
> > sessions, representing User A and User B. The first connection runs
> > this:
> >
> > begin tran
> > update Customers set Tel='01234 567 890' where ID=1
> >
> > Without committing the transaction, this SQL does apply an exclusive
> > lock on the customer with ID 1.
> >
> > This User B SQL works well too:
> >
> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> > select * from Customers where ID=1
> >
> > User B can read the dirty data.
> >
> > However, if user B goes to customer with ID 2, this will not work:
> >
> > update Customers set Name='My Customer Inc.' where ID=2
> >
> > It seems that the User A transaction is not applying a row-level lock,
> > but rather a page lock, or even table lock. How can I force the UPDATE
> > in user A's session to only lock one customer row?
> >
> >
> >
> >
> >
> > Uri Dimant wrote:
> >> Davy
> >> UPDATE statement creates an exclusive lock on the row so if the another
> >> user
> >> wants to change/update the same row he/she will be locked, however what
> >> if
> >> you have ( you probably have ) more details for the customer , so in
> >> that
> >> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
> >> procedure
> >> which makes updating
> >>
> >> Also take a look at transaction isolation levels:
> >> a.. READ UNCOMMITTED
> >> READ COMMITTED
> >> REPEATABLE READ
> >> SERIALIZABLE
> >>
> >>
> >>
> >> "Davy B" <david.bridge@.itv.com> wrote in message
> >> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >> >I have a VB6 rich client application that accesses SQL Server 2000
> >> > using ADO. The database has a table of customers. The application has
> >> > a form that shows customer details, and allows the user to navigate the
> >> > to next/previous customer in the table.
> >> >
> >> > When user A is the first to open customer 1's details, I want them to
> >> > "take ownership" of that record, such that only user A can make changes
> >> > to the record. When user B navigates to customer 1, I want user B to
> >> > be able to view the customer's details, but not be able to change them.
> >> >
> >> > However, if user B navigates to customer 2, whose record is on the same
> >> > data page as customer 1, I do want user B to be able to take ownership
> >> > of customer 2's record, and be able to make changes.
> >> >
> >> > In short, I want row-level locking on the customers table. I was
> >> > relying on the client updating a customer record to indicate they have
> >> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> >> > users view the same customer within five secs of each other, the second
> >> > user cannot ascertain that the first user has taken ownership.
> >> >
> >> > How do I implement row-level locking? Or is there another way?
> >> >
> >|||Yes, Dan. My current design is optimistic, which is fine 99% of the
time. But occasionally two users do open the same record; one user
overwrites changes made by another. The problem with pessimistic
design is that when one user opts to change a record it precludes all
others from changing one. The whole table gets locked.
I am considering continuing with an optimistic design, but using files
created in a shared area to indicate ownership. If user A opens record
123 for modification, they will create and lock a file called 123.txt.
When user B access that record they attempt to create a 123.txt, but
get "access denied", since user A has the file locked. Lateral
thinking? Or should I be doing this a different way?
Davy
Dan Guzman wrote:
> You've described a 'pessimistic concurrency' technique. Here's an
> description from the SQL Server 2005 Books Online:
> <Excerpt
> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/829c973e-b1d0-422c-b951-43c5968e95eb.htm">
> Concurrency control theory has two classifications for the methods of
> instituting concurrency control:
> Pessimistic concurrency control
> A system of locks prevents users from modifying data in a way that affects
> other users. After a user performs an action that causes a lock to be
> applied, other users cannot perform actions that would conflict with the
> lock until the owner releases it. This is called pessimistic control because
> it is mainly used in environments where there is high contention for data,
> where the cost of protecting data with locks is less than the cost of
> rolling back transactions if concurrency conflicts occur.
> Optimistic concurrency control
> In optimistic concurrency control, users do not lock data when they read it.
> When a user updates data, the system checks to see if another user changed
> the data after it was read. If another user updated the data, an error is
> raised. Typically, the user receiving the error rolls back the transaction
> and starts over. This is called optimistic because it is mainly used in
> environments where there is low contention for data, and where the cost of
> occasionally rolling back a transaction outweighs the costs of locking data
> when read.
> </Excerpt>
> Although you could use SQL Server's row locking to implement pessimistic
> concurrency, that would be especially bad for your application because it
> would require that you keep the transaction open while waiting for user
> action. Consider that the user may go to lunch or take the afternoon off
> while viewing customer data. A cardinal rule is that one should never keep
> a transaction open while waiting for user action.
> I suggest you consider using an optimistic concurrency approach instead.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >I have a VB6 rich client application that accesses SQL Server 2000
> > using ADO. The database has a table of customers. The application has
> > a form that shows customer details, and allows the user to navigate the
> > to next/previous customer in the table.
> >
> > When user A is the first to open customer 1's details, I want them to
> > "take ownership" of that record, such that only user A can make changes
> > to the record. When user B navigates to customer 1, I want user B to
> > be able to view the customer's details, but not be able to change them.
> >
> > However, if user B navigates to customer 2, whose record is on the same
> > data page as customer 1, I do want user B to be able to take ownership
> > of customer 2's record, and be able to make changes.
> >
> > In short, I want row-level locking on the customers table. I was
> > relying on the client updating a customer record to indicate they have
> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> > users view the same customer within five secs of each other, the second
> > user cannot ascertain that the first user has taken ownership.
> >
> > How do I implement row-level locking? Or is there another way?
> >|||Hi
What a lot of developers do it to use row versioning. This version stamp is
read and when updating the row, you first compare what is on the row
compared to what you have read earlier. If they are the same, the update may
proceed, if they differ, then someone else has made a change and the update
fails.
The SQL Server data type timestamp is used for this.
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149604836.357340.208660@.f6g2000cwb.googlegroups.com...
> Yes, Dan. My current design is optimistic, which is fine 99% of the
> time. But occasionally two users do open the same record; one user
> overwrites changes made by another. The problem with pessimistic
> design is that when one user opts to change a record it precludes all
> others from changing one. The whole table gets locked.
> I am considering continuing with an optimistic design, but using files
> created in a shared area to indicate ownership. If user A opens record
> 123 for modification, they will create and lock a file called 123.txt.
> When user B access that record they attempt to create a 123.txt, but
> get "access denied", since user A has the file locked. Lateral
> thinking? Or should I be doing this a different way?
> Davy
>
>
> Dan Guzman wrote:
>> You've described a 'pessimistic concurrency' technique. Here's an
>> description from the SQL Server 2005 Books Online:
>> <Excerpt
>> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/829c973e-b1d0-422c-b951-43c5968e95eb.htm">
>> Concurrency control theory has two classifications for the methods of
>> instituting concurrency control:
>> Pessimistic concurrency control
>> A system of locks prevents users from modifying data in a way that
>> affects
>> other users. After a user performs an action that causes a lock to be
>> applied, other users cannot perform actions that would conflict with the
>> lock until the owner releases it. This is called pessimistic control
>> because
>> it is mainly used in environments where there is high contention for
>> data,
>> where the cost of protecting data with locks is less than the cost of
>> rolling back transactions if concurrency conflicts occur.
>> Optimistic concurrency control
>> In optimistic concurrency control, users do not lock data when they read
>> it.
>> When a user updates data, the system checks to see if another user
>> changed
>> the data after it was read. If another user updated the data, an error is
>> raised. Typically, the user receiving the error rolls back the
>> transaction
>> and starts over. This is called optimistic because it is mainly used in
>> environments where there is low contention for data, and where the cost
>> of
>> occasionally rolling back a transaction outweighs the costs of locking
>> data
>> when read.
>> </Excerpt>
>> Although you could use SQL Server's row locking to implement pessimistic
>> concurrency, that would be especially bad for your application because it
>> would require that you keep the transaction open while waiting for user
>> action. Consider that the user may go to lunch or take the afternoon off
>> while viewing customer data. A cardinal rule is that one should never
>> keep
>> a transaction open while waiting for user action.
>> I suggest you consider using an optimistic concurrency approach instead.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Davy B" <david.bridge@.itv.com> wrote in message
>> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>> >I have a VB6 rich client application that accesses SQL Server 2000
>> > using ADO. The database has a table of customers. The application has
>> > a form that shows customer details, and allows the user to navigate the
>> > to next/previous customer in the table.
>> >
>> > When user A is the first to open customer 1's details, I want them to
>> > "take ownership" of that record, such that only user A can make changes
>> > to the record. When user B navigates to customer 1, I want user B to
>> > be able to view the customer's details, but not be able to change them.
>> >
>> > However, if user B navigates to customer 2, whose record is on the same
>> > data page as customer 1, I do want user B to be able to take ownership
>> > of customer 2's record, and be able to make changes.
>> >
>> > In short, I want row-level locking on the customers table. I was
>> > relying on the client updating a customer record to indicate they have
>> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
>> > users view the same customer within five secs of each other, the second
>> > user cannot ascertain that the first user has taken ownership.
>> >
>> > How do I implement row-level locking? Or is there another way?
>> >
>|||On 6 Jun 2006 07:31:02 -0700, "Davy B" <david.bridge@.itv.com> wrote:
>It now seems clear that if SQL Server was to allow two users to lock
>and update different rows in the same table, the primary key could get
>compromised. So it must be preventing me from achieving a row-level
>lock in the interest of maintaining integrity.
>My current client design is optimistic, but the users are now having
>problems whereby two of them do occasionally open the same record. The
>first makes changes which get overwritten by the second. When I change
>the design to pessimistic, only the first user who gets an exclusive
>lock is able to save changes to any record in that same table. The
>others can do a dirty read, but it's effectively view-only. I am now
>looking at a design that retains optimism, and uses file locks - if
>user A opens record 123, they attempt to create a file called 123.txt
>in a shared folder. The file creation will fail if another user has
>already opened record 123.
>Maybe there's a better way?
Not really.
If you do that file-lock business, second user will still be locked
out! It's the logical situation, not the mechanism, which is the
problem.
Anyway, why use a file, create your own lock table, if you really want
to go that way. Which you probably shouldn't.
My advice is to fiddle with the ADO/SQLServer features for pessimistic
locking a little further. Set the lock_timeout to a small number so
the second guy to write can get a quick message that the record is
locked. Better yet, warn the second guy before he modifies anything,
that the record is already locked! Boy, this optimistic locking is a
lost art, ain't it?
Josh|||> But occasionally two users do open the same record; one user
> overwrites changes made by another.
That's chaos (last in wins) rather than optimistic concurrency. To properly
implement optimistic concurrency you need to check to ensure initially
retrieved data haven't changed and notify the user accordingly.
As Mike suggested, you can simplify coding by checking a rowversion column
(a.k.a timestamp) during the update rather than specifying all columns to be
updated. SQL Server generates a unique value for the rowversion each time
the row is updated so the check will fail of the row has changed since
initially read by the application. Below is an example.
CREATE TABLE Table1
(
Table1_Key int NOT NULL
CONSTRAINT PK_Table1 PRIMARY KEY,
Table1_Data int NOT NULL,
RowVersionColumn RowVersion NOT NULL
)
GO
ALTER PROC dbo.usp_Update_Table1
@.Table1_Key int,
@.Table1_Data int,
@.ExpectedRowVersion rowversion
AS
SET NOCOUNT ON
UPDATE dbo.Table1
SET @.Table1_Data = @.Table1_Data
WHERE
Table1_Key = @.Table1_Key AND
RowVersionColumn = @.ExpectedRowVersion
IF @.@.ROWCOUNT = 0
BEGIN
RAISERROR('Data updated or deleted by another user', 16, 1)
END
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149604836.357340.208660@.f6g2000cwb.googlegroups.com...
> Yes, Dan. My current design is optimistic, which is fine 99% of the
> time. But occasionally two users do open the same record; one user
> overwrites changes made by another. The problem with pessimistic
> design is that when one user opts to change a record it precludes all
> others from changing one. The whole table gets locked.
> I am considering continuing with an optimistic design, but using files
> created in a shared area to indicate ownership. If user A opens record
> 123 for modification, they will create and lock a file called 123.txt.
> When user B access that record they attempt to create a 123.txt, but
> get "access denied", since user A has the file locked. Lateral
> thinking? Or should I be doing this a different way?
> Davy
>
>
> Dan Guzman wrote:
>> You've described a 'pessimistic concurrency' technique. Here's an
>> description from the SQL Server 2005 Books Online:
>> <Excerpt
>> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/829c973e-b1d0-422c-b951-43c5968e95eb.htm">
>> Concurrency control theory has two classifications for the methods of
>> instituting concurrency control:
>> Pessimistic concurrency control
>> A system of locks prevents users from modifying data in a way that
>> affects
>> other users. After a user performs an action that causes a lock to be
>> applied, other users cannot perform actions that would conflict with the
>> lock until the owner releases it. This is called pessimistic control
>> because
>> it is mainly used in environments where there is high contention for
>> data,
>> where the cost of protecting data with locks is less than the cost of
>> rolling back transactions if concurrency conflicts occur.
>> Optimistic concurrency control
>> In optimistic concurrency control, users do not lock data when they read
>> it.
>> When a user updates data, the system checks to see if another user
>> changed
>> the data after it was read. If another user updated the data, an error is
>> raised. Typically, the user receiving the error rolls back the
>> transaction
>> and starts over. This is called optimistic because it is mainly used in
>> environments where there is low contention for data, and where the cost
>> of
>> occasionally rolling back a transaction outweighs the costs of locking
>> data
>> when read.
>> </Excerpt>
>> Although you could use SQL Server's row locking to implement pessimistic
>> concurrency, that would be especially bad for your application because it
>> would require that you keep the transaction open while waiting for user
>> action. Consider that the user may go to lunch or take the afternoon off
>> while viewing customer data. A cardinal rule is that one should never
>> keep
>> a transaction open while waiting for user action.
>> I suggest you consider using an optimistic concurrency approach instead.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Davy B" <david.bridge@.itv.com> wrote in message
>> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>> >I have a VB6 rich client application that accesses SQL Server 2000
>> > using ADO. The database has a table of customers. The application has
>> > a form that shows customer details, and allows the user to navigate the
>> > to next/previous customer in the table.
>> >
>> > When user A is the first to open customer 1's details, I want them to
>> > "take ownership" of that record, such that only user A can make changes
>> > to the record. When user B navigates to customer 1, I want user B to
>> > be able to view the customer's details, but not be able to change them.
>> >
>> > However, if user B navigates to customer 2, whose record is on the same
>> > data page as customer 1, I do want user B to be able to take ownership
>> > of customer 2's record, and be able to make changes.
>> >
>> > In short, I want row-level locking on the customers table. I was
>> > relying on the client updating a customer record to indicate they have
>> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
>> > users view the same customer within five secs of each other, the second
>> > user cannot ascertain that the first user has taken ownership.
>> >
>> > How do I implement row-level locking? Or is there another way?
>> >
>|||It does help, Dan. I am most grateful.
Dan Guzman wrote:
> > But occasionally two users do open the same record; one user
> > overwrites changes made by another.
> That's chaos (last in wins) rather than optimistic concurrency. To properly
> implement optimistic concurrency you need to check to ensure initially
> retrieved data haven't changed and notify the user accordingly.
> As Mike suggested, you can simplify coding by checking a rowversion column
> (a.k.a timestamp) during the update rather than specifying all columns to be
> updated. SQL Server generates a unique value for the rowversion each time
> the row is updated so the check will fail of the row has changed since
> initially read by the application. Below is an example.
> CREATE TABLE Table1
> (
> Table1_Key int NOT NULL
> CONSTRAINT PK_Table1 PRIMARY KEY,
> Table1_Data int NOT NULL,
> RowVersionColumn RowVersion NOT NULL
> )
> GO
> ALTER PROC dbo.usp_Update_Table1
> @.Table1_Key int,
> @.Table1_Data int,
> @.ExpectedRowVersion rowversion
> AS
> SET NOCOUNT ON
> UPDATE dbo.Table1
> SET @.Table1_Data = @.Table1_Data
> WHERE
> Table1_Key = @.Table1_Key AND
> RowVersionColumn = @.ExpectedRowVersion
> IF @.@.ROWCOUNT = 0
> BEGIN
> RAISERROR('Data updated or deleted by another user', 16, 1)
> END
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149604836.357340.208660@.f6g2000cwb.googlegroups.com...
> > Yes, Dan. My current design is optimistic, which is fine 99% of the
> > time. But occasionally two users do open the same record; one user
> > overwrites changes made by another. The problem with pessimistic
> > design is that when one user opts to change a record it precludes all
> > others from changing one. The whole table gets locked.
> >
> > I am considering continuing with an optimistic design, but using files
> > created in a shared area to indicate ownership. If user A opens record
> > 123 for modification, they will create and lock a file called 123.txt.
> > When user B access that record they attempt to create a 123.txt, but
> > get "access denied", since user A has the file locked. Lateral
> > thinking? Or should I be doing this a different way?
> >
> > Davy
> >
> >
> >
> >
> > Dan Guzman wrote:
> >> You've described a 'pessimistic concurrency' technique. Here's an
> >> description from the SQL Server 2005 Books Online:
> >>
> >> <Excerpt
> >> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/829c973e-b1d0-422c-b951-43c5968e95eb.htm">
> >>
> >> Concurrency control theory has two classifications for the methods of
> >> instituting concurrency control:
> >>
> >> Pessimistic concurrency control
> >>
> >> A system of locks prevents users from modifying data in a way that
> >> affects
> >> other users. After a user performs an action that causes a lock to be
> >> applied, other users cannot perform actions that would conflict with the
> >> lock until the owner releases it. This is called pessimistic control
> >> because
> >> it is mainly used in environments where there is high contention for
> >> data,
> >> where the cost of protecting data with locks is less than the cost of
> >> rolling back transactions if concurrency conflicts occur.
> >>
> >> Optimistic concurrency control
> >>
> >> In optimistic concurrency control, users do not lock data when they read
> >> it.
> >> When a user updates data, the system checks to see if another user
> >> changed
> >> the data after it was read. If another user updated the data, an error is
> >> raised. Typically, the user receiving the error rolls back the
> >> transaction
> >> and starts over. This is called optimistic because it is mainly used in
> >> environments where there is low contention for data, and where the cost
> >> of
> >> occasionally rolling back a transaction outweighs the costs of locking
> >> data
> >> when read.
> >>
> >> </Excerpt>
> >>
> >> Although you could use SQL Server's row locking to implement pessimistic
> >> concurrency, that would be especially bad for your application because it
> >> would require that you keep the transaction open while waiting for user
> >> action. Consider that the user may go to lunch or take the afternoon off
> >> while viewing customer data. A cardinal rule is that one should never
> >> keep
> >> a transaction open while waiting for user action.
> >>
> >> I suggest you consider using an optimistic concurrency approach instead.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Davy B" <david.bridge@.itv.com> wrote in message
> >> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >> >I have a VB6 rich client application that accesses SQL Server 2000
> >> > using ADO. The database has a table of customers. The application has
> >> > a form that shows customer details, and allows the user to navigate the
> >> > to next/previous customer in the table.
> >> >
> >> > When user A is the first to open customer 1's details, I want them to
> >> > "take ownership" of that record, such that only user A can make changes
> >> > to the record. When user B navigates to customer 1, I want user B to
> >> > be able to view the customer's details, but not be able to change them.
> >> >
> >> > However, if user B navigates to customer 2, whose record is on the same
> >> > data page as customer 1, I do want user B to be able to take ownership
> >> > of customer 2's record, and be able to make changes.
> >> >
> >> > In short, I want row-level locking on the customers table. I was
> >> > relying on the client updating a customer record to indicate they have
> >> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> >> > users view the same customer within five secs of each other, the second
> >> > user cannot ascertain that the first user has taken ownership.
> >> >
> >> > How do I implement row-level locking? Or is there another way?
> >> >
> >
using ADO. The database has a table of customers. The application has
a form that shows customer details, and allows the user to navigate the
to next/previous customer in the table.
When user A is the first to open customer 1's details, I want them to
"take ownership" of that record, such that only user A can make changes
to the record. When user B navigates to customer 1, I want user B to
be able to view the customer's details, but not be able to change them.
However, if user B navigates to customer 2, whose record is on the same
data page as customer 1, I do want user B to be able to take ownership
of customer 2's record, and be able to make changes.
In short, I want row-level locking on the customers table. I was
relying on the client updating a customer record to indicate they have
ownership, but ADO caches changes for (I believe) 5 seconds, so if two
users view the same customer within five secs of each other, the second
user cannot ascertain that the first user has taken ownership.
How do I implement row-level locking? Or is there another way?Davy
UPDATE statement creates an exclusive lock on the row so if the another user
wants to change/update the same row he/she will be locked, however what if
you have ( you probably have ) more details for the customer , so in that
case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored procedure
which makes updating
Also take a look at transaction isolation levels:
a.. READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>I have a VB6 rich client application that accesses SQL Server 2000
> using ADO. The database has a table of customers. The application has
> a form that shows customer details, and allows the user to navigate the
> to next/previous customer in the table.
> When user A is the first to open customer 1's details, I want them to
> "take ownership" of that record, such that only user A can make changes
> to the record. When user B navigates to customer 1, I want user B to
> be able to view the customer's details, but not be able to change them.
> However, if user B navigates to customer 2, whose record is on the same
> data page as customer 1, I do want user B to be able to take ownership
> of customer 2's record, and be able to make changes.
> In short, I want row-level locking on the customers table. I was
> relying on the client updating a customer record to indicate they have
> ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> users view the same customer within five secs of each other, the second
> user cannot ascertain that the first user has taken ownership.
> How do I implement row-level locking? Or is there another way?
>|||Thanks, Uri. Most grateful.
I still have a problem, though. I have set up two Query Analyzer
sessions, representing User A and User B. The first connection runs
this:
begin tran
update Customers set Tel='01234 567 890' where ID=1
Without committing the transaction, this SQL does apply an exclusive
lock on the customer with ID 1.
This User B SQL works well too:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select * from Customers where ID=1
User B can read the dirty data.
However, if user B goes to customer with ID 2, this will not work:
update Customers set Name='My Customer Inc.' where ID=2
It seems that the User A transaction is not applying a row-level lock,
but rather a page lock, or even table lock. How can I force the UPDATE
in user A's session to only lock one customer row?
Uri Dimant wrote:
> Davy
> UPDATE statement creates an exclusive lock on the row so if the another user
> wants to change/update the same row he/she will be locked, however what if
> you have ( you probably have ) more details for the customer , so in that
> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored procedure
> which makes updating
> Also take a look at transaction isolation levels:
> a.. READ UNCOMMITTED
> READ COMMITTED
> REPEATABLE READ
> SERIALIZABLE
>
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >I have a VB6 rich client application that accesses SQL Server 2000
> > using ADO. The database has a table of customers. The application has
> > a form that shows customer details, and allows the user to navigate the
> > to next/previous customer in the table.
> >
> > When user A is the first to open customer 1's details, I want them to
> > "take ownership" of that record, such that only user A can make changes
> > to the record. When user B navigates to customer 1, I want user B to
> > be able to view the customer's details, but not be able to change them.
> >
> > However, if user B navigates to customer 2, whose record is on the same
> > data page as customer 1, I do want user B to be able to take ownership
> > of customer 2's record, and be able to make changes.
> >
> > In short, I want row-level locking on the customers table. I was
> > relying on the client updating a customer record to indicate they have
> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> > users view the same customer within five secs of each other, the second
> > user cannot ascertain that the first user has taken ownership.
> >
> > How do I implement row-level locking? Or is there another way?
> >|||Davy
Do you have an index on ID column?
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
> Thanks, Uri. Most grateful.
> I still have a problem, though. I have set up two Query Analyzer
> sessions, representing User A and User B. The first connection runs
> this:
> begin tran
> update Customers set Tel='01234 567 890' where ID=1
> Without committing the transaction, this SQL does apply an exclusive
> lock on the customer with ID 1.
> This User B SQL works well too:
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> select * from Customers where ID=1
> User B can read the dirty data.
> However, if user B goes to customer with ID 2, this will not work:
> update Customers set Name='My Customer Inc.' where ID=2
> It seems that the User A transaction is not applying a row-level lock,
> but rather a page lock, or even table lock. How can I force the UPDATE
> in user A's session to only lock one customer row?
>
>
> Uri Dimant wrote:
>> Davy
>> UPDATE statement creates an exclusive lock on the row so if the another
>> user
>> wants to change/update the same row he/she will be locked, however what
>> if
>> you have ( you probably have ) more details for the customer , so in
>> that
>> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
>> procedure
>> which makes updating
>> Also take a look at transaction isolation levels:
>> a.. READ UNCOMMITTED
>> READ COMMITTED
>> REPEATABLE READ
>> SERIALIZABLE
>>
>> "Davy B" <david.bridge@.itv.com> wrote in message
>> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>> >I have a VB6 rich client application that accesses SQL Server 2000
>> > using ADO. The database has a table of customers. The application has
>> > a form that shows customer details, and allows the user to navigate the
>> > to next/previous customer in the table.
>> >
>> > When user A is the first to open customer 1's details, I want them to
>> > "take ownership" of that record, such that only user A can make changes
>> > to the record. When user B navigates to customer 1, I want user B to
>> > be able to view the customer's details, but not be able to change them.
>> >
>> > However, if user B navigates to customer 2, whose record is on the same
>> > data page as customer 1, I do want user B to be able to take ownership
>> > of customer 2's record, and be able to make changes.
>> >
>> > In short, I want row-level locking on the customers table. I was
>> > relying on the client updating a customer record to indicate they have
>> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
>> > users view the same customer within five secs of each other, the second
>> > user cannot ascertain that the first user has taken ownership.
>> >
>> > How do I implement row-level locking? Or is there another way?
>> >
>|||Yes. It's the primary key. Is there an implication?
Uri Dimant wrote:
> Davy
> Do you have an index on ID column?
>
>
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
> > Thanks, Uri. Most grateful.
> >
> > I still have a problem, though. I have set up two Query Analyzer
> > sessions, representing User A and User B. The first connection runs
> > this:
> >
> > begin tran
> > update Customers set Tel='01234 567 890' where ID=1
> >
> > Without committing the transaction, this SQL does apply an exclusive
> > lock on the customer with ID 1.
> >
> > This User B SQL works well too:
> >
> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> > select * from Customers where ID=1
> >
> > User B can read the dirty data.
> >
> > However, if user B goes to customer with ID 2, this will not work:
> >
> > update Customers set Name='My Customer Inc.' where ID=2
> >
> > It seems that the User A transaction is not applying a row-level lock,
> > but rather a page lock, or even table lock. How can I force the UPDATE
> > in user A's session to only lock one customer row?
> >
> >
> >
> >
> >
> > Uri Dimant wrote:
> >> Davy
> >> UPDATE statement creates an exclusive lock on the row so if the another
> >> user
> >> wants to change/update the same row he/she will be locked, however what
> >> if
> >> you have ( you probably have ) more details for the customer , so in
> >> that
> >> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
> >> procedure
> >> which makes updating
> >>
> >> Also take a look at transaction isolation levels:
> >> a.. READ UNCOMMITTED
> >> READ COMMITTED
> >> REPEATABLE READ
> >> SERIALIZABLE
> >>
> >>
> >>
> >> "Davy B" <david.bridge@.itv.com> wrote in message
> >> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >> >I have a VB6 rich client application that accesses SQL Server 2000
> >> > using ADO. The database has a table of customers. The application has
> >> > a form that shows customer details, and allows the user to navigate the
> >> > to next/previous customer in the table.
> >> >
> >> > When user A is the first to open customer 1's details, I want them to
> >> > "take ownership" of that record, such that only user A can make changes
> >> > to the record. When user B navigates to customer 1, I want user B to
> >> > be able to view the customer's details, but not be able to change them.
> >> >
> >> > However, if user B navigates to customer 2, whose record is on the same
> >> > data page as customer 1, I do want user B to be able to take ownership
> >> > of customer 2's record, and be able to make changes.
> >> >
> >> > In short, I want row-level locking on the customers table. I was
> >> > relying on the client updating a customer record to indicate they have
> >> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> >> > users view the same customer within five secs of each other, the second
> >> > user cannot ascertain that the first user has taken ownership.
> >> >
> >> > How do I implement row-level locking? Or is there another way?
> >> >
> >|||Hi
Row level locking would be occurring at data level, but if a modification of
an index is involved, you might have blocking on the index page.
Look at the output of sp_lock to see what type of lock is causing the
problem.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
> Thanks, Uri. Most grateful.
> I still have a problem, though. I have set up two Query Analyzer
> sessions, representing User A and User B. The first connection runs
> this:
> begin tran
> update Customers set Tel='01234 567 890' where ID=1
> Without committing the transaction, this SQL does apply an exclusive
> lock on the customer with ID 1.
> This User B SQL works well too:
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> select * from Customers where ID=1
> User B can read the dirty data.
> However, if user B goes to customer with ID 2, this will not work:
> update Customers set Name='My Customer Inc.' where ID=2
> It seems that the User A transaction is not applying a row-level lock,
> but rather a page lock, or even table lock. How can I force the UPDATE
> in user A's session to only lock one customer row?
>
>
> Uri Dimant wrote:
>> Davy
>> UPDATE statement creates an exclusive lock on the row so if the another
>> user
>> wants to change/update the same row he/she will be locked, however what
>> if
>> you have ( you probably have ) more details for the customer , so in
>> that
>> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
>> procedure
>> which makes updating
>> Also take a look at transaction isolation levels:
>> a.. READ UNCOMMITTED
>> READ COMMITTED
>> REPEATABLE READ
>> SERIALIZABLE
>>
>> "Davy B" <david.bridge@.itv.com> wrote in message
>> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>> >I have a VB6 rich client application that accesses SQL Server 2000
>> > using ADO. The database has a table of customers. The application has
>> > a form that shows customer details, and allows the user to navigate the
>> > to next/previous customer in the table.
>> >
>> > When user A is the first to open customer 1's details, I want them to
>> > "take ownership" of that record, such that only user A can make changes
>> > to the record. When user B navigates to customer 1, I want user B to
>> > be able to view the customer's details, but not be able to change them.
>> >
>> > However, if user B navigates to customer 2, whose record is on the same
>> > data page as customer 1, I do want user B to be able to take ownership
>> > of customer 2's record, and be able to make changes.
>> >
>> > In short, I want row-level locking on the customers table. I was
>> > relying on the client updating a customer record to indicate they have
>> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
>> > users view the same customer within five secs of each other, the second
>> > user cannot ascertain that the first user has taken ownership.
>> >
>> > How do I implement row-level locking? Or is there another way?
>> >
>|||You've described a 'pessimistic concurrency' technique. Here's an
description from the SQL Server 2005 Books Online:
<Excerpt
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/829c973e-b1d0-422c-b951-43c5968e95eb.htm">
Concurrency control theory has two classifications for the methods of
instituting concurrency control:
Pessimistic concurrency control
A system of locks prevents users from modifying data in a way that affects
other users. After a user performs an action that causes a lock to be
applied, other users cannot perform actions that would conflict with the
lock until the owner releases it. This is called pessimistic control because
it is mainly used in environments where there is high contention for data,
where the cost of protecting data with locks is less than the cost of
rolling back transactions if concurrency conflicts occur.
Optimistic concurrency control
In optimistic concurrency control, users do not lock data when they read it.
When a user updates data, the system checks to see if another user changed
the data after it was read. If another user updated the data, an error is
raised. Typically, the user receiving the error rolls back the transaction
and starts over. This is called optimistic because it is mainly used in
environments where there is low contention for data, and where the cost of
occasionally rolling back a transaction outweighs the costs of locking data
when read.
</Excerpt>
Although you could use SQL Server's row locking to implement pessimistic
concurrency, that would be especially bad for your application because it
would require that you keep the transaction open while waiting for user
action. Consider that the user may go to lunch or take the afternoon off
while viewing customer data. A cardinal rule is that one should never keep
a transaction open while waiting for user action.
I suggest you consider using an optimistic concurrency approach instead.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>I have a VB6 rich client application that accesses SQL Server 2000
> using ADO. The database has a table of customers. The application has
> a form that shows customer details, and allows the user to navigate the
> to next/previous customer in the table.
> When user A is the first to open customer 1's details, I want them to
> "take ownership" of that record, such that only user A can make changes
> to the record. When user B navigates to customer 1, I want user B to
> be able to view the customer's details, but not be able to change them.
> However, if user B navigates to customer 2, whose record is on the same
> data page as customer 1, I do want user B to be able to take ownership
> of customer 2's record, and be able to make changes.
> In short, I want row-level locking on the customers table. I was
> relying on the client updating a customer record to indicate they have
> ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> users view the same customer within five secs of each other, the second
> user cannot ascertain that the first user has taken ownership.
> How do I implement row-level locking? Or is there another way?
>|||Dave
I did just testing and it worked for me just fine, perhaps I missunderstood
you.
--Open connection1 (userA)
--Run
create table t (c1 int primary key,c2 char(1))
insert into t values (1,'a')
insert into t values (2,'b')
begin tran
update t set c2='c' where c1=1
--rollback tran
--Open connection2 (userB)
--Run
select * from t where c1 =2
update t set c2='t' where c1=2
--After I ssued ROLLBACK at connection1 i see
c1 c2
-- --
1 a
2 t
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149594164.589487.34150@.c74g2000cwc.googlegroups.com...
> Yes. It's the primary key. Is there an implication?
>
> Uri Dimant wrote:
>> Davy
>> Do you have an index on ID column?
>>
>>
>> "Davy B" <david.bridge@.itv.com> wrote in message
>> news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
>> > Thanks, Uri. Most grateful.
>> >
>> > I still have a problem, though. I have set up two Query Analyzer
>> > sessions, representing User A and User B. The first connection runs
>> > this:
>> >
>> > begin tran
>> > update Customers set Tel='01234 567 890' where ID=1
>> >
>> > Without committing the transaction, this SQL does apply an exclusive
>> > lock on the customer with ID 1.
>> >
>> > This User B SQL works well too:
>> >
>> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> > select * from Customers where ID=1
>> >
>> > User B can read the dirty data.
>> >
>> > However, if user B goes to customer with ID 2, this will not work:
>> >
>> > update Customers set Name='My Customer Inc.' where ID=2
>> >
>> > It seems that the User A transaction is not applying a row-level lock,
>> > but rather a page lock, or even table lock. How can I force the UPDATE
>> > in user A's session to only lock one customer row?
>> >
>> >
>> >
>> >
>> >
>> > Uri Dimant wrote:
>> >> Davy
>> >> UPDATE statement creates an exclusive lock on the row so if the
>> >> another
>> >> user
>> >> wants to change/update the same row he/she will be locked, however
>> >> what
>> >> if
>> >> you have ( you probably have ) more details for the customer , so in
>> >> that
>> >> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
>> >> procedure
>> >> which makes updating
>> >>
>> >> Also take a look at transaction isolation levels:
>> >> a.. READ UNCOMMITTED
>> >> READ COMMITTED
>> >> REPEATABLE READ
>> >> SERIALIZABLE
>> >>
>> >>
>> >>
>> >> "Davy B" <david.bridge@.itv.com> wrote in message
>> >> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>> >> >I have a VB6 rich client application that accesses SQL Server 2000
>> >> > using ADO. The database has a table of customers. The application
>> >> > has
>> >> > a form that shows customer details, and allows the user to navigate
>> >> > the
>> >> > to next/previous customer in the table.
>> >> >
>> >> > When user A is the first to open customer 1's details, I want them
>> >> > to
>> >> > "take ownership" of that record, such that only user A can make
>> >> > changes
>> >> > to the record. When user B navigates to customer 1, I want user B
>> >> > to
>> >> > be able to view the customer's details, but not be able to change
>> >> > them.
>> >> >
>> >> > However, if user B navigates to customer 2, whose record is on the
>> >> > same
>> >> > data page as customer 1, I do want user B to be able to take
>> >> > ownership
>> >> > of customer 2's record, and be able to make changes.
>> >> >
>> >> > In short, I want row-level locking on the customers table. I was
>> >> > relying on the client updating a customer record to indicate they
>> >> > have
>> >> > ownership, but ADO caches changes for (I believe) 5 seconds, so if
>> >> > two
>> >> > users view the same customer within five secs of each other, the
>> >> > second
>> >> > user cannot ascertain that the first user has taken ownership.
>> >> >
>> >> > How do I implement row-level locking? Or is there another way?
>> >> >
>> >
>|||Uri,
Yeah, that's my problem. I want user B to be able to save a change on
the second record, even though user A has got the first record locked.
I don't want user B to have to wait for user A to commit or roll back
their transaction.
I have used sp_lock, and established that the first update establishes
three locks if type TAB, PAG and KEY. I am starting to guess that the
behaviour I want is not possible - since the table has a primary key,
which is effectively a unique index, allowing two rows to be updated by
two users could compromise the index, so SQL Server prevents it.
If I continue with my current design, which is optimistic, I wonder
whether I could use files to indicate ownership? If all users have
access to a shared folder, the client application could open a WSH
textstream called C1.txt (where 1 = row ID), and leave it open. Anyone
who wants ownership of record 1 could attempt to create C1.txt - this
will fail if another user already has that file open. I'm thinking
laterally now?
Davy
Uri Dimant wrote:
> Dave
> I did just testing and it worked for me just fine, perhaps I missunderstood
> you.
> --Open connection1 (userA)
> --Run
> create table t (c1 int primary key,c2 char(1))
> insert into t values (1,'a')
> insert into t values (2,'b')
> begin tran
> update t set c2='c' where c1=1
> --rollback tran
> --Open connection2 (userB)
> --Run
> select * from t where c1 =2
> update t set c2='t' where c1=2
> --After I ssued ROLLBACK at connection1 i see
> c1 c2
> -- --
> 1 a
> 2 t
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149594164.589487.34150@.c74g2000cwc.googlegroups.com...
> > Yes. It's the primary key. Is there an implication?
> >
> >
> > Uri Dimant wrote:
> >> Davy
> >> Do you have an index on ID column?
> >>
> >>
> >>
> >>
> >>
> >> "Davy B" <david.bridge@.itv.com> wrote in message
> >> news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
> >> > Thanks, Uri. Most grateful.
> >> >
> >> > I still have a problem, though. I have set up two Query Analyzer
> >> > sessions, representing User A and User B. The first connection runs
> >> > this:
> >> >
> >> > begin tran
> >> > update Customers set Tel='01234 567 890' where ID=1
> >> >
> >> > Without committing the transaction, this SQL does apply an exclusive
> >> > lock on the customer with ID 1.
> >> >
> >> > This User B SQL works well too:
> >> >
> >> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> >> > select * from Customers where ID=1
> >> >
> >> > User B can read the dirty data.
> >> >
> >> > However, if user B goes to customer with ID 2, this will not work:
> >> >
> >> > update Customers set Name='My Customer Inc.' where ID=2
> >> >
> >> > It seems that the User A transaction is not applying a row-level lock,
> >> > but rather a page lock, or even table lock. How can I force the UPDATE
> >> > in user A's session to only lock one customer row?
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > Uri Dimant wrote:
> >> >> Davy
> >> >> UPDATE statement creates an exclusive lock on the row so if the
> >> >> another
> >> >> user
> >> >> wants to change/update the same row he/she will be locked, however
> >> >> what
> >> >> if
> >> >> you have ( you probably have ) more details for the customer , so in
> >> >> that
> >> >> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
> >> >> procedure
> >> >> which makes updating
> >> >>
> >> >> Also take a look at transaction isolation levels:
> >> >> a.. READ UNCOMMITTED
> >> >> READ COMMITTED
> >> >> REPEATABLE READ
> >> >> SERIALIZABLE
> >> >>
> >> >>
> >> >>
> >> >> "Davy B" <david.bridge@.itv.com> wrote in message
> >> >> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >> >> >I have a VB6 rich client application that accesses SQL Server 2000
> >> >> > using ADO. The database has a table of customers. The application
> >> >> > has
> >> >> > a form that shows customer details, and allows the user to navigate
> >> >> > the
> >> >> > to next/previous customer in the table.
> >> >> >
> >> >> > When user A is the first to open customer 1's details, I want them
> >> >> > to
> >> >> > "take ownership" of that record, such that only user A can make
> >> >> > changes
> >> >> > to the record. When user B navigates to customer 1, I want user B
> >> >> > to
> >> >> > be able to view the customer's details, but not be able to change
> >> >> > them.
> >> >> >
> >> >> > However, if user B navigates to customer 2, whose record is on the
> >> >> > same
> >> >> > data page as customer 1, I do want user B to be able to take
> >> >> > ownership
> >> >> > of customer 2's record, and be able to make changes.
> >> >> >
> >> >> > In short, I want row-level locking on the customers table. I was
> >> >> > relying on the client updating a customer record to indicate they
> >> >> > have
> >> >> > ownership, but ADO caches changes for (I believe) 5 seconds, so if
> >> >> > two
> >> >> > users view the same customer within five secs of each other, the
> >> >> > second
> >> >> > user cannot ascertain that the first user has taken ownership.
> >> >> >
> >> >> > How do I implement row-level locking? Or is there another way?
> >> >> >
> >> >
> >|||Thanks for getting involved, Mike.
It now seems clear that if SQL Server was to allow two users to lock
and update different rows in the same table, the primary key could get
compromised. So it must be preventing me from achieving a row-level
lock in the interest of maintaining integrity.
My current client design is optimistic, but the users are now having
problems whereby two of them do occasionally open the same record. The
first makes changes which get overwritten by the second. When I change
the design to pessimistic, only the first user who gets an exclusive
lock is able to save changes to any record in that same table. The
others can do a dirty read, but it's effectively view-only. I am now
looking at a design that retains optimism, and uses file locks - if
user A opens record 123, they attempt to create a file called 123.txt
in a shared folder. The file creation will fail if another user has
already opened record 123.
Maybe there's a better way?
Davy
Michael Epprecht [MSFT] wrote:
> Hi
> Row level locking would be occurring at data level, but if a modification of
> an index is involved, you might have blocking on the index page.
> Look at the output of sp_lock to see what type of lock is causing the
> problem.
> Regards
> --
> Mike
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149593405.365896.293950@.c74g2000cwc.googlegroups.com...
> > Thanks, Uri. Most grateful.
> >
> > I still have a problem, though. I have set up two Query Analyzer
> > sessions, representing User A and User B. The first connection runs
> > this:
> >
> > begin tran
> > update Customers set Tel='01234 567 890' where ID=1
> >
> > Without committing the transaction, this SQL does apply an exclusive
> > lock on the customer with ID 1.
> >
> > This User B SQL works well too:
> >
> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> > select * from Customers where ID=1
> >
> > User B can read the dirty data.
> >
> > However, if user B goes to customer with ID 2, this will not work:
> >
> > update Customers set Name='My Customer Inc.' where ID=2
> >
> > It seems that the User A transaction is not applying a row-level lock,
> > but rather a page lock, or even table lock. How can I force the UPDATE
> > in user A's session to only lock one customer row?
> >
> >
> >
> >
> >
> > Uri Dimant wrote:
> >> Davy
> >> UPDATE statement creates an exclusive lock on the row so if the another
> >> user
> >> wants to change/update the same row he/she will be locked, however what
> >> if
> >> you have ( you probably have ) more details for the customer , so in
> >> that
> >> case you need to wrap BEGIN TRAN ....COMMIT TRAN into the stored
> >> procedure
> >> which makes updating
> >>
> >> Also take a look at transaction isolation levels:
> >> a.. READ UNCOMMITTED
> >> READ COMMITTED
> >> REPEATABLE READ
> >> SERIALIZABLE
> >>
> >>
> >>
> >> "Davy B" <david.bridge@.itv.com> wrote in message
> >> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >> >I have a VB6 rich client application that accesses SQL Server 2000
> >> > using ADO. The database has a table of customers. The application has
> >> > a form that shows customer details, and allows the user to navigate the
> >> > to next/previous customer in the table.
> >> >
> >> > When user A is the first to open customer 1's details, I want them to
> >> > "take ownership" of that record, such that only user A can make changes
> >> > to the record. When user B navigates to customer 1, I want user B to
> >> > be able to view the customer's details, but not be able to change them.
> >> >
> >> > However, if user B navigates to customer 2, whose record is on the same
> >> > data page as customer 1, I do want user B to be able to take ownership
> >> > of customer 2's record, and be able to make changes.
> >> >
> >> > In short, I want row-level locking on the customers table. I was
> >> > relying on the client updating a customer record to indicate they have
> >> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> >> > users view the same customer within five secs of each other, the second
> >> > user cannot ascertain that the first user has taken ownership.
> >> >
> >> > How do I implement row-level locking? Or is there another way?
> >> >
> >|||Yes, Dan. My current design is optimistic, which is fine 99% of the
time. But occasionally two users do open the same record; one user
overwrites changes made by another. The problem with pessimistic
design is that when one user opts to change a record it precludes all
others from changing one. The whole table gets locked.
I am considering continuing with an optimistic design, but using files
created in a shared area to indicate ownership. If user A opens record
123 for modification, they will create and lock a file called 123.txt.
When user B access that record they attempt to create a 123.txt, but
get "access denied", since user A has the file locked. Lateral
thinking? Or should I be doing this a different way?
Davy
Dan Guzman wrote:
> You've described a 'pessimistic concurrency' technique. Here's an
> description from the SQL Server 2005 Books Online:
> <Excerpt
> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/829c973e-b1d0-422c-b951-43c5968e95eb.htm">
> Concurrency control theory has two classifications for the methods of
> instituting concurrency control:
> Pessimistic concurrency control
> A system of locks prevents users from modifying data in a way that affects
> other users. After a user performs an action that causes a lock to be
> applied, other users cannot perform actions that would conflict with the
> lock until the owner releases it. This is called pessimistic control because
> it is mainly used in environments where there is high contention for data,
> where the cost of protecting data with locks is less than the cost of
> rolling back transactions if concurrency conflicts occur.
> Optimistic concurrency control
> In optimistic concurrency control, users do not lock data when they read it.
> When a user updates data, the system checks to see if another user changed
> the data after it was read. If another user updated the data, an error is
> raised. Typically, the user receiving the error rolls back the transaction
> and starts over. This is called optimistic because it is mainly used in
> environments where there is low contention for data, and where the cost of
> occasionally rolling back a transaction outweighs the costs of locking data
> when read.
> </Excerpt>
> Although you could use SQL Server's row locking to implement pessimistic
> concurrency, that would be especially bad for your application because it
> would require that you keep the transaction open while waiting for user
> action. Consider that the user may go to lunch or take the afternoon off
> while viewing customer data. A cardinal rule is that one should never keep
> a transaction open while waiting for user action.
> I suggest you consider using an optimistic concurrency approach instead.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >I have a VB6 rich client application that accesses SQL Server 2000
> > using ADO. The database has a table of customers. The application has
> > a form that shows customer details, and allows the user to navigate the
> > to next/previous customer in the table.
> >
> > When user A is the first to open customer 1's details, I want them to
> > "take ownership" of that record, such that only user A can make changes
> > to the record. When user B navigates to customer 1, I want user B to
> > be able to view the customer's details, but not be able to change them.
> >
> > However, if user B navigates to customer 2, whose record is on the same
> > data page as customer 1, I do want user B to be able to take ownership
> > of customer 2's record, and be able to make changes.
> >
> > In short, I want row-level locking on the customers table. I was
> > relying on the client updating a customer record to indicate they have
> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> > users view the same customer within five secs of each other, the second
> > user cannot ascertain that the first user has taken ownership.
> >
> > How do I implement row-level locking? Or is there another way?
> >|||Hi
What a lot of developers do it to use row versioning. This version stamp is
read and when updating the row, you first compare what is on the row
compared to what you have read earlier. If they are the same, the update may
proceed, if they differ, then someone else has made a change and the update
fails.
The SQL Server data type timestamp is used for this.
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149604836.357340.208660@.f6g2000cwb.googlegroups.com...
> Yes, Dan. My current design is optimistic, which is fine 99% of the
> time. But occasionally two users do open the same record; one user
> overwrites changes made by another. The problem with pessimistic
> design is that when one user opts to change a record it precludes all
> others from changing one. The whole table gets locked.
> I am considering continuing with an optimistic design, but using files
> created in a shared area to indicate ownership. If user A opens record
> 123 for modification, they will create and lock a file called 123.txt.
> When user B access that record they attempt to create a 123.txt, but
> get "access denied", since user A has the file locked. Lateral
> thinking? Or should I be doing this a different way?
> Davy
>
>
> Dan Guzman wrote:
>> You've described a 'pessimistic concurrency' technique. Here's an
>> description from the SQL Server 2005 Books Online:
>> <Excerpt
>> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/829c973e-b1d0-422c-b951-43c5968e95eb.htm">
>> Concurrency control theory has two classifications for the methods of
>> instituting concurrency control:
>> Pessimistic concurrency control
>> A system of locks prevents users from modifying data in a way that
>> affects
>> other users. After a user performs an action that causes a lock to be
>> applied, other users cannot perform actions that would conflict with the
>> lock until the owner releases it. This is called pessimistic control
>> because
>> it is mainly used in environments where there is high contention for
>> data,
>> where the cost of protecting data with locks is less than the cost of
>> rolling back transactions if concurrency conflicts occur.
>> Optimistic concurrency control
>> In optimistic concurrency control, users do not lock data when they read
>> it.
>> When a user updates data, the system checks to see if another user
>> changed
>> the data after it was read. If another user updated the data, an error is
>> raised. Typically, the user receiving the error rolls back the
>> transaction
>> and starts over. This is called optimistic because it is mainly used in
>> environments where there is low contention for data, and where the cost
>> of
>> occasionally rolling back a transaction outweighs the costs of locking
>> data
>> when read.
>> </Excerpt>
>> Although you could use SQL Server's row locking to implement pessimistic
>> concurrency, that would be especially bad for your application because it
>> would require that you keep the transaction open while waiting for user
>> action. Consider that the user may go to lunch or take the afternoon off
>> while viewing customer data. A cardinal rule is that one should never
>> keep
>> a transaction open while waiting for user action.
>> I suggest you consider using an optimistic concurrency approach instead.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Davy B" <david.bridge@.itv.com> wrote in message
>> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>> >I have a VB6 rich client application that accesses SQL Server 2000
>> > using ADO. The database has a table of customers. The application has
>> > a form that shows customer details, and allows the user to navigate the
>> > to next/previous customer in the table.
>> >
>> > When user A is the first to open customer 1's details, I want them to
>> > "take ownership" of that record, such that only user A can make changes
>> > to the record. When user B navigates to customer 1, I want user B to
>> > be able to view the customer's details, but not be able to change them.
>> >
>> > However, if user B navigates to customer 2, whose record is on the same
>> > data page as customer 1, I do want user B to be able to take ownership
>> > of customer 2's record, and be able to make changes.
>> >
>> > In short, I want row-level locking on the customers table. I was
>> > relying on the client updating a customer record to indicate they have
>> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
>> > users view the same customer within five secs of each other, the second
>> > user cannot ascertain that the first user has taken ownership.
>> >
>> > How do I implement row-level locking? Or is there another way?
>> >
>|||On 6 Jun 2006 07:31:02 -0700, "Davy B" <david.bridge@.itv.com> wrote:
>It now seems clear that if SQL Server was to allow two users to lock
>and update different rows in the same table, the primary key could get
>compromised. So it must be preventing me from achieving a row-level
>lock in the interest of maintaining integrity.
>My current client design is optimistic, but the users are now having
>problems whereby two of them do occasionally open the same record. The
>first makes changes which get overwritten by the second. When I change
>the design to pessimistic, only the first user who gets an exclusive
>lock is able to save changes to any record in that same table. The
>others can do a dirty read, but it's effectively view-only. I am now
>looking at a design that retains optimism, and uses file locks - if
>user A opens record 123, they attempt to create a file called 123.txt
>in a shared folder. The file creation will fail if another user has
>already opened record 123.
>Maybe there's a better way?
Not really.
If you do that file-lock business, second user will still be locked
out! It's the logical situation, not the mechanism, which is the
problem.
Anyway, why use a file, create your own lock table, if you really want
to go that way. Which you probably shouldn't.
My advice is to fiddle with the ADO/SQLServer features for pessimistic
locking a little further. Set the lock_timeout to a small number so
the second guy to write can get a quick message that the record is
locked. Better yet, warn the second guy before he modifies anything,
that the record is already locked! Boy, this optimistic locking is a
lost art, ain't it?
Josh|||> But occasionally two users do open the same record; one user
> overwrites changes made by another.
That's chaos (last in wins) rather than optimistic concurrency. To properly
implement optimistic concurrency you need to check to ensure initially
retrieved data haven't changed and notify the user accordingly.
As Mike suggested, you can simplify coding by checking a rowversion column
(a.k.a timestamp) during the update rather than specifying all columns to be
updated. SQL Server generates a unique value for the rowversion each time
the row is updated so the check will fail of the row has changed since
initially read by the application. Below is an example.
CREATE TABLE Table1
(
Table1_Key int NOT NULL
CONSTRAINT PK_Table1 PRIMARY KEY,
Table1_Data int NOT NULL,
RowVersionColumn RowVersion NOT NULL
)
GO
ALTER PROC dbo.usp_Update_Table1
@.Table1_Key int,
@.Table1_Data int,
@.ExpectedRowVersion rowversion
AS
SET NOCOUNT ON
UPDATE dbo.Table1
SET @.Table1_Data = @.Table1_Data
WHERE
Table1_Key = @.Table1_Key AND
RowVersionColumn = @.ExpectedRowVersion
IF @.@.ROWCOUNT = 0
BEGIN
RAISERROR('Data updated or deleted by another user', 16, 1)
END
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Davy B" <david.bridge@.itv.com> wrote in message
news:1149604836.357340.208660@.f6g2000cwb.googlegroups.com...
> Yes, Dan. My current design is optimistic, which is fine 99% of the
> time. But occasionally two users do open the same record; one user
> overwrites changes made by another. The problem with pessimistic
> design is that when one user opts to change a record it precludes all
> others from changing one. The whole table gets locked.
> I am considering continuing with an optimistic design, but using files
> created in a shared area to indicate ownership. If user A opens record
> 123 for modification, they will create and lock a file called 123.txt.
> When user B access that record they attempt to create a 123.txt, but
> get "access denied", since user A has the file locked. Lateral
> thinking? Or should I be doing this a different way?
> Davy
>
>
> Dan Guzman wrote:
>> You've described a 'pessimistic concurrency' technique. Here's an
>> description from the SQL Server 2005 Books Online:
>> <Excerpt
>> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/829c973e-b1d0-422c-b951-43c5968e95eb.htm">
>> Concurrency control theory has two classifications for the methods of
>> instituting concurrency control:
>> Pessimistic concurrency control
>> A system of locks prevents users from modifying data in a way that
>> affects
>> other users. After a user performs an action that causes a lock to be
>> applied, other users cannot perform actions that would conflict with the
>> lock until the owner releases it. This is called pessimistic control
>> because
>> it is mainly used in environments where there is high contention for
>> data,
>> where the cost of protecting data with locks is less than the cost of
>> rolling back transactions if concurrency conflicts occur.
>> Optimistic concurrency control
>> In optimistic concurrency control, users do not lock data when they read
>> it.
>> When a user updates data, the system checks to see if another user
>> changed
>> the data after it was read. If another user updated the data, an error is
>> raised. Typically, the user receiving the error rolls back the
>> transaction
>> and starts over. This is called optimistic because it is mainly used in
>> environments where there is low contention for data, and where the cost
>> of
>> occasionally rolling back a transaction outweighs the costs of locking
>> data
>> when read.
>> </Excerpt>
>> Although you could use SQL Server's row locking to implement pessimistic
>> concurrency, that would be especially bad for your application because it
>> would require that you keep the transaction open while waiting for user
>> action. Consider that the user may go to lunch or take the afternoon off
>> while viewing customer data. A cardinal rule is that one should never
>> keep
>> a transaction open while waiting for user action.
>> I suggest you consider using an optimistic concurrency approach instead.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Davy B" <david.bridge@.itv.com> wrote in message
>> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
>> >I have a VB6 rich client application that accesses SQL Server 2000
>> > using ADO. The database has a table of customers. The application has
>> > a form that shows customer details, and allows the user to navigate the
>> > to next/previous customer in the table.
>> >
>> > When user A is the first to open customer 1's details, I want them to
>> > "take ownership" of that record, such that only user A can make changes
>> > to the record. When user B navigates to customer 1, I want user B to
>> > be able to view the customer's details, but not be able to change them.
>> >
>> > However, if user B navigates to customer 2, whose record is on the same
>> > data page as customer 1, I do want user B to be able to take ownership
>> > of customer 2's record, and be able to make changes.
>> >
>> > In short, I want row-level locking on the customers table. I was
>> > relying on the client updating a customer record to indicate they have
>> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
>> > users view the same customer within five secs of each other, the second
>> > user cannot ascertain that the first user has taken ownership.
>> >
>> > How do I implement row-level locking? Or is there another way?
>> >
>|||It does help, Dan. I am most grateful.
Dan Guzman wrote:
> > But occasionally two users do open the same record; one user
> > overwrites changes made by another.
> That's chaos (last in wins) rather than optimistic concurrency. To properly
> implement optimistic concurrency you need to check to ensure initially
> retrieved data haven't changed and notify the user accordingly.
> As Mike suggested, you can simplify coding by checking a rowversion column
> (a.k.a timestamp) during the update rather than specifying all columns to be
> updated. SQL Server generates a unique value for the rowversion each time
> the row is updated so the check will fail of the row has changed since
> initially read by the application. Below is an example.
> CREATE TABLE Table1
> (
> Table1_Key int NOT NULL
> CONSTRAINT PK_Table1 PRIMARY KEY,
> Table1_Data int NOT NULL,
> RowVersionColumn RowVersion NOT NULL
> )
> GO
> ALTER PROC dbo.usp_Update_Table1
> @.Table1_Key int,
> @.Table1_Data int,
> @.ExpectedRowVersion rowversion
> AS
> SET NOCOUNT ON
> UPDATE dbo.Table1
> SET @.Table1_Data = @.Table1_Data
> WHERE
> Table1_Key = @.Table1_Key AND
> RowVersionColumn = @.ExpectedRowVersion
> IF @.@.ROWCOUNT = 0
> BEGIN
> RAISERROR('Data updated or deleted by another user', 16, 1)
> END
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Davy B" <david.bridge@.itv.com> wrote in message
> news:1149604836.357340.208660@.f6g2000cwb.googlegroups.com...
> > Yes, Dan. My current design is optimistic, which is fine 99% of the
> > time. But occasionally two users do open the same record; one user
> > overwrites changes made by another. The problem with pessimistic
> > design is that when one user opts to change a record it precludes all
> > others from changing one. The whole table gets locked.
> >
> > I am considering continuing with an optimistic design, but using files
> > created in a shared area to indicate ownership. If user A opens record
> > 123 for modification, they will create and lock a file called 123.txt.
> > When user B access that record they attempt to create a 123.txt, but
> > get "access denied", since user A has the file locked. Lateral
> > thinking? Or should I be doing this a different way?
> >
> > Davy
> >
> >
> >
> >
> > Dan Guzman wrote:
> >> You've described a 'pessimistic concurrency' technique. Here's an
> >> description from the SQL Server 2005 Books Online:
> >>
> >> <Excerpt
> >> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/829c973e-b1d0-422c-b951-43c5968e95eb.htm">
> >>
> >> Concurrency control theory has two classifications for the methods of
> >> instituting concurrency control:
> >>
> >> Pessimistic concurrency control
> >>
> >> A system of locks prevents users from modifying data in a way that
> >> affects
> >> other users. After a user performs an action that causes a lock to be
> >> applied, other users cannot perform actions that would conflict with the
> >> lock until the owner releases it. This is called pessimistic control
> >> because
> >> it is mainly used in environments where there is high contention for
> >> data,
> >> where the cost of protecting data with locks is less than the cost of
> >> rolling back transactions if concurrency conflicts occur.
> >>
> >> Optimistic concurrency control
> >>
> >> In optimistic concurrency control, users do not lock data when they read
> >> it.
> >> When a user updates data, the system checks to see if another user
> >> changed
> >> the data after it was read. If another user updated the data, an error is
> >> raised. Typically, the user receiving the error rolls back the
> >> transaction
> >> and starts over. This is called optimistic because it is mainly used in
> >> environments where there is low contention for data, and where the cost
> >> of
> >> occasionally rolling back a transaction outweighs the costs of locking
> >> data
> >> when read.
> >>
> >> </Excerpt>
> >>
> >> Although you could use SQL Server's row locking to implement pessimistic
> >> concurrency, that would be especially bad for your application because it
> >> would require that you keep the transaction open while waiting for user
> >> action. Consider that the user may go to lunch or take the afternoon off
> >> while viewing customer data. A cardinal rule is that one should never
> >> keep
> >> a transaction open while waiting for user action.
> >>
> >> I suggest you consider using an optimistic concurrency approach instead.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Davy B" <david.bridge@.itv.com> wrote in message
> >> news:1149584339.190809.22090@.h76g2000cwa.googlegroups.com...
> >> >I have a VB6 rich client application that accesses SQL Server 2000
> >> > using ADO. The database has a table of customers. The application has
> >> > a form that shows customer details, and allows the user to navigate the
> >> > to next/previous customer in the table.
> >> >
> >> > When user A is the first to open customer 1's details, I want them to
> >> > "take ownership" of that record, such that only user A can make changes
> >> > to the record. When user B navigates to customer 1, I want user B to
> >> > be able to view the customer's details, but not be able to change them.
> >> >
> >> > However, if user B navigates to customer 2, whose record is on the same
> >> > data page as customer 1, I do want user B to be able to take ownership
> >> > of customer 2's record, and be able to make changes.
> >> >
> >> > In short, I want row-level locking on the customers table. I was
> >> > relying on the client updating a customer record to indicate they have
> >> > ownership, but ADO caches changes for (I believe) 5 seconds, so if two
> >> > users view the same customer within five secs of each other, the second
> >> > user cannot ascertain that the first user has taken ownership.
> >> >
> >> > How do I implement row-level locking? Or is there another way?
> >> >
> >
Wednesday, March 21, 2012
Row Locking in SQL via ADO (VB 6)
Hi,
I'm trying to use the pessimistic row locking of SQL to get following result.
When a customer form is openend, the row should be locked for writing.
This lock should be left open until the user closes the customer form.
I cannot use transactions because there can be more then 1 customer form open in the same app. In ADO a connection is IN transaction or is NOT, nested transactions are not supported.
How can I keep this row locked on SQL and this until I unlock it or the connection is broken ( in case of problems on client machine )?
And how can I see on another machine of this row ( customer ) is already locked so I can open him in read-only?
For the moment I'm using extra fields that hold the info wether the customer is locked en by whom. But that's on application level, not on DB-level.
I hope this is clear enough.I've often wondered if there is any reason that justifies pessimistic locking. So far I haven't found one. I recommend shutting down the SQL Server to get pessimistic locking... If the box is off, no other user can modify your data, and it makes the scaling problems caused by pessimistic locking less of a problem.
To answer your question more directly, yes pessimistic locking can be done using ADO. It has been a long time since I've had any reason to try to hurt myself that badly after I established that it was possible, so I'm fuzzy on the details.
-PatP|||This may be what you're looking for:
http://www34.brinkster.com/a213855/|||Pat,
I'm convinced that pessimistic locking is not the ideal solution.
How should i take care of the record-locking then?
Regards,
Sven Peeters|||Ummmm, optimistic locking (http://search.microsoft.com/search/results.aspx?qu=%22optimistic+locking%22&View=msdn&st=b&c=0&s=1&swc=0)? I especially reccomend An update on UPDATing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbdev00/html/vb00e1.asp), but there are lots of good articles to read!
-PatP
I'm trying to use the pessimistic row locking of SQL to get following result.
When a customer form is openend, the row should be locked for writing.
This lock should be left open until the user closes the customer form.
I cannot use transactions because there can be more then 1 customer form open in the same app. In ADO a connection is IN transaction or is NOT, nested transactions are not supported.
How can I keep this row locked on SQL and this until I unlock it or the connection is broken ( in case of problems on client machine )?
And how can I see on another machine of this row ( customer ) is already locked so I can open him in read-only?
For the moment I'm using extra fields that hold the info wether the customer is locked en by whom. But that's on application level, not on DB-level.
I hope this is clear enough.I've often wondered if there is any reason that justifies pessimistic locking. So far I haven't found one. I recommend shutting down the SQL Server to get pessimistic locking... If the box is off, no other user can modify your data, and it makes the scaling problems caused by pessimistic locking less of a problem.
To answer your question more directly, yes pessimistic locking can be done using ADO. It has been a long time since I've had any reason to try to hurt myself that badly after I established that it was possible, so I'm fuzzy on the details.
-PatP|||This may be what you're looking for:
http://www34.brinkster.com/a213855/|||Pat,
I'm convinced that pessimistic locking is not the ideal solution.
How should i take care of the record-locking then?
Regards,
Sven Peeters|||Ummmm, optimistic locking (http://search.microsoft.com/search/results.aspx?qu=%22optimistic+locking%22&View=msdn&st=b&c=0&s=1&swc=0)? I especially reccomend An update on UPDATing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbdev00/html/vb00e1.asp), but there are lots of good articles to read!
-PatP
Friday, March 9, 2012
Row cannot be located for updating
Hi,
I am getting "Row cannot be located for updating. Some values may have
been changed" error when I try to update from visual basic with ado.
This happens only when set as default locale on the pc other language
than English.
Anybody can help on this
Thanks
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!A M (antmios@.yahoo.com) writes:
> I am getting "Row cannot be located for updating. Some values may have
> been changed" error when I try to update from visual basic with ado.
> This happens only when set as default locale on the pc other language
> than English.
> Anybody can help on this
I can vaguely guess what is going on, but with out a reproducible case
I find it difficult to say anything useful. Can you provide a sample?
That would need to include both the CREATE TABLE statement for the table
and the VB code.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Subscribe to:
Posts (Atom)