Row Level Locking

Hello,
I have been trying to figure out how to user Row Level locking feature in
SQL Server 2005. Here is the scenario.
1.Open a new query window in SQL Server Management sudio.
2.Execute a Begin transaction and an Update statement that updates a row in
a table. Do not commit the transaction yet.
3. Open another query window
4. Execute a Begin transaction and an Update statement that updates a
different row in the same table. Do not commit the transaction yet.
The secod session does not complete execution and waits because the table is
locked. I verified that by checking the locks in the database. I was
expecting just the first row to be locked and not the entire table. Once I
commit the first transaction, the second session completes execution of the
update statement.
How do I make it to lock only the row and not the table. I did try using
ROWLOCK hint as part of te UPDATE statement. It did not help. I had turned
off the Page Level locks for the index in the table.
VMake sure you have an index on the column you use in the WHERE clause. Other
wise, SQL Server has to
look at each row to see whether the row satisfies the WHERE condition (and t
he other connection has
one row with excusive lock).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"V" <V@.discussions.microsoft.com> wrote in message
news:1631B36D-FC9E-4B3B-A1ED-7586261C74B1@.microsoft.com...
> Hello,
> I have been trying to figure out how to user Row Level locking feature in
> SQL Server 2005. Here is the scenario.
> 1.Open a new query window in SQL Server Management sudio.
> 2.Execute a Begin transaction and an Update statement that updates a row
in
> a table. Do not commit the transaction yet.
> 3. Open another query window
> 4. Execute a Begin transaction and an Update statement that updates a
> different row in the same table. Do not commit the transaction yet.
> The secod session does not complete execution and waits because the table
is
> locked. I verified that by checking the locks in the database. I was
> expecting just the first row to be locked and not the entire table. Once I
> commit the first transaction, the second session completes execution of th
e
> update statement.
> How do I make it to lock only the row and not the table. I did try using
> ROWLOCK hint as part of te UPDATE statement. It did not help. I had turned
> off the Page Level locks for the index in the table.
> V
>|||Thanks, That helped. After creating an index on the column used in where
clause, it worked.
"Tibor Karaszi" wrote:

> Make sure you have an index on the column you use in the WHERE clause. Oth
erwise, SQL Server has to
> look at each row to see whether the row satisfies the WHERE condition (and
the other connection has
> one row with excusive lock).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "V" <V@.discussions.microsoft.com> wrote in message
> news:1631B36D-FC9E-4B3B-A1ED-7586261C74B1@.microsoft.com...
>