Row locking problem - SQL Server Forums

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 locking problem

Author

Topic

torust
Starting Member

5 Posts

Posted - 2008-08-25 : 00:11:45

It seems that my SQL statement works in SQL Server 2000 but not in 2005 for the row locking.

Begin transacton
select * from mytable with (xlock, rowlock) where myfield='abc'

I can update other records or query other records in 2000. However, in 2005, it holds all the records until rollback or commit is executed. How can I get the results same as that of 2000?

Thanks

torust
Starting Member

5 Posts

Posted - 2008-08-25 : 02:44:22

In 2000, if I just use select * from mytable, the statement is hold until executing

Begin transacton
select * from mytable with (xlock, rowlock) where myfield='abc'

However, 2005 seems default is no lock. Even I use select * from mytable in other client, the whole set of results can be display. But if I use select * from mytable with (xlock, rowlock) where myfield <> "abc", the statement is hold while 2000 can display the result what I want.

torust
Starting Member

5 Posts

Posted - 2008-08-25 : 05:37:33

sorry, I use
Begin transacton
select * from mytable with (holdlock, rowlock) where myfield='abc' instead of xlock..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources