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 |
|
devteam
Starting Member
15 Posts |
 Posted - 2006-02-24 : 23:00:45
|
| Hi,i am using sql server 2000. while i am using my application, simple select query also blocking...i have used default transaction TRANSACTION_READ_COMMITTED and also i did not do any insert and update operation. even that query is blocking the other process also... after blocking occured i have used dbcc opentran command to found out the old transaction given below... what is meant for implicit transaction and also why simple select query is blocking without any insert and update statementOldest active transaction: SPID (server process ID) : 64 UID (user ID) : 1 Name : implicit_transaction LSN : (1607:65:1) Start time : Feb 24 2006 12:24:06:560PMthanks |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2006-02-25 : 08:47:09
|
| A longstanding transaction will lock everything. if you cannot ensure that the transaction will commit or rollback quickly, you really really shouldnt use it. |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-02-25 : 09:19:49
|
| In a heavily used OLTP application where there are lots of quick inserts,updates,deletes along with quick selects you will occasionally need longer running selects. Like for searches or reports. If those can't be put off to a "reporting database" like a replicated version, then a typical approach is to use READ UNCOMMITTED isolation levels for the selects. That will, of course, result in "dirty reads" but usually that's not a problem. Depends on your business and requirements of the results. But that stategy will prevent any blocking by the selects. By the way, when I think of "longer running selects" I'm thinking like 1 to 20 seconds. If you're running things that take minutes or longer then like coolerbob says, you probably should find another way or create an environment where that won't affect the "app user experience"On another note, if that Oldest Active Transaction is too old (meaning no one is waiting for these results) then something weird happened. You need to kill that connection and see if it is a recurring problem which you should get to the bottom of.Be One with the OptimizerTG |
|
|
|
|
|
|
Should use commit for select statement