June 10, 2009 at 3:04 am
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...:-)
June 10, 2009 at 6:15 am
I have seen such read locking handled in an application with a separate lock table, where all access to the table goes through the application - the application keeps track of which rows are locked - not SQL Server. This way the application can warn users if another lock exists.
But if your transaction is not updating the record - why do you want to lock it?
I suppose it could be possible - but to lock a row exclusively (so another user cannot read it) you have to attempt to update it within your transaction. (you could SET a column to its existing value in an UPDATE - but I'm not sure if SQL Server would be fooled by this - you would have to experiment)
And you need a TRANSACTION ISOLATION LEVEL that will prevent reading altered data.
June 10, 2009 at 6:51 am
The very nature of SQL Server is try to make it possible for lots of people to read the same data at the same time. Reading data, not updating/deleting it, is not usually considered an exclusive act.
You could try using the SERIALIZABLE table hint in the query. I'm just not sure that another query using the NOLOCK hint couldn't get past you.
If you're controlling all access & querying to the tables, you could use a lock table as mentioned earlier or update values inside the table to show that a particular row, or rows, is in use and then filter by joining to the lock table or querying against the lock column. However, either approach could be a bit of a performance bottleneck.
Why do you need to do this? What business requirement are you trying to meet?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 10, 2009 at 1:15 pm
This should do:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM Categories with (ROWLOCK) WHERE CategoryID = 1
...
Anyone trying to select this row will wait your transaction finish. Select * on this table will also wait.
DBA Cabuloso
________________
DBA Cabuloso
Lucas Benevides
June 11, 2009 at 6:17 am
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.
June 11, 2009 at 6:28 am
You're going to have to implement a code solution, not try to control locking. Here's a rough outline of an approach. You add two columns, call them InUse and UserID or something. Then, don't try to read the data, try to update the row to change InUse from 0 to 1. If it's already 1, you can't do anything and the query returns, either an error or no data, whatever you determine is right in the situation. If you can update the value, you also, at the same time, generate a uniqueid and update UserID. Return the uniqueID to the reader. Until they're done with it, it's now locked. When they're done, do another update, setting InUse to 0.
As long as you can absolutely control access through stored procedures, something along these lines will work, although you're going to see quite a lot of contention. If ad hoc reads are possible, this won't work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 12, 2009 at 6:03 am
Hi,
We hope your suggestion will help to us to resolve the issue. We will try to implement and update you.
Thanks,
Balaji.
December 3, 2010 at 11:54 pm
The above method is used in all my projects. But i want to know what happens in the following situation.
if [USER-A] reads the record checks if it is in use, if not he writes the in-use field. Simultaneously if [USER-B] reads the record before [USER-A] update the field in-use. Now [USER-B] will also write to the field in-use, because the time taken to read/check/write is going to take time. In a distributed net work this time is a factor. how to over come this.
Is their a way to lock a record at the time read for some time till a update to the in-use field takes place.
Thanks 🙂
December 4, 2010 at 6:44 am
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.
It appears that you have two business rules:
a) The object's state history cannot have two rows with the same state.
b) For the object's state history, the expiration date of a state is the effective date of the subsequent row.
It appears that you are attempting to implement these two business rules in a process that is not part of the database.
As you have discovered, the database has no capability prevent a row from being read by any other connection except by updating the row and insuring that all processes use the read committed isolation level.
Suggest you have the rules enforced by the database by:
For the first rule, create a uniqueness constraint.
For the second rule, use triggers to maintain the derivable value of the expiration date. The triggers should include "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;" and also should NOT have any variables.
The process should then just attempt an insert into the object's state history and then perform appropriate error handling. You may have a processing rule that uniqueness constraint violations are ignored.
SQL = Scarcely Qualifies as a Language
December 5, 2010 at 9:01 pm
Hi,
Thanks for the replay, but i am not clear to the point, can you give a code example how to do it.
have a nice day
May 11, 2012 at 4:01 pm
I know this is an old thread, but, here it goes.
Same predicament here. In my case, I have a table that holds keys (seeds) to other tables. Because it holds seeds to several other tables, it would be nice to not have to lock the entire table, only that specific row that contains the specific seed to the table I am about to insert to, so other users could not read that specific row and get a value that I am using, while still being able to access other rows containing seeds to other tables. Since this is a LIVE production DB that servers a third party ERP system, modifying the tables and how they accomplish their table seeds is not an option. I ended up having to lock the entire table. Not the end of the world since my code will be finished in milliseconds, but again, it would be nice to be able to lock only that specific row from being read.
Cheers, Raphael
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply