Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    0

    Unanswered: Transaction in Stored Precedure

    If your stored procedure has just this code within it (nothing else), does it make sense to put it into a transaction?

    begin tran

    insert into RECTR_fax_status_audit
    select *
    from RECTR_fax_status
    where fax_id = @fax_id

    commit tran


    Thanks so much for your help!

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Every SQL DML statement in ASE is an implicit transaction. So, unless you want to be able to roll it back it is merely superfluous to specify an actual begin/commit. I don't know if there are performance implications of explicit transactions in this case but I wouldn't be surprised if there were.
    Thanks,

    Matt

  3. #3
    Join Date
    Dec 2004
    Location
    Broomfield, Colorado
    Posts
    16

    named transactions.

    In certain instances it is helpful to trace the code that is blocking/locking.

    For this reason, we name the transactions after the stored procedure or block of code/ sub application executing it. Even for single dml statements.

    Then you can see the transaction and help isolate where it is coming from:

    begin tran whatever_app_create

    insert into whatever values ( 1,2,3)

    .........
    go



    -- in another session: If you know the blocking spid.....

    select spid, tran_name from master..sysprocesses
    where spid = <spid>
    go

    -- I think there is a gotcha with nested named transactions, not sure if that is still the case with the 12.x versions of ASE.

    Johndz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •