SQL Server Performance

Should i use commit for select statement

Discussion in 'T-SQL Performance Tuning for Developers' started by devteam, Feb 24, 2006.

  1. devteam New Member

    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

  2. SQLDBcontrol New Member

    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
  3. devteam New Member

    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
  4. SQLDBcontrol New Member

    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

Share This Page