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.
| Author |
Topic |
|
sqldev6
Starting Member
18 Posts |
 Posted - 2008-07-24 : 09:54:48
|
| Hi All,In a transaction we are retrieving top 10 records from table by using the ROWLOCK and HOLDLOCK hints for the given search criteria. After that we are updating some status for those top 10 records and issuing the COMMITE transaction. Why we are using those hints in the SELECT query is, if in any other transaction if other user provide the same search criteria then those records(above selected records) should not be reterive. At the same time different applications are trying to insert the records into that same table. While trying to insert records we are getting Time out error.Could any one please help us.Thanks in advance. |
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2008-07-24 : 11:25:05
|
| Without seeing the implementation, it can very difficult do decide on a reply and ultimately depends on what you are trying to do. The design you just mentioned looks like granular set-exclusion and goes against the grain of a multi-user databases. I have seen this before, but i would recommend not using such hints (rowlock, and holdlock) and implement the correct isolation mode for what your are doing. As a starter, look up Isolation Modes - it will give you a better insight into SQL Server concurrency model and should help to offer a better design to what you have described so far...Are you good enough? Skillprover.com |
 |
|
|
|
|
|
Nedded alternative for ROWLOCK and HOLDLOCK hints.