"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 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.
>
> V
>

"Tibor Karaszi" wrote:

> Make sure you have an index on the column you use in the WHERE clause. Otherwise, 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...
> > 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.
> >
> > V
> >
>
>