SQL Server row lock
-
How to row lock in SQL Server 2005. I execute a sql for row locking and that is SELECT * FROM authors WITH (HOLDLOCK, ROWLOCK) WHERE au_id = '274-80-9391' it work fine but in this case row is lock for update not for selection. I just want to know how to lock a row as a result another user can not see that row when issue a SQL in SQL Server. please guide me. thanks
tbhattacharjee
-
How to row lock in SQL Server 2005. I execute a sql for row locking and that is SELECT * FROM authors WITH (HOLDLOCK, ROWLOCK) WHERE au_id = '274-80-9391' it work fine but in this case row is lock for update not for selection. I just want to know how to lock a row as a result another user can not see that row when issue a SQL in SQL Server. please guide me. thanks
tbhattacharjee
Bad news there, I'm afraid. You can't rowlock an row for a select in SQL Server 2005 so that others can't see it. Even the xlock hint won't cut it for you because it works as a page lock rather than a row lock in SQL Server 2005 and, if SQL Server determines that the underlying rows haven't changed, it will allow the read of the data.
I'm not a stalker, I just know things. Oh by the way, you're out of milk.
Forgive your enemies - it messes with their heads
-
How to row lock in SQL Server 2005. I execute a sql for row locking and that is SELECT * FROM authors WITH (HOLDLOCK, ROWLOCK) WHERE au_id = '274-80-9391' it work fine but in this case row is lock for update not for selection. I just want to know how to lock a row as a result another user can not see that row when issue a SQL in SQL Server. please guide me. thanks
tbhattacharjee
If you are using SQL Server, you may want to investigate implementing your own locking logic by using sp_applock. Check this out ... http://msdn.microsoft.com/en-us/library/aa933410(SQL.80).aspx[^]