(rowlock xlock) Lock hinnts

Discussion:

(too old to reply)

Sai

2003-12-12 17:10:50 UTC

I want to have an exclusive lock at row level for a sql
statement in a transaction,to accomplish this

I used (rowlock xlock) lock hint on SELECT
statement,surprisingly it is not working for some of the
tables

is there any restriction using this hint??

Here is an example to reproduce the above probelm

1) LOCK hint is working fine with this table "employee"
from PUBS

QA --Query Analyzer

a)QA 1
begin tran
select * from employee(rowlock xlock) where emp_id = 'F-
C16315M'

b)QA 2
select * from employee where emp_id = 'F-C16315M'

So QA2 will not get data as it was expected because of the
LOCK hint from QA1,so this works
fine.

2) Lock hint Not working with this table "authors" from
PUBS

a)QA 1
begin tran
select * from authors(rowlock xlock) where emp_id = 'F-
C16315M'

b)QA 2
select * from authors where emp_id = 'F-C16315M'

Here I am able to get that data in QA2 ,so lock hint is
not working

Any one has idea????

Thanks
Sai

Louis Davidson

2003-12-12 20:57:37 UTC

Permalink

I don't seem to have an emp_id in my authors table. One point though, you
might try separating your hints with commas.
--
----------------------------------------------------------------------------
-----------
Louis Davidson (***@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

Post by Sai
I want to have an exclusive lock at row level for a sql
statement in a transaction,to accomplish this
I used (rowlock xlock) lock hint on SELECT
statement,surprisingly it is not working for some of the
tables
is there any restriction using this hint??
Here is an example to reproduce the above probelm
1) LOCK hint is working fine with this table "employee"
from PUBS
QA --Query Analyzer
a)QA 1
begin tran
select * from employee(rowlock xlock) where emp_id = 'F-
C16315M'
b)QA 2
select * from employee where emp_id = 'F-C16315M'
So QA2 will not get data as it was expected because of the
LOCK hint from QA1,so this works
fine.
2) Lock hint Not working with this table "authors" from
PUBS
a)QA 1
begin tran
select * from authors(rowlock xlock) where emp_id = 'F-
C16315M'
b)QA 2
select * from authors where emp_id = 'F-C16315M'
Here I am able to get that data in QA2 ,so lock hint is
not working
Any one has idea????
Thanks
Sai

Sai

2003-12-13 18:03:11 UTC

Permalink

Hi
Louis
Thanks for your reply and sorry for provising
the wrong querry,here is the right one

) LOCK hint is working fine with this table "employee"
from PUBS

QA --Query Analyzer

a)QA 1
begin tran
select * from employee(rowlock xlock) where emp_id = 'F-
C16315M'

b)QA 2
select * from employee where emp_id = 'F-C16315M'

So QA2 will not get data as it was expected because of the
LOCK hint from QA1,so this works
fine.

2) Lock hint Not working with this table "authors" from
PUBS

a)QA 1
begin tran
select * from authors(rowlock xlock) where au_id = '213-46-
8915'

b)QA 2
select * from authors where au_id = '213-46-8915'

I tried separating hnts with commas,no difference

thanks for help
sai

-----Original Message-----
I don't seem to have an emp_id in my authors table. One

point though, you

might try separating your hints with commas.
--
----------------------------------------------------------

------------------

-----------
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies

will be ignored :)

the

Post by Sai
LOCK hint from QA1,so this works
fine.
2) Lock hint Not working with this table "authors" from
PUBS
a)QA 1
begin tran
select * from authors(rowlock xlock) where emp_id = 'F-
C16315M'
b)QA 2
select * from authors where emp_id = 'F-C16315M'
Here I am able to get that data in QA2 ,so lock hint is
not working
Any one has idea????
Thanks
Sai

Tibor Karaszi

2003-12-13 18:15:46 UTC

Permalink

Unfortunately (IMO), SQL Server optimizes away this log in some situations.
I don't recall the details, but if you search the archives and the net, I'm
sure you'll find more info.

Can you use UPDLOCK (you need in both).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver

Post by Sai
Hi
Louis
Thanks for your reply and sorry for provising
the wrong querry,here is the right one
) LOCK hint is working fine with this table "employee"
from PUBS
QA --Query Analyzer
a)QA 1
begin tran
select * from employee(rowlock xlock) where emp_id = 'F-
C16315M'
b)QA 2
select * from employee where emp_id = 'F-C16315M'
So QA2 will not get data as it was expected because of the
LOCK hint from QA1,so this works
fine.
2) Lock hint Not working with this table "authors" from
PUBS
a)QA 1
begin tran
select * from authors(rowlock xlock) where au_id = '213-46-
8915'
b)QA 2
select * from authors where au_id = '213-46-8915'
I tried separating hnts with commas,no difference
thanks for help
sai

-----Original Message-----
I don't seem to have an emp_id in my authors table. One

point though, you

might try separating your hints with commas.
--
----------------------------------------------------------

------------------

will be ignored :)

the