Should i use commit for select statement | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Should i use commit for select statement

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 statement
i have checked the lock type is LCK_M_S … 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 thanks
Even select queries place locks (shared locks) for the duration of the select statement. And that is exactly what LCK_M_S is – a shared lock. SQL statements all run in a transaction. If you do not specify "begin tran" explicitly then SQL Server runs each individual statement as an implicit transaction.
quote:Originally posted by devteam 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 statement
i have checked the lock type is LCK_M_S … 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 thanks

Karl Grambow www.sqldbcontrol.com
I did not used any procedures. i have just written a code for select statement.
then how can i specify the begin tran.
quote:Originally posted by SQLDBcontrol Even select queries place locks (shared locks) for the duration of the select statement. And that is exactly what LCK_M_S is – a shared lock. SQL statements all run in a transaction. If you do not specify "begin tran" explicitly then SQL Server runs each individual statement as an implicit transaction.
quote:Originally posted by devteam 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 statement
i have checked the lock type is LCK_M_S … 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 thanks

Karl Grambow www.sqldbcontrol.com

I’m not sure I understand your question. Procedures have nothing to do with transactions. A transaction is a single unit of work (as defined by you) that completes as a whole or fails as a whole. –this is a transaction (implicit)
update orders
set orderdate = ’10 May 2002′
where orderid=2 –this is second transaction (implicit)
update orders
set orderdate = ’10 May 2002′
where orderid=3 –this is also a transaction (explicit)
begin tran
update orders
set orderdate = ’10 May 2002′
where orderid=2 update orders
set orderdate = ’10 May 2002′
where orderid=3
commit tran
quote:Originally posted by devteam I did not used any procedures. i have just written a code for select statement.
then how can i specify the begin tran.
quote:Originally posted by SQLDBcontrol Even select queries place locks (shared locks) for the duration of the select statement. And that is exactly what LCK_M_S is – a shared lock. SQL statements all run in a transaction. If you do not specify "begin tran" explicitly then SQL Server runs each individual statement as an implicit transaction.
quote:Originally posted by devteam 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 statement
i have checked the lock type is LCK_M_S … 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 thanks

Karl Grambow www.sqldbcontrol.com

Karl Grambow www.sqldbcontrol.com
]]>