SQL Server Performance Forum – Threads Archive
Open transactions
Hi,I am using sql server 2000. some times when i was executing a simple select query against in the database, the select query is keep on running. is this because of row lock ? and transactions are opened before executing a query? what are the situation open tranasctions are occured… please explain me what is meant by open tranasction and when it is occured? how to close the transction? Thanks
A few things to check for: 1. Even simple select queries run against a large/unindexed/fragmented database can run for a very long time.
2. Open transactions can block other SQL statements depending on the transaction isolation level of that open transaction. By default, there shouldn’t be any issue with isolation levels. An open transaction? : Basically a transaction that has not executed its COMMIT or ROLLBACK statement (or has not been chosen as a deadlock victim.) Closing it? : First run sp_lock and sp_who2 to determine which processes and statements are blocking(if any) your SELECT statement. You can choose to KILL them (because more often than not, you cannot issue a COMMIT/ROLLBACK).
Nathan H. Omukwenyi
Hi, Thanks for ur information. onething i did not get it… open transaction has not been chosen as a deadlock victim.. can u explain abt what are situations deadlock can occured?
if i kill the process then i cannt retrieve the value for the select statement or i did wrong. Thanks
quote:Originally posted by vbkenya
A few things to check for:
1. Even simple select queries run against a large/unindexed/fragmented database can run for a very long time.
2. Open transactions can block other SQL statements depending on the transaction isolation level of that open transaction. By default, there shouldn’t be any issue with isolation levels. An open transaction? : Basically a transaction that has not executed its COMMIT or ROLLBACK statement (or has not been chosen as a deadlock victim.) Closing it? : First run sp_lock and sp_who2 to determine which processes and statements are blocking(if any) your SELECT statement. You can choose to KILL them (because more often than not, you cannot issue a COMMIT/ROLLBACK).
Nathan H. Omukwenyi
2. Open transactions can block other SQL statements depending on the transaction isolation level of that open transaction. By default, there shouldn’t be any issue with isolation levels. An open transaction? : Basically a transaction that has not executed its COMMIT or ROLLBACK statement (or has not been chosen as a deadlock victim.) Closing it? : First run sp_lock and sp_who2 to determine which processes and statements are blocking(if any) your SELECT statement. You can choose to KILL them (because more often than not, you cannot issue a COMMIT/ROLLBACK).
Nathan H. Omukwenyi
run this command in QA to know about transactions in current database dbcc opentran
Hi, i have ran this command .. it show the given below result Oldest 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:560PM so can u tell me, 64 is the blocking process ? and i have used dbcc inputbuffer(64), it displays a query… is this query blocking the other processes? i have checked the lock type is LCK_M_S … why this lock is occured? what are situations they are occured?
quote:Originally posted by ranjitjain
run this command in QA to know about transactions in current database
dbcc opentran
1. Simple select query can run forever even without locking or fragmentation. It is easy to write long running query.
2. Active transaction doesn’t mean it blocks anything.
To examine locks run sp_lock stored procedure.
]]>