MAX_IDLE_BLOCKER_TIME to stop locking problems

We’ve all been there … You’re trying to save some changes to the database, and no matter what you try, your application just hangs. After some to-and-fro with the DBA, or perhaps you are the DBA, you stumble across that uncommitted transaction that “Joe from marketing” commenced at 11:55am.  You pop around to Joe’s desk, and there it is on screen….and Joe has popped out to lunch for an hour ….. grrrrr!

Of course, you don’t really see this on their screen, because their screen is locked and unless they are one of those people that likes to attach their Windows password on a post-it note next to their monitor then you are out of luck.

(Apologies to anyone really called Joe who works in marketing for the clash of fiction with reality )

Luckily for more modern applications, they are typically stateless and thus the days of transactions commencing and being held straight from the customer’s PC are predominantly a thing of the past. However, even in these situations, the problem can simply shift to that application server, where a browser session has long since either crashed or been shutdown and the accompanying application server process happily sits there forever with an open transaction waiting for a customer response that never comes.

Help is at hand with a nice new parameter in 20c. If a session is idle, but it is blocking others, the database can now turf that session (“turf” is an aussie colloquialism for “kill” ) so that others can continue their database work unimpeded.

Here’s an example of that in action. I’ll set the idle block limit to 1 minute.

SQL> alter system set max_idle_blocker_time = 1;

System altered.

Now session 1 will be our “lunchtime nuisance”.

SQL> delete from t;

1 row deleted.

Session 2 would normally wait forever to get access to this row, but after waiting for a little while … look what happens:

SQL> select * from t for update ;

         X
----------
         1

Elapsed: 00:00:57.42

When I go back to session 1, you can see that the database has given him his marching orders!

SQL> select * from t;
select * from t
              *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 3638
Session ID: 65 Serial number: 16907

Take that Joe!