Each time when data is changed and updated through ADF BC, before posting changes to DB, SQL query with FOR UPDATE NOWAIT is generated and executed. In case if other process locks row to be updated, or another user in the same moment is updating it, error will be generated and update will be stopped. There might be use cases, when you would like to wait for certain period of time, until row will be unlocked and then commit row changes. This is especially true, if 3rd party (PL/SQL) process is updating rows and you have defined change indicator attribute in ADF BC (see my previous post - ADF BC Version Number and Change Indicator to Track Changed Rows).
We can change default behavior, instead of requesting for immediate lock - we can wait a period of time. If lock becomes available during this period, session acquires lock. If row remains locked, error is returned. Instead of default FOR UDPATE NOWAIT, we can generate FOR UDPATE WAIT (time period in seconds).
To override default behavior, we need to specify custom SQLBuilder class. This can be registered in Application Module configuration jbo.SQLBuilder property:
Class must extend from OracleSQLBuilderImpl and override getSqlVariantLockTrailer() method. My sample application is implemented to return FOR UPDATE WAIT 30:
We can do a test. We can simulate PL/SQL lock by executing SQL query with FOR UPDATE from SQL Developer:
Try to update same row from ADF BC with default behavior, error will be returned - "Failed to lock the record, another user holds the lock". This is expected, because row remains locked:
With overriden SQL Builder, FOR UPDATE WAIT 30 is generated. It waits 30 seconds, as soon as lock is removed - lock from current session is set and row data is updated:
Download sample application - LockUpdateApp.zip.
We can change default behavior, instead of requesting for immediate lock - we can wait a period of time. If lock becomes available during this period, session acquires lock. If row remains locked, error is returned. Instead of default FOR UDPATE NOWAIT, we can generate FOR UDPATE WAIT (time period in seconds).
To override default behavior, we need to specify custom SQLBuilder class. This can be registered in Application Module configuration jbo.SQLBuilder property:
Class must extend from OracleSQLBuilderImpl and override getSqlVariantLockTrailer() method. My sample application is implemented to return FOR UPDATE WAIT 30:
We can do a test. We can simulate PL/SQL lock by executing SQL query with FOR UPDATE from SQL Developer:
Try to update same row from ADF BC with default behavior, error will be returned - "Failed to lock the record, another user holds the lock". This is expected, because row remains locked:
With overriden SQL Builder, FOR UPDATE WAIT 30 is generated. It waits 30 seconds, as soon as lock is removed - lock from current session is set and row data is updated:
Download sample application - LockUpdateApp.zip.
No comments:
Post a Comment