Lock conflicts with Automatic Row Processing (DML)

Oracle APEX 3.0 contains a nice new feature you should be aware of. There is a new substitution variable APEX_DML_LOCK_WAIT_TIME which you can use to define how the behavior of the “Automatic Row Processing (DML)” and ApplyMRU/D processes should be in case if the processed row is locked.

With the current setting APEX will wait forever until the row gets unlocked. The bad think about that is that users don’t really wait long, they will press the stop button of the browser and submit the changes again and again. And everytime a new database connection from the connection pool is consumed and never released. Probably that would be an interesting denial of service attack vector, but that’s a different story.

When can such a locking situation occur?

For example if you have a database where you also have an Oracle Forms application to access the same data, or any other client/server application. Because it’s quite common for such applications, that they have a pessimistic locking strategy which means that they lock the row as soon as the users starts to change something. APEX and most web applications use an optimistic locking strategy.

Locks can also exist for a longer time if you have long running batch jobs or something similar which are not immediately committing there changes.

What setting should I use?

I would suggest to set the new substitution value to 0 in every application you create. Independent if you have the above scenario. A setting of 0 will immediately raise an error if the row is locked.

Other options can be found in the online documentation in the chapter About DML Lockings. More background information can be found in the related OTN thread.