help with locking

Hi,
I have an application that calls a strored procedure that return a record
built from several tables joined.
This data is then present to the user in application where they can then
edit and save.
The problem I have is that the as the user changes different bits of data,
it will be updating the table that holds that specific data. If the user
changes all the data, it would update several SQL tables.
I want to be able to put all this into a transaction, so that if their is a
problem, I can just rollback the transaction, and all the data put in
numerous different tables will be rolled back.
The problem is that I want to lock a couple of the records in a few of the
tables at the point the stored procedure does the select.
An example would be that if one user is working in record_id 92, then nobody
else can load that single row at the same time.
Whilst a user is editing a record in the application, with that record
locked, i want all the other users to be able to select records from the
table.
What I am seeing is if I force a lock, such as an xLock, then the entire
table is locked, selects are suspended until i either commit or rollback my
transaction. (the select with the xlock clause is executed with a sql
transaction)
Hope that makes sense, and somebody is able to help.
Using a timestamp, and comparing before updating is not really an option as
there are many table underneath that would all have to be timestamp checked,
and that would seem a poor way of doing this.
ThanksCan you specify ROWLOCK instead of XLOCK in your SELECT statement?
I am not sure whether this is what you want.
Leo Leong
"Aussie Rules" wrote:

> Hi,
> I have an application that calls a strored procedure that return a record
> built from several tables joined.
> This data is then present to the user in application where they can then
> edit and save.
> The problem I have is that the as the user changes different bits of data,
> it will be updating the table that holds that specific data. If the user
> changes all the data, it would update several SQL tables.
> I want to be able to put all this into a transaction, so that if their is
a
> problem, I can just rollback the transaction, and all the data put in
> numerous different tables will be rolled back.
> The problem is that I want to lock a couple of the records in a few of the
> tables at the point the stored procedure does the select.
> An example would be that if one user is working in record_id 92, then nobo
dy
> else can load that single row at the same time.
> Whilst a user is editing a record in the application, with that record
> locked, i want all the other users to be able to select records from the
> table.
> What I am seeing is if I force a lock, such as an xLock, then the entire
> table is locked, selects are suspended until i either commit or rollback m
y
> transaction. (the select with the xlock clause is executed with a sql
> transaction)
> Hope that makes sense, and somebody is able to help.
> Using a timestamp, and comparing before updating is not really an option a
s
> there are many table underneath that would all have to be timestamp checke
d,
> and that would seem a poor way of doing this.
>
> Thanks
>
>|||On Fri, 3 Jun 2005 06:37:13 +0100, Aussie Rules wrote:
(snip)
>Whilst a user is editing a record in the application, with that record
>locked, i want all the other users to be able to select records from the
>table.
>What I am seeing is if I force a lock, such as an xLock, then the entire
>table is locked, selects are suspended until i either commit or rollback my
>transaction. (the select with the xlock clause is executed with a sql
>transaction)
>Hope that makes sense, and somebody is able to help.
Hi Aussie,
How did you conclude that the entire table is locked? Default behaviour
for SQL Server is to use row-level locks, unless such a large proportion
of the rows in the table gets locked that promoting to a table-level
lock makes more sense. For single-row updates or few-row updates, that
should not happen.
However, you should be aware that a locked row won't be skipped if it
has to be read. And some queries have to read more rows than they'll
eventually return.
Take this query for example:
SELECT COUNT(*)
FROM Personnel
WHERE Sex = 'M'
If the sex column is indexed and the optimizer decides to use that index
to evaluate this query, then this query will return data quickly, even
if ALL rows of female personnel are currently locked. However, if there
is no index on sex (or if the optimizer chooses a plan that bypasses
this index), then a lock on even one of the female employees' rows will
effectively block this query - the database has to read the row in order
to check the WHERE condition, so it will wait until the lock is
released, then fetch the row, check the sex column and discard it from
the results.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 3 Jun 2005 06:37:13 +0100, "Aussie Rules"
<someone@.somewhere.com> wrote:
>What I am seeing is if I force a lock, such as an xLock, then the entire
>table is locked, selects are suspended until i either commit or rollback my
>transaction. (the select with the xlock clause is executed with a sql
>transaction)
Try an updlock, locks just the row(s).
But watch out for what Hugo says.

>Using a timestamp, and comparing before updating is not really an option as
>there are many table underneath that would all have to be timestamp checked
,
>and that would seem a poor way of doing this.
This should not really be a problem in most data models. Your app
should be structured enough that tables are always accessed in a
particular order. If they all do the appropriate (pessimistic)
locking, then they will see each others' locks at the parent table
levels, and in general all will be well.
I presume you are doing rich/smart or two-tier client/server
development, it's pretty much impossible to do true pessimistic
locking for stateless (!) web apps.
I have built entire apps against SQLServer using pessimistic locking
as you describe, it can be done, though it's terribly unpopular these
days, and BOL even recommends against it!
Josh