We are trying to implement pessimistic locks in a system that allows many users to update their data at the same time.
However, I cannot use “GetEntityForUpdate” because the entity is a composite key in an external DB.
Is it possible to use “SELECT FOR UPDATE” in the SQL widget to perform the lock and then use the “CommitTransaction” server action to release the lock?
Also, is there any other way to achieve pessimistic locking with compound keys?
Translated with DeepL.com
Hi takuma minami,
One way to achieve it is to have an additional field in the table with status or just a boolean field (sample:, isaRecordInUse ) to manage the lock.
Each time you access the record you can check the status and if accessible just updated to True.
After operation set back to True.
During the user operation if other user access it and is false you can pop-up an error message and exit.
Hope that it helps
Just to add a correction.
After operation set back to False
You can use the SQL widget to achieve what you are looking for. However, I recommend completing all operations within the SQL widget and committing the transaction there, rather than handling it outside the widget.
Hi @takuma minami,
Use advance Sql to achieve this.
You can write multiple queries.
stored procedure with if else statement
If it's successful it give results in number of rows affected
I hope this will help.
Let me know if you need any help for stored procedure.
Thanks,
Madhuri
Hello
To implement pessimistic locking in OutSystems for an entity with a composite key in an external database, you can use the SELECT FOR UPDATE statement within a SQL widget. Here’s a detailed approach:
Create a SQL Query with SELECT FOR UPDATE:
Add a SQL widget to your action.
Write the SQL query to lock the record based on the composite key
1.Define input parameters @KeyPart1 and @KeyPart2 for the parts of your composite key.
2.Begin Transaction:
Use BeginTransaction to start a transaction before executing the SQL query.
Update the Record:
Perform the update operations after locking the record.Use CommitTransaction to release the lock and commit the changes.
// Begin a transaction
BeginTransaction()
// Lock the record
SELECT * FROM YourTable
WHERE KeyPart1 = @KeyPart1 AND KeyPart2 = @KeyPart2
FOR UPDATE
// Update the record
UPDATE YourTable
SET Column1 = @NewValue
// Commit the transaction to release the lock
CommitTransaction()
Ensure your database supports SELECT FOR UPDATE with composite keys.
i hope it's help you