Row level lock in SQL Server 2005

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)

Row level lock in SQL Server 2005

Author

Topic

Balage_Baju
Starting Member

8 Posts

Posted - 2009-06-10 : 05:09:52

Hi,

I would like to know how to lock a particular row of a table being read by more than one user at a time.

For example, I have a table called "Emp" with the following data:

EmpID EmpName Salary
1 AAA 1000
2 BBB 2000
3 CCC 3000
4 DDD 4000

If i write a "SELECT" query against "Emp" table like:
BEGIN TRAN
SELECT * FROM Emp WHERE EmpID = 2
< some SQL Statements..............>
COMMIT TRAN

When i run the above query, other than me no body else should be able to access(read/write/update) the row where EmpID = 2 till SQL Server commits this transaction.

Basically, i want to prevent the users reading the same row from a table at the same time.

Any help is appreciated...

Thanks,
Balaji.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 05:11:03

You can use transaction level snapshot

E 12°55'05.63"
N 56°04'39.26"

Balage_Baju
Starting Member

8 Posts

Posted - 2009-06-10 : 05:16:00

Thanks for the quick reply, i will check and get back to you...

Thanks,
Balaji.

Balage_Baju
Starting Member

8 Posts

Posted - 2009-06-10 : 07:08:28

Hi,
I tried with "Snapshot" option, but it does not help me. Everybody is able to fetch the same record when the Transaction is running.. Am not able to give the exclusive read lock.

Please provide your inputs.

Thanks,
Balaji.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 07:12:49

You can't force other users to not being able to read the record.
They can always use WITH (NOLOCK) query hint.

You transaction will however force them to wait their UPDATE.

Have a check at SERIALIZABLE transaction level.

E 12°55'05.63"
N 56°04'39.26"

Balage_Baju
Starting Member

8 Posts

Posted - 2009-06-11 : 08:29:05

Hi,
We tried with "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE" and still any no. of users are able to read the same record from the table.

Here is our Business requirement:

We have a requirement to maintain the state history of an object which tells how much time an object was in a particular state. For every state change, previous state's row will be updated with end time and a new row is inserted with new state change time.

Due to the business requirement, multiple threads are monitoring the table to insert state change information and some times two threads are inserting same state transition information into the table. Querying the latest state to determine the next state transition is not working in this case as it is possible to query the state by multiple threads at the same time and more than one thread is inserting same transition record into the table. As a result more than one record is inserted with the same state into the table for an object.

We would like to know, is it possible to lock a row for read itself at the table level so that only one thread can read at a time and complete the next state transition and releases the read lock after the state transition of an object.

Thanks,
Balaji.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-11 : 08:42:03

So you've got some application checking up on the status of a piece of data and if it has changed do something?????

why not just use a trigger instead? No more hassle with multiple threads.

Or are you trying to keep track of READS? as well as updates / deletes?

Not really sure what you are trying to achieve here. Probably got the wrong end of the stick.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources