We are using occi in order to access Oracle 12 via a C++ process. One of the operations has to ensure that the client has to pick the latest data in the database and operate according to the latest value. The statement is
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 are using TYPES). For some reason this command did not go though and the database table is LOCKED. All other operations are waiting the first thread to finish, however the thread is killed and we have reached a deadend.
What is the optimal solution to avoid this catastrophic senario? Can I set a timeout in the statement in order to by 100% that a thread can operate on the "select for update", let's say for a maximum of 10 seconds? In other words the thread of execution can lock the database table/row but no more than a predifined time.
Is this possible?
There is a session parameter ddl_lock_timeout but no dml_lock_timeout. So you can not go this way. So Either you have 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 modify the application logic. Or you can implement your own interruption mechanism. Simply fire a parallel thread and after some time execute OCIBreak. It is documented and supported solution. Calling OCIBreak is thread safe. The blocked SELECT .. FOR UPDATE statement will be released and you will get an error ORA-01013: user requested cancel of current operation
So on OCCI level you will have to handle this error.