How to lock rows, but read other rows? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to lock rows, but read other rows?

Author

Topic

snakyjake
Starting Member

1 Post

Posted - 2009-11-04 : 16:42:38

GOALS:

Select a record from a table and hold a lock onto the record until transaction is complete.
No other query should be able to read the record.
Other queries can read the records that are NOT being selected/locked.

EXAMPLE:

-- SPID A
If Exists (Select * From TempDb.Sys.Tables Where Name Like '%T1') Drop Table ##T1

Create Table ##T1 (C1 int)

Insert into ##T1 (C1) Values(1);
Insert into ##T1 (C1) Values(2);
Insert into ##T1 (C1) Values(3);
Insert into ##T1 (C1) Values(4);
Insert into ##T1 (C1) Values(5);

Begin Transaction

Select *
--From ##T1 WITH (HOLDLOCK, ROWLOCK) -- Tried, but doesn't seem to lock.
From ##T1 WITH (HOLDLOCK, ROWLOCK, XLOCK) -- Tried, but can't read table.
Where C1 = 2

--Rollback

-- SPID B

/*
This query should return all records that are not locked.
However, the query returns all records, even "locked" records.
*/

Select *
From ##T1
--From ##T1 WITH (READPAST) -- Tried

Thanks,

Jake

Pradip
Starting Member

32 Posts

Posted - 2009-11-05 : 03:22:41

I think allowing view with filter those locked row and allowing access from view only is one of the solution.

pradipjain

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-11-05 : 05:31:52

The problem is XLOCK locks at least a page and the table will only take up one page.

This seems a strange requirement. If you tell us what you are really trying to do, someone may to able to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources