Version: 17.07
Supported Since: 17.07
The Row Lock Scope Start processing element can be used to do some processing while acquiring a row lock on a configured row of a database table. This will acquire a row lock as configured from the given parameters, and proceed with further processing with the row lock until it founds a Row Lock Scope End Processor which will complete the row lock path by releasing the acquired lock. Because of that, these two elements are coupled together and users have the freedom to perform any row lock dependent processing in between these two elements using the fetched result set from the locked row. Extracted result set data from the locked row is available as a scope variable and this variable name can be configured via the ResultSet Variable Name parameter.
Once processing reached the Row Lock Scope End Processor and the row lock was released, processing will return back to the normal processing flow and proceed.
This row locking processing elements can be used with any relational SQL database system with row lock support, such as Oracle, SQL Server and MySQL databases.
In order to use this processing element, you must first select the Database Operations dependency from the processor list when you are creating an empty Ultra project. If you have already created a project, you can add this dependency via Component Registry. From Tools menu, select Ultra Studio → Component Registry and from the Processors list, select the Database Operations dependency. |
Row Locking Flow |
The processing element will acquire the row lock for the configured row and processing will be forwarded to this port after assigning the result set as a scope variable |
Row Lock Complete Flow |
After completing the row lock related processing and releasing the row lock, processing will be handed over to this out port |
Empty Locked Row Completion |
If any rows could not be found for row locking, processing will be handed over to this outport |
On Exception |
The message context will be sent to this outport if any exception occurred while extracting the payload from the message |
Data source * |
Basic |
Data Source resource to be used to get the database row lock. |
SQL query * |
Basic |
SQL query to be used to get the row lock and this should include the required syntax to acquire the row lock based on the underline database type |
Mapping file |
Basic |
Optional mapping file to configure parameters which are used in the SQL query |
ResultSet Variable Name |
Advance |
An optional value to configure the variable name of the result set which is fetched with the row lock. If this is not configured, default value (result_set.reference) will be used. |
Max Rows to Lock |
Advance |
An optional integer value to configure the maximum number of rows to fetch for the row lock at a single attempt. |
In the following use case, the requirement is to do a database update with multiple threads by polling the database to select possible candidates for the update. A single row should be updated by a single thread to make sure the consistency of the database. Because of that it’s required to get a row lock of a row before starting process and then do required update and mark it as a processed row to make sure that it won’t be picked up for later.
Result Set Updater and Result Set Reader is used to read the values from the database row and update the row as processed at the end of the flow.
In this flow a Row Lock Scope Start processing elements is configured to get a row lock of a single row and extract out the result set to a variable.