hi. i don't have much experience with locking using lock hints so wondered i
f
someone could help me with usage of ROWLOCK. i am writing a number of procs
which will perform validation on data prior to performing updates. i need
read consistency for the duration of these procs whilst guaranteeing maximum
concurrency. therefore i intend to use the following pattern:
create procedure MyProcedure
@.MyParam int
as
-- wrap in transaction
begin transaction
-- obtain lock
select 1
from MyTable (HOLDLOCK, UPDLOCK)
where MyPKColumn = @.MyParam
-- other operations here involving SELECTs on MyTable
-- perform update
update MyTable
set MyOthercolumn = 'NewValue'
where MyPKColumn = @.MyParam
-- release all locks
commit transaction
what i don't like about this is that i am beginning my transaction earlier
than i would prefer, but otherwise this appears to meet my requirements. is
this a good strategy, or is there a better way? what issues might i face?
many thanks
kh> what i don't like about this is that i am beginning my transaction earlier
> than i would prefer, but otherwise this appears to meet my requirements.
> is
> this a good strategy, or is there a better way? what issues might i face?
You are probably also locking pages (or perhaps even the entire table). IF
you take this path you might want to look into specifying ROWLOCK so that
you only lock the particular row that you are working on.
It seems like you are trying to reinvent the wheel.
You don't account for the situation where the data has changed since the
user initially retrieved the data.
User A retrieves the data for PKcol = 1
User B retrieves the data for PKcol = 1
User A goes to lunch
User B starts updating the data (within the application GUI)
Userr B hits "save" and writes the data to the database
User A comes back from lunch, finishes updating the data within the GUI, and
clicks save.
The data that User B entered is overwritten by User A.
One way around this problem is to pass the old and new values to the stored
procedure. The WHERE clause would use the primary key and it would compare
the @.old params to the data that is in the table. If @.@.rowcount = 0 the
data was different and the update did not happen.
Now that I have you worried about that type of concurrency issue, lets get
back to your validation question.
Can't you validate data within the GUI?
If not you should perform data validation (I assume that this is the "--
other operations here involving SELECTs on MyTable" outside of a
transaction. Heck, I don't even see why you need a transaction in this
stored procedure. It does not seem to buy you anything.
I don't know what type of data validation you need to do. Lets say that you
cannot have multiple UserNames or FileNames within a table. You could do
something like this before update statement. The RETURN will cause the
stored procedure to end. It will also pass back (via the return code) the
value within the parens.
--validation check
IF EXISTS (SELECT * FROM dbo.MyTable WHERE UserName = @.MyUserName )
BEGIN
RETURN (1)
END
--validation check
IF EXISTS (SELECT * FROM dbo.MyTable WHERE FileName= @.MyFileName )
BEGIN
RETURN (2)
END
--everything is a-ok, lets update
UPDATE dbo.MyTable SET MyOthercolumn = 'NewValue'
WHERE MyPKColumn = @.MyParam
RETURN (0)
Keith Kratochvil
"kh" <kh@.newsgroups.nospam> wrote in message
news:FBDE5CBC-4CB8-4CE2-8928-13BB3624F0C4@.microsoft.com...
> hi. i don't have much experience with locking using lock hints so wondered
> if
> someone could help me with usage of ROWLOCK. i am writing a number of
> procs
> which will perform validation on data prior to performing updates. i need
> read consistency for the duration of these procs whilst guaranteeing
> maximum
> concurrency. therefore i intend to use the following pattern:
> create procedure MyProcedure
> @.MyParam int
> as
> -- wrap in transaction
> begin transaction
> -- obtain lock
> select 1
> from MyTable (HOLDLOCK, UPDLOCK)
> where MyPKColumn = @.MyParam
> -- other operations here involving SELECTs on MyTable
> -- perform update
> update MyTable
> set MyOthercolumn = 'NewValue'
> where MyPKColumn = @.MyParam
> -- release all locks
> commit transaction
> what i don't like about this is that i am beginning my transaction earlier
> than i would prefer, but otherwise this appears to meet my requirements.
> is
> this a good strategy, or is there a better way? what issues might i face?
> many thanks
> kh
>|||keith. many thanks. some notes for clarity:
> You are probably also locking pages (or perhaps even the entire table). I
F
> you take this path you might want to look into specifying ROWLOCK so that
> you only lock the particular row that you are working on.
sorry, copy and paste error: the lock hints should of course be (HOLDLOCK,
ROWLOCK) and since I am selecting using the Primary Key I am (hopefully) onl
y
locking a single record.
> You don't account for the situation where the data has changed since the
> user initially retrieved the data. <snip>
there is no user access to the database accept via our app server. users can
go for lunch as often as they like, data will never be left in an uncommitte
d
state other than during stored procedure execution. the usage of ROWLOCK
hopefully avoids the situation you describe since it is only used during wel
l
defined units of execution.
> One way around this problem is to pass the old and new values to the store
d
> procedure. The WHERE clause would use the primary key and it would compar
e
> the @.old params to the data that is in the table. If @.@.rowcount = 0 the
> data was different and the update did not happen.
i am intentionally taking a 'pessimistic concurrency' approach here
> Can't you validate data within the GUI? <snip>
the validation involves selects and inserts into other tables (auditing,
etc) and relates to the requirements of downstream applications rather than
business rules within our own application. it is therefore not appropriate t
o
perform this validation within our UI or app server.
> Heck, I don't even see why you need a transaction in this
> stored procedure. It does not seem to buy you anything.
the only reason that the validation takes place within a transaction is so
that the ROWLOCK is held and i can guarantee that the data has not changed
between the beginning of the validation and the ultimate commit of this data
to the database.
kh|||> users can go for lunch as often as they like
That sounds great. I would like to take 3 or 4 lunches per day!
> the only reason that the validation takes place within a transaction is so
> that the ROWLOCK is held and i can guarantee that the data has not changed
> between the beginning of the validation and the ultimate commit of this
> data
> to the database.
That sounds reasonable. You know your system better than any of us. I
guess you are taking the correct approach.
Keith Kratochvil|||cheers keith. so in summary:
- i know my app better than you
- you know sql server better than me
- my users will shortly need a strict exercise regime
kh
Showing posts with label locking. Show all posts
Showing posts with label locking. Show all posts
Friday, March 30, 2012
Rowlock appears to be locking entire table.
Without going into a lot of history, I am using embedded SQL in a Micro-Focus
COBOL application.
I have a need to be able to select a specific row from my table and lock that row until I release it.
I have successfully used a declare cursor with a rowlock hint that does lock that row, unfortunately
it also locks the entire table.
I know that SQL Server is best utilized by allowing it it to manage locks, but this is a unique situation
and all I want is a clear answer as to how this should work when I have to do it.
here is a sample of what I have tried. I'm hoping someone has successfully done something
like this (maybe not even in cobol).
EXEC SQL DECLARE TECHLK CURSOR FOR
select
TECH_ID
from TECH_REC
with (rowlock,nowait)
where (TECH_ID = :SQL-TECH-ID)
END-EXEC.
EXEC SQL
OPEN TECHLK
END-EXEC.
EXEC SQL
FETCH TECHLK INTO
:SQL-TECH-ID
END-EXEC.
any help would be appreciated.. thx, Ray Dodson
1. How did you identify that entire table is locked?
2. What queries does your application actually sent to the server? You can figure that out using Profile tool.
3. SQL Server maintain record lock granularity untill it has enough resources for that. Otherwise lock granularity escalated to the table level.
|||This reply asked more questions than it answered.
1. I used the management item in seql server enterprise manager to see the
locking status.
Actually, I found after this post was sent that my keys were not set up correctly (imported
from another database). that appears to be why I was locking the entire table. I have rectified that
and things seem to be working correctly now.
thanks for you time... Ray Dodson
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?
> >> >
> >
Row-Level Locking
How do I do row-level locking on SQL Server?
Thanks,
NidNid (kpnid@.purdue.edu) writes:
> How do I do row-level locking on SQL Server?
The question is to unspecific to get a good answer. If all you want
is to be sure that table accesses in general uses row-locking, be
sure that you have indexes on the columns included in the WHERE clause.
If you say:
UPDATE tbl SET p = 0 WHERE col = 12
and there is no index on tbl.col, then this query may lock the entire
table until the transaction commits. If there is an index on col, SQL
Server can find the row directly, and only needs to lock the row.
If you mean something else, please be more specific.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hello Erland,
First of all, thank you for the response. I have to confess that I know just
a little bit about SQL Server.
Anyway, I have a ColdFusion application retrieving a record from a table
called ASSIGNMENT. Currently multiple users can update the same assignment
at the same time (which is really bad) although I used
SELECT * FROM ASSIGNMENT WITH (ROWLOCK)
WHERE Assignment_ID = <value
Assignment_ID is an identity number and it's the primary key of this table.
I think SQL Server automatically created an index for this guy since it's
the primary key.
I'm looking for anything like MS Access form which an editing row is locked
automatically. I don't want to do the lock via the application, but the
table itself if there is a way (I hope).
I found some sites talking about EXEC sp_tableoption '<table name>', 'insert
row lock', 'true'. I guess it's the same thing I'm looking for, but it
works on 6.5, not 2000. :(
I hope I don't confuse you.
Nid
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns956013A5AC5CYazorman@.127.0.0.1...
> Nid (kpnid@.purdue.edu) writes:
> > How do I do row-level locking on SQL Server?
> The question is to unspecific to get a good answer. If all you want
> is to be sure that table accesses in general uses row-locking, be
> sure that you have indexes on the columns included in the WHERE clause.
> If you say:
> UPDATE tbl SET p = 0 WHERE col = 12
> and there is no index on tbl.col, then this query may lock the entire
> table until the transaction commits. If there is an index on col, SQL
> Server can find the row directly, and only needs to lock the row.
> If you mean something else, please be more specific.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Nid,
It's not a good idea to attempt to hold row locks like this for a long
period of time. It can cause blocking of other types of operations and will
help to ruin scalability of a system.
Instead, you're going to have to create your own locking scheme. This might
involve a column on your table indicating whether a row is locked (or by
whom), a column indicating when it was locked, and a job to expire locks on
a regular basis. You might also want to consider a TIMESTAMP column. This
will allow you to still do updates even if the lock has expired, if no other
users have updated the row in question. You can send the timestamp back to
the application and before the app writes the new data, it can check to make
sure the timestamp is the same.
Finally, you should consider using an INSTEAD OF trigger for UPDATEs to the
table, to ensure that every app follows the locking rules, so that you won't
have to duplicate the code in many stored procedures or applications.
"Nid" <kpnid@.purdue.edu> wrote in message
news:chqna8$s9j$1@.mozo.cc.purdue.edu...
> I'm looking for anything like MS Access form which an editing row is
locked
> automatically. I don't want to do the lock via the application, but the
> table itself if there is a way (I hope).|||Thanks Adam, I like you idea. I sort of thinking about having my own
locking system, but didn't have time to think about the design yet and your
suggestion is so great!
I still want to know whether or not we can set the row-level locking in SQL
Server 2000 though. If so, can anyone tell me how to do it and can we set
the locking time?
Thanks,
Nid
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:4140eef0$0$6909$61fed72c@.news.rcn.com...
> Nid,
> It's not a good idea to attempt to hold row locks like this for a long
> period of time. It can cause blocking of other types of operations and
will
> help to ruin scalability of a system.
> Instead, you're going to have to create your own locking scheme. This
might
> involve a column on your table indicating whether a row is locked (or by
> whom), a column indicating when it was locked, and a job to expire locks
on
> a regular basis. You might also want to consider a TIMESTAMP column.
This
> will allow you to still do updates even if the lock has expired, if no
other
> users have updated the row in question. You can send the timestamp back
to
> the application and before the app writes the new data, it can check to
make
> sure the timestamp is the same.
> Finally, you should consider using an INSTEAD OF trigger for UPDATEs to
the
> table, to ensure that every app follows the locking rules, so that you
won't
> have to duplicate the code in many stored procedures or applications.
>
> "Nid" <kpnid@.purdue.edu> wrote in message
> news:chqna8$s9j$1@.mozo.cc.purdue.edu...
> > I'm looking for anything like MS Access form which an editing row is
> locked
> > automatically. I don't want to do the lock via the application, but the
> > table itself if there is a way (I hope).|||"N" <kpnid@.purdue.edu> wrote in message
news:chr4p8$5f1$1@.mozo.cc.purdue.edu...
> Thanks Adam, I like you idea. I sort of thinking about having my own
> locking system, but didn't have time to think about the design yet and
your
> suggestion is so great!
> I still want to know whether or not we can set the row-level locking in
SQL
> Server 2000 though. If so, can anyone tell me how to do it and can we set
> the locking time?
It is done automatically. There's nothing to set.
You can provide lockhints on your selects though.
Not sure what you mean by "locking time". Once you lock a row, it's locked
until you release it.
Seriously though, the method you want to go with I don't think is good.|||Greg, obviously it's not done automatically (at least not through my CF
appl.). That's why I asked for help. ^_^
SELECT * FROM ASSIGNMENT WITH (ROWLOCK) WHERE Assignment_ID = <value> <--
Is this the lockhints you are referring to? If yes, I did try but it didn't
work.
About the locking time, I wonder if we can lock a record when it's open, can
we set to lock it for 10 minutes, something like that?
What you said about automatically release, I guess (again I guess) it would
work for exclusive lock (UPDATE/INSERT/DELETE), but not shared lock
(SELECT).
Anyway, please ignore what I will do or not do; it is good or bad idea, etc.
I just want to find out the answers to the questions below. Is there anyone
can help me? Thanks in advance!
----------------------
I still want to know whether or not we can set the row-level locking in SQL
Server 2000 though. If so, can anyone tell me how to do it and can we set
the locking time?
----------------------
Nid
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:x%80d.20522$2s.10099@.twister.nyroc.rr.com...
> "N" <kpnid@.purdue.edu> wrote in message
> news:chr4p8$5f1$1@.mozo.cc.purdue.edu...
> > Thanks Adam, I like you idea. I sort of thinking about having my own
> > locking system, but didn't have time to think about the design yet and
> your
> > suggestion is so great!
> > I still want to know whether or not we can set the row-level locking in
> SQL
> > Server 2000 though. If so, can anyone tell me how to do it and can we
set
> > the locking time?
> It is done automatically. There's nothing to set.
> You can provide lockhints on your selects though.
> Not sure what you mean by "locking time". Once you lock a row, it's
locked
> until you release it.
> Seriously though, the method you want to go with I don't think is good.|||"Nid" <kpnid@.purdue.edu> wrote in message
news:chsc5a$l4k$1@.mozo.cc.purdue.edu...
> Greg, obviously it's not done automatically (at least not through my CF
> appl.). That's why I asked for help. ^_^
It is done automatically... unless it gets escalated to a "bigger" lock,
i.e. a extant, table, etc.
> SELECT * FROM ASSIGNMENT WITH (ROWLOCK) WHERE Assignment_ID = <value>
<--
> Is this the lockhints you are referring to? If yes, I did try but it
didn't
> work.
Define didn't work. That should work.
Or better yet, include some DDL and a repro script here so folks can test
out what you're seeing.
> About the locking time, I wonder if we can lock a record when it's open,
can
> we set to lock it for 10 minutes, something like that?
No.
> What you said about automatically release, I guess (again I guess) it
would
> work for exclusive lock (UPDATE/INSERT/DELETE), but not shared lock
> (SELECT).
Ok, it sounds like the issue you're having is you don't want a shared lock?
> Anyway, please ignore what I will do or not do; it is good or bad idea,
etc.
> I just want to find out the answers to the questions below. Is there
anyone
> can help me? Thanks in advance!
> ----------------------
> I still want to know whether or not we can set the row-level locking in
SQL
> Server 2000 though. If so, can anyone tell me how to do it and can we set
> the locking time?
> ----------------------
> Nid
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message
> news:x%80d.20522$2s.10099@.twister.nyroc.rr.com...
> > "N" <kpnid@.purdue.edu> wrote in message
> > news:chr4p8$5f1$1@.mozo.cc.purdue.edu...
> > > Thanks Adam, I like you idea. I sort of thinking about having my own
> > > locking system, but didn't have time to think about the design yet and
> > your
> > > suggestion is so great!
> > > > I still want to know whether or not we can set the row-level locking
in
> > SQL
> > > Server 2000 though. If so, can anyone tell me how to do it and can we
> set
> > > the locking time?
> > > It is done automatically. There's nothing to set.
> > You can provide lockhints on your selects though.
> > Not sure what you mean by "locking time". Once you lock a row, it's
> locked
> > until you release it.
> > Seriously though, the method you want to go with I don't think is good.|||On Fri, 10 Sep 2004 09:02:14 -0500, Nid wrote:
>Greg, obviously it's not done automatically (at least not through my CF
>appl.). That's why I asked for help. ^_^
>SELECT * FROM ASSIGNMENT WITH (ROWLOCK) WHERE Assignment_ID = <value> <--
>Is this the lockhints you are referring to? If yes, I did try but it didn't
>work.
>About the locking time, I wonder if we can lock a record when it's open, can
>we set to lock it for 10 minutes, something like that?
>What you said about automatically release, I guess (again I guess) it would
>work for exclusive lock (UPDATE/INSERT/DELETE), but not shared lock
>(SELECT).
>Anyway, please ignore what I will do or not do; it is good or bad idea, etc.
>I just want to find out the answers to the questions below. Is there anyone
>can help me? Thanks in advance!
>----------------------
>I still want to know whether or not we can set the row-level locking in SQL
>Server 2000 though. If so, can anyone tell me how to do it and can we set
>the locking time?
>----------------------
>Nid
Hi Nid,
You seem to have some misunderstandings about locking.
The default behaviour of lockings is as follows:
* When reading data (SELECT), a shared lock is issued; this prevents
modification of the data but allows simultaneous reading by others. This
lock is only held during the execution of the statement - as soon as the
data is returned, the lock will be released.
* When modifying data (INSERT, UPDATE, DELETE), an exclusive lock is
issued; this prevents both modification and reading to the data. This lock
will be held for the entire duration of the transaction - the lock will
only be released after a COMMIT TRANSACTION or ROLLBACK TRANSACTION has
been processed.
I'm pretty sure that your application DOES lock the data it's reading. But
I'm also pretty sure that these locks are only present during the read
operation and released afterwards, as that is the default behaviour.
There are various settings that force a shared lock to remain active for
the entire duration of a transaction, but these should really only be used
in short running transactions, not while a row is on the screen and the
program awaits user input. Note that locks will block other connections
that request the same data!
The usual way to control concurrency when users enter data is to read the
data without locking it, wait for user input and then verify that the data
in the table is unchanged before doing the update. If it's changed,
someone else has performed an update before you did; the application
should inform the user, reread the data and allow the user to re-enter the
update. This is called "optimistic concurrency control".
A small sample, showing how an employee is read and how the update may be
performed:
SELECT @.FirstName = FirstName, @.LastName = LastName, @.Function = Function
FROM Employees
WHERE EmpID = @.EmpID
SET @.oldFirstName = @.FirstName
SET @.oldLastName = @.LastName
SET @.oldFunction = @.Function
(show data on screen; await user input)
(if user has changed any data, attempt to update)
UPDATE Employees
SET FirstName = @.FirstName, LastName = @.LastName, Function = @.Function
WHERE EmpID = @.EmpID
AND FirstName = @.oldFirstName-- Check if nobody else
AND LastName = @.oldLastName-- changed any of these
AND Function = @.oldFunction-- before we had a chance.
IF @.@.ROWCOUNT = 0
BEGIN
here comes error handling in case someone else
changed the row before we did
END
This becomes unwieldy if there's a lot of columns. Also, columns that
allow NULL values require messier code than this. An alternative is to add
one special column with the ROWVERSION (older name: TIMESTAMP) datatype.
SQL Server will automatically change the value in this column every time a
row is changed; instead of comparing all columns, a simple compare of the
current value in the ROWVERSION column to the version when the row was
read will suffice to detect if somebody else touched the row after it was
read.
You also ask about keeping a lock for a fixed time, e.g. 10 minutes. The
only way to achieve this is to issue a command that takes a lock, then use
WAITFOR to force the connection to wait for a while. But I fail to see any
use for such a construction!
USE pubs
BEGIN TRANSACTION
UPDATE authors
SET phone = ''
WHERE au_id = '172-32-1176'
-- This will take an exclusive row lock
WAITFOR DELAY '00:10:00'
-- Wait 10 minutes before proceeding
ROLLBACK TRANSACTION
-- Undo the change and release the lock
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Greg,
With all respect, I won't answer your questions because it's not my interest
to do the row-level locking like I originally planned. So, it's not a good
use of time talking about what problem I got; what didn't work, etc. ^_^
Now, I'm just curious about whether or not we can set the row-level locking
in SQL Server 2000 by using SQL Enterprise Manager. If so, how to do it?
That's all I would like to know.
By the way, you didn't have to reply to my message in the first place, but
you did because you wanted to help and I do really appreciate your time and
everyone's also. Thanks!
Nid
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:zPi0d.21427$2s.5370@.twister.nyroc.rr.com...
> "Nid" <kpnid@.purdue.edu> wrote in message
> news:chsc5a$l4k$1@.mozo.cc.purdue.edu...
> > Greg, obviously it's not done automatically (at least not through my CF
> > appl.). That's why I asked for help. ^_^
> It is done automatically... unless it gets escalated to a "bigger" lock,
> i.e. a extant, table, etc.
>
> > SELECT * FROM ASSIGNMENT WITH (ROWLOCK) WHERE Assignment_ID = <value>
> <--
> > Is this the lockhints you are referring to? If yes, I did try but it
> didn't
> > work.
> Define didn't work. That should work.
> Or better yet, include some DDL and a repro script here so folks can test
> out what you're seeing.
>
> > About the locking time, I wonder if we can lock a record when it's open,
> can
> > we set to lock it for 10 minutes, something like that?
> No.
> > What you said about automatically release, I guess (again I guess) it
> would
> > work for exclusive lock (UPDATE/INSERT/DELETE), but not shared lock
> > (SELECT).
> Ok, it sounds like the issue you're having is you don't want a shared
lock?
> > Anyway, please ignore what I will do or not do; it is good or bad idea,
> etc.
> > I just want to find out the answers to the questions below. Is there
> anyone
> > can help me? Thanks in advance!
>
> > ----------------------
> > I still want to know whether or not we can set the row-level locking in
> SQL
> > Server 2000 though. If so, can anyone tell me how to do it and can we
set
> > the locking time?
> > ----------------------
> > Nid
> > "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
> message
> > news:x%80d.20522$2s.10099@.twister.nyroc.rr.com...
> > > > "N" <kpnid@.purdue.edu> wrote in message
> > > news:chr4p8$5f1$1@.mozo.cc.purdue.edu...
> > > > Thanks Adam, I like you idea. I sort of thinking about having my
own
> > > > locking system, but didn't have time to think about the design yet
and
> > > your
> > > > suggestion is so great!
> > > > > > I still want to know whether or not we can set the row-level locking
> in
> > > SQL
> > > > Server 2000 though. If so, can anyone tell me how to do it and can
we
> > set
> > > > the locking time?
> > > > > > It is done automatically. There's nothing to set.
> > > > You can provide lockhints on your selects though.
> > > > Not sure what you mean by "locking time". Once you lock a row, it's
> > locked
> > > until you release it.
> > > > Seriously though, the method you want to go with I don't think is
good.
> >|||Hello Hugo,
This is reall helpful and clear explanation. Thanks!
Please see my response below.
> You seem to have some misunderstandings about locking.
> The default behaviour of lockings is as follows:
> * When reading data (SELECT), a shared lock is issued; this prevents
> modification of the data but allows simultaneous reading by others. This
> lock is only held during the execution of the statement - as soon as the
> data is returned, the lock will be released.
> * When modifying data (INSERT, UPDATE, DELETE), an exclusive lock is
> issued; this prevents both modification and reading to the data. This lock
> will be held for the entire duration of the transaction - the lock will
> only be released after a COMMIT TRANSACTION or ROLLBACK TRANSACTION has
> been processed.
I think my biggest problem is my English. I know what you are saying, but I
can't explain what I meant by locking! So, it's my bad that I confused
others. Please accept my apology!
> I'm pretty sure that your application DOES lock the data it's reading. But
> I'm also pretty sure that these locks are only present during the read
> operation and released afterwards, as that is the default behaviour.
It doesn't. I used different browsers to open the same record and I could
update the record on both browsers. Unfortunately, I'm not a DBA; I am a
web programmer. For SQL Server, I only know how to create tables, indexes,
constraint, relationship, transfer data, backup, and restore by using
Enterprise tool. That's pretty much it.
> There are various settings that force a shared lock to remain active for
> the entire duration of a transaction, but these should really only be used
> in short running transactions, not while a row is on the screen and the
> program awaits user input. Note that locks will block other connections
> that request the same data!
> The usual way to control concurrency when users enter data is to read the
> data without locking it, wait for user input and then verify that the data
> in the table is unchanged before doing the update. If it's changed,
> someone else has performed an update before you did; the application
> should inform the user, reread the data and allow the user to re-enter the
> update. This is called "optimistic concurrency control".
> A small sample, showing how an employee is read and how the update may be
> performed:
> SELECT @.FirstName = FirstName, @.LastName = LastName, @.Function = Function
> FROM Employees
> WHERE EmpID = @.EmpID
> SET @.oldFirstName = @.FirstName
> SET @.oldLastName = @.LastName
> SET @.oldFunction = @.Function
> (show data on screen; await user input)
> (if user has changed any data, attempt to update)
> UPDATE Employees
> SET FirstName = @.FirstName, LastName = @.LastName, Function = @.Function
> WHERE EmpID = @.EmpID
> AND FirstName = @.oldFirstName -- Check if nobody else
> AND LastName = @.oldLastName -- changed any of these
> AND Function = @.oldFunction -- before we had a chance.
> IF @.@.ROWCOUNT = 0
> BEGIN
> here comes error handling in case someone else
> changed the row before we did
> END
> This becomes unwieldy if there's a lot of columns. Also, columns that
> allow NULL values require messier code than this. An alternative is to add
> one special column with the ROWVERSION (older name: TIMESTAMP) datatype.
> SQL Server will automatically change the value in this column every time a
> row is changed; instead of comparing all columns, a simple compare of the
> current value in the ROWVERSION column to the version when the row was
> read will suffice to detect if somebody else touched the row after it was
> read.
> You also ask about keeping a lock for a fixed time, e.g. 10 minutes. The
> only way to achieve this is to issue a command that takes a lock, then use
> WAITFOR to force the connection to wait for a while. But I fail to see any
> use for such a construction!
> USE pubs
> BEGIN TRANSACTION
> UPDATE authors
> SET phone = ''
> WHERE au_id = '172-32-1176'
> -- This will take an exclusive row lock
> WAITFOR DELAY '00:10:00'
> -- Wait 10 minutes before proceeding
> ROLLBACK TRANSACTION
> -- Undo the change and release the lock
I'm going to try this when I'm more familiar with SQL Server or have more
access to the DB. (I'm not a DBA. I don't have full privilege).
For now, I go with Erland's suggestion. It's simple enough for amateur like
me and I think I have more control over it. I simply added two columns to
keep track of time open and close of a record. When user selects an
assignment, I check the time close filed, if it is not empty (someone has
this record open), I will redirect him/her to read-only screen. If there is
a release time in this field, I will write to the time open field. I have
JavaScript code to call an action page to write the time to the time close
field when user leaves the page or closes the window. I also have simple
Meta tag to refresh a page every ten minutes.
Well, the way I do might sound stupid to someone, but so far it works like I
want and I'm happy. Thanks again everyone for your information and good
intention.|||Hi Nid,
Some comments below:
On Fri, 10 Sep 2004 14:51:59 -0500, Nid wrote:
(snip)
>> I'm pretty sure that your application DOES lock the data it's reading. But
>> I'm also pretty sure that these locks are only present during the read
>> operation and released afterwards, as that is the default behaviour.
>It doesn't. I used different browsers to open the same record and I could
>update the record on both browsers.
Yes, that's the expected behaviour when you use SQL Server's standard
isolation level setting. The details of locks taken and released in this
sceario are as follows:
1. You open a browser and request to show the data of row #17. SQL Server
requests and gets a shared lock for row 17, reads the data, sends the data
to the client (your browser) and releases the lock.
2. You open a second browser and request to show row #17. SQL Server will
(again) request a shared lock for row 17; as the former lock was already
released, this lock will be given, the data is read and sent to the
browser and the lock gets released again.
3. Now you update data in one of the browsers and send it back to SQL
Server. This time, SQL Server will request an exclusive lock. Since all
previous locks have been released, SQL Server will grant you this lock and
update the data for row 17. I assume that you either begin and commit a
transaction when you perform the update or that you don't use explicit
transactions (in which case each statement is treated as a seperate
transaction) - in both cases, the transaction gets committed after the
update is processed and SQL Server will release the exclusive row lock on
row 17.
4. Finally, you change the data in the other browser and send it to the
server. And again, since all previous locks were already released, the
requested exclusive row lock will be taken, the data updated and the lock
will be released again.
If you had implemented optimistic concurrency control (as I described in
my previous message), then there would be no change to the locking, but
the last update would fail to find any rows matching the WHERE clause,
resulting in no update at all - and your application could take any action
you'd like upon seeing that no rows were affected by the update.
It is possible to change this behaviour. If you start a transaction before
reading the row and don't commit it until the update has been performed
(or cancelled by the user), AND you set the transaction isolation level to
repeatable read or serializable, then you'll see that the lock taken when
the first browser reads the row will "stick" for the rest of the
transaction. But that will probably harm your application more than it
will help, because the same scenario (reading the same row in two broweser
windows) will, depending on the locking hints used in the read operation
a) cause a deadlock that will result in the abortion of one of the
processen by SQL Server, or
b) cause a blocking situation where the second browser will not show the
data requested until the dialog in the first window is completely
finished.
(snip)
>I'm going to try this when I'm more familiar with SQL Server or have more
>access to the DB. (I'm not a DBA. I don't have full privilege).
Please ask your DBA to at least give you access to Query Analyzer. Making
tables, views etc in QA gives you much more control than EM gives you. And
QA is better for debugging - not only because it has a debugging facility
built in, but also because it doesn't try to do any of the creative stuff
with error messages that some clients seem to do.
>For now, I go with Erland's suggestion. It's simple enough for amateur like
>me and I think I have more control over it.
(snip)
>Well, the way I do might sound stupid to someone, but so far it works like I
>want and I'm happy. Thanks again everyone for your information and good
>intention.
If this way makes it work as you wish, you understand it enough to be able
to control it and it doesn't cause any errors, then there should be no
reason not to go for it.
On the other hand ... did you consider the possibility that someone might
just close the browser window, experience power loss or find some other
way to disconnect from the database in a dramatic and unexpected way? You
should have some way to clean up the open and close time of the affected
rows after such a scenario!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Nid (kpnid@.purdue.edu) writes:
>> I'm pretty sure that your application DOES lock the data it's reading.
>> But I'm also pretty sure that these locks are only present during the
>> read operation and released afterwards, as that is the default
>> behaviour.
> It doesn't. I used different browsers to open the same record and I
> could update the record on both browsers. Unfortunately, I'm not a DBA;
> I am a web programmer. For SQL Server, I only know how to create
> tables, indexes, constraint, relationship, transfer data, backup, and
> restore by using Enterprise tool. That's pretty much it.
This is because if you have not started a transaction, the default
behaviour is auto-commit, which means that once the update has been
performed, the update is committed.
This has the effect that if two users get the same record from the
database, and each performs an update, the last update may overwrite
the first.
This is a little different from ANSI, which mandates implicit transactions.
That is, as soon as you submit an UPDATE, INSERT or DELETE, a new
transaction starts, and you must explicitly commits. SQL Server is able
to perform in this way, if you issue the command SET IMPLICIT_TRANSACTIONS
ON (which has to be done per connection).
This does not really change things, though, since you have committed the
first user, the second user can still overwrite the first update.
> For now, I go with Erland's suggestion. It's simple enough for amateur
> like me and I think I have more control over it. I simply added two
> columns to keep track of time open and close of a record. When user
> selects an assignment, I check the time close filed, if it is not empty
> (someone has this record open), I will redirect him/her to read-only
> screen. If there is a release time in this field, I will write to the
> time open field.
This sounds like a much better solution. However, the honour for the
idea goes to Adam, not me.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Nid" <kpnid@.purdue.edu> wrote in message
news:chsuei$63$1@.mozo.cc.purdue.edu...
> Greg,
>
> With all respect, I won't answer your questions because it's not my
interest
> to do the row-level locking like I originally planned. So, it's not a
good
> use of time talking about what problem I got; what didn't work, etc. ^_^
Ok, after reading your other posts, I understand what the problem is.
SQL Server IS doing row-level locking. For the length of your select, etc.
Just as it's designed.
However, you want application level locking which is a completely different
beast. Now that I understand what you want, I can only answer below...
>
>
> Now, I'm just curious about whether or not we can set the row-level
locking
> in SQL Server 2000 by using SQL Enterprise Manager. If so, how to do it?
> That's all I would like to know.
You can NOT do what you want to (which is essentially application locking)
within SQL Server. You have to design your schema and application to lock
rows using some sort of semaphore which the application can check and modify
as appropriate.
One method is to have a timefield which gets checked by the calling
application. If NULL, it updates teh timefield with the current time, does
what you want and when done updates the timefield with a NULL value again.
Meanwhile if the application running on another computer selects the same
row and sees the time is NOT NULL, it then goes on to a different row.
You can handle "timeouts" one of two ways. The calling application can check
to see if the time in the timefield is more than X minutes old and if so,
treat it the same as NULL. I would not do it this way. (btw, if you do, make
sure all the time comparisions are done on the server side using the server
time, otherwise a client X minutes out of synch will cause problems.)
Otherwise, I might have a scheduled task on the server running every X
minutes looking for rows that have not been set to NULL. It would check if
the calling app still had an open connection. If so, it may leave it,
otherwise it sets it to NULL.
In any case, what you want can not be done automatically by SQL Server. In
SQL Server terms, the locks last the length of the transaction.
So a "select * from foo where ID='bar'" the shared locks just the length of
time it takes for SQL Server to retrieve the data. This is why you can
update the row from either client. So basically, to sum up and reiterate,
you'll have to roll your own.
>
> By the way, you didn't have to reply to my message in the first place, but
> you did because you wanted to help and I do really appreciate your time
and
> everyone's also. Thanks!
Well, none of us HAVE to, but we do like to. Helps keep us on our toes to.
Hope this helps and good luck.|||Nid (kpnid@.purdue.edu) writes:
> For now, I go with Erland's suggestion. It's simple enough for amateur
> like me and I think I have more control over it. I simply added two
> columns to keep track of time open and close of a record. When user
> selects an assignment, I check the time close filed, if it is not empty
> (someone has this record open), I will redirect him/her to read-only
> screen. If there is a release time in this field, I will write to the
> time open field. I have JavaScript code to call an action page to write
> the time to the time close field when user leaves the page or closes the
> window. I also have simple Meta tag to refresh a page every ten
> minutes.
There might still be problems if the server shuts down expectedly or
whatever reason that causes the user to lose connection without the
Javascript code to fire. This can be handled with some logic that does
deems a lock as stale - for instance a lock being more than 30 minutes
old.
Yet an alternative, and closer to your original approach is to use
application locks. Application locks are handled by the SQL Server
Engine, but you are the one who define the locked resources. The main
advantage with application locks is that they will go away when something
disconnects.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9561F1BB0E05AYazorman@.127.0.0.1...
> There might still be problems if the server shuts down expectedly or
> whatever reason that causes the user to lose connection without the
> Javascript code to fire. This can be handled with some logic that does
> deems a lock as stale - for instance a lock being more than 30 minutes
> old.
Can use a SQL Server Agent job for this, as well.
> Yet an alternative, and closer to your original approach is to use
> application locks. Application locks are handled by the SQL Server
> Engine, but you are the one who define the locked resources. The main
> advantage with application locks is that they will go away when something
> disconnects.
That would be fine if it's okay to keep persistent connections; in a web
environment this would be difficult to maintain (have to keep track of
sessions and sticky servers), potentially dangerous (certain kinds of
connection objects are not well-suited for keeping in session memory), and
would ruin benefits of connection pooling. I would strongly recommend
against keeping connections open in between requests.|||Adam Machanic (amachanic@.hotmail._removetoemail_.com) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9561F1BB0E05AYazorman@.127.0.0.1...
>> Yet an alternative, and closer to your original approach is to use
>> application locks. Application locks are handled by the SQL Server
>> Engine, but you are the one who define the locked resources. The main
>> advantage with application locks is that they will go away when something
>> disconnects.
> That would be fine if it's okay to keep persistent connections; in a
> web environment this would be difficult to maintain (have to keep track
> of sessions and sticky servers), potentially dangerous (certain kinds of
> connection objects are not well-suited for keeping in session memory),
> and would ruin benefits of connection pooling. I would strongly
> recommend against keeping connections open in between requests.
Good point.
It goes without saying the original intended scheme by locking rows through
SQL Server is also a failure in this context.
Application lock is more like to be a solution for a non-web application
where you cannot accept pessimistic locking. But I will have to admit that
I would think twice before I took that route.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Subscribe to:
Posts (Atom)