Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2008
    Posts
    56

    Unanswered: Long running transactions

    Hi,
    I have an issue that I see alot where applications appear to run alot longer than they should. We are running SQL Server 2000 and the applations are built in Power Builder.
    What I see is transactions running for longer than I hour witht the last command issued as "Awaiting Command". There don't appear to be any blocks or deadlocks. Could this be an issue with the application not closing the connection?

    Thanks

    Rebecca

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    yeah - I think you are confusing transactions with connections. Are you talking about the output of sp_who2? Just to really cover everything, off you are refreshing right?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2008
    Posts
    56
    Hi, I was looking at the output from a 3rd party tool -SQL Diagnostics Manager. There it is being referred to as a transaction. Looking at it from sp_who2 it's status is sleeping so it probally isn't processing any data.
    Thanks

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    What version of PowerBuilder are you using? I have to use version 6.5 (for reasons) and I can't make it run smooth with SQL server 2005. When I open the "Select Tables" window, all I get are the system tables.

    Selecting or deselecting "Show system tables" makes no difference.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Apply SP4 recently ? anyway, you would see what appears to be connections blocking themselves, something MS calls showing us I/O waits that seems to slow things down 100 fold.

  6. #6
    Join Date
    Oct 2008
    Posts
    56
    We are using Power Builder 11.
    Do you know if there is a feature in PB11 to automatically logout a user after a certain time of inactivity?

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    "Sleeping" can (but does not necessarily) mean that the process is waiting for data to be read from disk. "Running" means at least one CPU is actually working on the task. Is the SPID's DiskIO number going up fairly regularly?

  8. #8
    Join Date
    Oct 2008
    Posts
    56
    The disk IO looks like it remains fairly constant.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Rebecca Johnston
    We are using Power Builder 11.
    Do you know if there is a feature in PB11 to automatically logout a user after a certain time of inactivity?
    Yes, this is easy to do and it will horrendously complicate your problem.

    This problem is normally caused when SQLCA.AutoCommit is true and you are relying on the combined wisdom and kindness of PowerBuilder to manage your SQL transactions for you. I've seen many applications and development tools, but I can't remember one of either being particularly wise or kind.

    If you want to fix the problem, you need to start to manage SQL transactions explicitly within your PowerBuilder code. If you want your application to start experiencing random data loss, application hangs, and miscellaneous odd behaviors then you probably need to add one line of code to the application object's timeout event.

    -PatP

  10. #10
    Join Date
    Oct 2008
    Posts
    56
    Thanks for you help. This application is legacy code. What I have been doing with the app team moving forward is to force them to use stored procs that contain the transaction processing rather than let them use embedded sql. In the stored procs I begin and end (commit/rollback) the trasactions.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just an observation... With new procedures, divide them into two groups: called by application versus called only by other sprocs. Make ROLLBACK TRANSACTION the first statement in procedures called by the application... That's the fastest way I've found to break PowerBuilder developers of bad habits.

    Just for the giggle factor, the line that you can add to the timeout event that will cause most most PowerBuilder applications to terminate is something like:
    Code:
    goApp.Close
    -PatP

Posting Permissions

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