Statement and lock timeouts

If another session locks a resource, a SELECT FOR UPDATE statement waits for a predetermined time period (timeout duration). If a statement times out, you cannot determine whether the process timed out because of a resource lock or for some other unrelated reason, such as slow network throughput or poor response from the database server.

The timeout duration is configurable. If the database server resides on a separate machine from the driver, or if server performance is not optimal, you might be required to increase the timeout duration so that the database driver does not give up before the server or network has had an opportunity to deliver the results from a request. Note that statements that do not take locks, or statements that read through locks, can also timeout.

To specify the lock wait period for SELECT FOR UPDATE, INSERT, DELETE, UPDATE, and LOCK TABLE statements, use the ORA_TIMEOUT environment variable or the ora_timeout resource variable. The following table specifies the default and minimum timeouts. Note that when you experiment with these options, you must do so with care. Timeout values are specified in increments of seconds.

You can specify the timeouts in the resource ora_timeout as follows:

Ora_timeout:{[value], [value], [value], [value], [value]}
Action:	      select   insert   update   delete   lock table

Here, all parameters are optional and you can specify integers to indicate the timeout for the particular action. If no parameter is specified, the default behavior for the actions is performed.

The following is an example of how to set the timeout parameters:

ora_timeout:{0, 5, 10, 15, 0}