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!
Is this parameter is new in 20c?
could see that being available in 19c too.
It has been backported
Except from the implementation simplicity, how is that different from the max_idle_blocker_time plan directive available with resource manager (since 8i)?
I dont know an official reason, but my hypothesis is that resource manager isn’t a great fit – because we might want to change plans etc during the course of day, week, etc. Good example of this is maintenance plans in the evening that we use for advisors and optimizer stats. You don’t want to have to edit every resource plan to include idle blocking directives when most likely you want to be static at the instance level.