Locked rows and lunch breaks ? A simple fix

Even in the new world of stateless web applications, from time to time we still come across the “lunch break locking” problem.  That is, someone or something, commences a transaction which locks some rows, and then their session goes idle, without committing or rolling back the transaction.  The metaphor commonly used was the “someone locks a row in a table and then goes out for lunch”, with them being totally oblivious to the havoc they have caused until they return.

With modern applications being generally stateless (or transactionally stateless) you would think the problem would have disappeared, but if anything, whilst the frequency of the problem has dropped, the diagnosis of the problem when it occurs is harder than ever.  The anonymity of application server sessions, and the scale of web-based customers means when a session does “lose track” of what it was doing and leaves a transaction active, it is very hard to detect until we have a major problem or outage on our hands.

But here’s a mitigation strategy you might want to consider.  Resource Manager can do some of the heavy lifting for you.  It can kill sessions that are idle but are blocking other sessions from completing their work.

Here’s a simple demo of how to put a resource manager plan in place to do that

SQL> begin
  2    dbms_resource_manager.create_pending_area();
  3    --
  5    dbms_resource_manager.create_consumer_group(
  7      COMMENT=>'CG for stop blocking'
  8      );
 10    dbms_resource_manager.create_plan(
 11      PLAN=> 'STOP_BLOCKERS',
 12      COMMENT=>'Plan for stop blocking'
 13    );
 15    dbms_resource_manager.create_plan_directive(
 16      PLAN=> 'STOP_BLOCKERS',
 18      COMMENT=>'Directive',
 19      MAX_IDLE_BLOCKER_TIME => 60
 20    );
 23    dbms_resource_manager.create_plan_directive(
 24      PLAN=> 'STOP_BLOCKERS',
 26      COMMENT=>'leave others alone'
 27    );
 33  end;
 34  /

PL/SQL procedure successfully completed.

The key element here is the plan directive, which sets MAX_IDLE_BLOCKER_TIME to 60 seconds. So if an idle session is blocking another for that duration, resource manage will kill the session. Let’s assign the the appropriate consumer group to SCOTT, enable the plan and see it in action

SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group('SCOTT','CG_STOP_BLOCKERS',false);

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager.set_initial_consumer_group('SCOTT','CG_STOP_BLOCKERS');

PL/SQL procedure successfully completed.


System altered.

SQL> conn scott/tiger

SQL> create table t as select 1 x from dual;

Table created.

SQL> delete from t;

1 row deleted.

So this session now has a lock on the row, but is now idle…Now we fire up another session who will get blocked

SQL> set timing on
SQL> delete from t;


After approximately 60 seconds, the second session did in fact respond

SQL> set timing on
SQL> delete from t;

1 row deleted.

Elapsed: 00:00:58.09

So presumably the lock from session 1 has been released. If we return to session 1, we can see how the lock get released

SQL> select * from dual;
select * from dual
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 24708
Session ID: 593 Serial number: 59128

They got killed ! So they can take their time at lunch, and come back with their double-choc muffins, chai latte and watercress salad…We don’t care, because our application is protected Smile

6 thoughts on “Locked rows and lunch breaks ? A simple fix

  1. Hi Connor,

    one of the problems that I regularly see at customer sites is that at least up to and including the sessions get only “killed” using the regular internal “kill” procedure, no matter if killed via Resource Manager or traditional profiles idle time rules.

    Although this frees up locks on transaction level etc., it doesn’t free up Parallel Slaves allocated to cursors that are still open (think of GUI tools like SQLDeveloper and only the first n rows fetched before leaving for lunch).

    Hence these customers do have the problem that they might have many more allocated Parallel Slaves than necessary with all kinds of possible side effects (downgrades, or queuing with new Auto DOP).

    These Parallel Slaves are only freed up when the killed session gets the confirmation “your session has been killed”, which requires another interaction of that client. As long as this doesn’t happen the Parallel Slaves are not freed up.

    I notice now above that you get a “ORA-03113”, and not the “ORA-00028 your session has been killed”, so has this behaviour maybe changed in 12c, meaning a “hard” disconnect instead of graceful “kill”, which would be great, in the hope that Parallel Slaves are now also freed up as soon as the session gets killed.


    • Thanks for stopping by Randolf. I have to admit, I’ve never look at this with parallel in mind. I do wish that in SQL Dev that if a query was run in parallel then (by default) all rows are returned, because the concept of incremental fetch strikes me as counter intuitive to running something in parallel.

  2. Thanks for this.
    Very useful for one of our databases where a state-of-the-art (NOT!) Java application (could it possibly be anything else?) is hell bent on randomly creating row lock contention almost every day…

  3. This is something I didn’t know about, and I think will be really useful. Thank you.

    Only comment is that ‘set_initial_consumer_group’ is deprecated and ‘set_consumer_group_mapping’ is a much more flexible way of putting a session into a consumer group.

    I appreciate that it’s not as simple for a demonstration though.

    • Thanks for the feedback Phil. I have to admit – it was laziness to use “set_initial_consumer_group” because I just re-hashed an old script for the demo. So it was totally valid to call me out on that🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s