Oracle 12. Maximum duration for "select for update" for occi C ++

Question

We use occi to access Oracle 12 through the C ++ process. One of the operations is to ensure that the client must select the latest data in the database and work according to the latest value. Statement

std::string sqlStmt = "SELECT REF(a) FROM O_RECORD a WHERE G_ID= :1 AND P_STATUS IN (:2, :3) FOR UPDATE OF PL_STATUS" 

(we use TYPES). For some reason, this command did not pass, and the database table is LOCKED. All other operations are waiting for the completion of the first thread, but the thread is killed, and we have reached a dead point.

What is the best solution to avoid this catastrophic senario? Can I set the timeout in the instructions so that it is 100% that the thread can work on "choose to update", for example, a maximum of 10 seconds? In other words, the thread of execution can block the table / row of the database, but for no more than a certain time.

Is it possible?

Answer 1

There is a ddl_lock_timeout session ddl_lock_timeout , but dml_lock_timeout is missing. Therefore, you cannot go this route. So you need to use

 SELECT REF(a) FROM O_RECORD a WHERE G_ID= :1 AND P_STATUS IN (:2, :3) FOR UPDATE OF PL_STATUS SKIP LOCKED 

And change the application logic. Or you can implement your own interrupt mechanism. Just start the parallel thread and after a while run OCIBreak . This is a documented and maintained solution. The OCIBreak call is thread safe. A locked SELECT .. FOR UPDATE will be issued and you will receive error message ORA-01013: user requested cancel of current operation

So, at the OCCI level, you have to handle this error.

Answer 2

Edit : A resource manager has been added that can impose an even more precise restriction by simply focusing on those sessions that are blocking others ...

using the resource manager:

The resource manager allows you to define more complex policies than those available for profiles, and in your case is more suitable than the latter.

You need to identify the plan and user groups associated with the plan, specify the policies associated with the plan / groups, and finally attach users to the groups. To have an idea of ​​how to do this, you can reuse this example @ support.oracle.com (it's too long to be posted here), but replacing MAX_IDLE_TIME with MAX_IDLE_BLOCKER_TIME .

The main line will be

 dbms_resource_manager.create_plan_directive( plan => 'TEST_PLAN', group_or_subplan => 'my_limited_throttled_group', comment => 'Limit blocking idle time to 300 seconds', MAX_IDLE_BLOCKER_TIME => 300) ; 

using profiles:

You can limit the period of inactivity of this session by specifying IDLE_TIME .

CREATE PROFILE :

If the user exceeds the session resource limit CONNECT_TIME or IDLE_TIME , then the database rolls back the current transaction and ends the session. When a user process calls, the database returns an error

To do this, specify the profile with the maximum downtime and apply it only to the appropriate users (so that you will not affect all users or applications)

 CREATE PROFILE o_record_consumer LIMIT IDLE_TIME 2; --2 minutes timeout alter user the_record_consumer profile o_record_consumer; 

The disadvantage is that this parameter is common for the entire session, therefore, if the same session should remain in standby mode during other operations, this policy will be applied in any case.

of interest...

You may already know that other sessions can coordinate their access to the same record in several ways:

• FOR UPDATE WAIT x; If you add the WAIT x clause to your select for update statement, the wait session will refuse to wait after the "x" seconds have elapsed. (the integer "x" must be hard-coded there, for example, the value "3", the variable will not work, at least in Oracle 11gR2).
• SKIP LOCKED; If you add the SKIP LOCKED clause to your select for update statement, the selection will not return records that are locked (as ibre5041 has already pointed up).
• You can signal an additional session (a kind of watchdog timer) that your session should start the request and, after successful execution, notify it of completion. A watchdog session can implement its kill-the-session-after-timeout logic. You have to pay extra complexity, but get the benefit of the fact that the timeout applies to this particular application, and not to the session. For this, see ORACLE-BASE - DBMS_PIPE or 3.2 DBMS_ALERT: Broadcasting Alerts to Users, Steven Feuerstein, 1998 .

Finally, perhaps you are trying to implement a home queue infrastructure. In this case, keep in mind that Oracle already has its own queue mechanics called "Extended Queue", and you can get a lot by simply using them; see ORACLE-BASE - Oracle Advanced Queuing .