row lock start

Row Locking Processor

Version: 17.07

Supported Since: 17.07

What is Rowlock Starting Processor?

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 lock start outports
row lock end outports

Out Ports

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

Parameters

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.

Sample Use Case

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.

row lock starting processor1

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.

In this topic
In this topic
Contact Us