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