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 |
|
blocky97
Starting Member
3 Posts |
 Posted - 2002-06-19 : 14:36:13
|
| I hope someone can help meI have a message board, I am using ASP SQL Server 2000Here is the situation that i think is happening...a user inserts a post into a thread, (or multiple users)another user(mulitple) users retrives the post...now because the insert is not done, i am getting a deadlock (the insert is a simple insert and 2 updates)i put a deadlock timeout of 300 miliseconds, however i keep getting:[Microsoft][ODBC SQL Server Driver][SQL Server]Lock request time out period exceeded.before i put the timeout i got:[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.is there a way to make it so that i don't get the deadlock, i.e. i don't care if the user does not get the post that is happening at the same time, thanksseth |
|
|
ThreePea
Yak Posting Veteran
83 Posts |
Posted - 2002-06-19 : 15:10:40
|
quote:
another user(mulitple) users retrives the post...
Exactly how does the user "retrieve" the post? I don't know for sure, but if you are using a simple SELECT statment you might solve your problem by using the NOLOCK locking hint, like so:SELECT field1, field2FROM post (NOLOCK)WHERE ... From BOL:NOLOCK: Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.=======================================================Tolerance is the last virtue of an immoral society. |
 |
|
|
blocky97
Starting Member
3 Posts |
Posted - 2002-06-19 : 15:12:43
|
| i read somewhere that you should not use hints, but i'll try it anyways!!! anything to make it stop locking!!! thanks! |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-19 : 16:28:00
|
quote:
i read somewhere that you should not use hints, but i'll try it anyways!!! anything to make it stop locking!!! thanks!
You read correctly, (nolock) is a bad option to use and causes all sorts of problems.Post the DML for the insert and updates and we'll find a better solution for you.setBasedIsTheTruepath<O> |
|
|
|
ThreePea
Yak Posting Veteran
83 Posts |
Posted - 2002-06-19 : 17:02:27
|
quote:
You read correctly, (nolock) is a bad option to use and causes all sorts of problems.
Oh, I have to disagree. We use NOLOCK on almost all our SELECTS, to speed up performance and avoid unnecessary share locks. In our situation dirty reads are not a problem, and in fact are more desireable than forcing users to wait for a record or a page to be free.blocky97 said he read something that stated you shouldn't use hints at all, which I think is just plain silly. Like any advanced feature you have to be careful, and know what you are doing, of course. But that doesn't mean you avoid them when they can help.I'm curious what "all sorts of problems" entails, as we haven't encountered one after eight years of doing it this way.========================================Tolerance is the last virtue of an immoral society. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-20 : 11:12:14
|
| Threepea, Dirty reads aren't a problem unless you rollback transactions on a regular basis. Depending on your business rules you might not perform any rollbacks, and if so (nolock) is safe and (depending on circumstances) provides marginally increased performance. Significant performance increases as a result of (nolock) is a sign of poor application design. The application might be holding transactions open for unnecessary durations of time, or not fetching rowsets to completion, or pulling back unnecessarily large rowsets, etc.However, if you do rollback transactions regularly, as is common with financial systems, for example, (nolock) can cause queries to return phantom data. Bad data is immeasurably worse than slowly obtained data.While on the subject of hints generally, they aren't a good idea because they force the optimizer to use execution paths that might not be optimal at a future point in time. When you use a join hint, or an index hint, you force an execution plan that might be good under current conditions but not after index selectivity changes or schema changes or any number of issues arise. Execution plans are not static; SQL Server recompiles objects on a regular basis to take advantage of that fact and hints essentially disable that. Worse, there's no way to know that SQL Server would choose a better plan for a query should a hint be removed.setBasedIsTheTruepath<O>Edited by - setbasedisthetruepath on 06/20/2002 13:24:30 |
|
|
|
|
|
|
|
|
deadlock