Do you take pleasure in killing peo…people’s sessions in the database? If so, you are not alone.

If you have Blocking Locks, the easiest way to fix that is kill the offending session and free up the requested resource.

But, who should win this battle? What transactions take precedence? Should the business analyst have his report kiboshed because a developer is testing a new feature? Should an ETL process be allowed to finish under any and all circumstances?

Who Wins, or Who Loses?
I raised this question on Twitter yesterday in response to a post from @HanSQL – who I’m pretty sure shot first in the infamous Cantina scene…

I read this as

  • The Business
  • The DBA
  • The Developer
  • SELECT * jokers
  • I’m not sure we can rely on a ranking system, but I would never cancel a business transaction or process to speed up someone’s query for number of widgets sold in Lower 48 last Febtober.

    Do you have a ranking system? Or do you just kill whoever gets in your way?

    Session Suicide
    Should you have the right to kill yourself in the database? By that I mean, should a database user always have the ability to kill their own sessions? Sometimes a ‘Cancel’ doesn’t cut it. Or do you appeal to the state (DBA)?

    I know, I know, controversial stuff!


    Author

    I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

    3 Comments

    1. Gary – I can’t stand anon/shared logins! I would never grant that PRIV to a BI user. But to a developer, in their own development instance, I can’t think of a reason NOT to allow them to kill their own sessions.

      Wil – I wonder how often the DBA reaches out to the user before yanking them out of the system? Good on you 🙂

    2. “Yourself” is a tricky one. Is another session with the same oracle username “yourself”, or do you go by the osuser or ip address ?

      Sometimes its hard to actually determine who owns what sessions, especially if BI tools are involved. In which case, the best session to kill is the one where you understand who is going impacted by its “death” and how.

    3. I think you’re on the right track here. You can’t always say DBA > Developer; rather you’ve got to look at the underlying reason each query is being run. Since you won’t always know why each query is running, the hierarchy you mentioned above might be what we default to, sure. But if the Developer is working on a critical production support issue, killing their query could be worse than killing a query from the business.

      In my situation last night, I was working on a Production release and the Developer should have known better than to run his query. But you can bet that I checked in with him before indiscriminately killing that query.

      And yes, I did shoot that bastard Greedo first. 🙂

    Reply To Wil Sisney (@HanSQL) Cancel Reply