Posted: 10 Oct 2020 7:36 EDT Last activity: 18 Nov 2020 1:45 EST
Pega lock mechanism
In this post, I am summarizing how Pega object locking works. If you find any information incorrect please let me know so I will update it.
First of all, Pega uses both database level lock and Pega level lock. Both locking are record basis and not entire table basis.
1. Pega level lock
When someone opens an assignment, the state becomes "Perform" mode and lock is obtained by the system. The lock information is inserted into "PR_SYS_LOCKS" table in the database and it is managed there. In this period, no other user can open the same assignment. When user submits or cancel the assignment, the state becomes "Review" mode and lock is released. The record is deleted from the table.
When someone tries to open an assignment that is already locked by other user, a message is shown as below. There is no way to unlock it.
If you try to open an assignment that is locked by yourself in other session, a different message is shown as below. This doesn't happen a lot but it could when you log in to the system using the same ID from different browser or terminal. In this case, you are given an option to forcibly log out other session. Be noted, Pega locks are exclusive to one thread.
2. Soft lock
Lock is effective for 30mins and then expires. After expiration, the lock becomes "soft lock" and other user can steal it thereafter. When other user opens the soft locked assignment, no warning is displayed and user is not aware of its soft lock state. In the database, the same record in PR_SYS_LOCKS table is updated by the new user. Lock before expiration is sometimes referred to as "hard lock" as opposed to soft lock. The defaulted 30mins expiration period can be changed by below two approaches:
(1) If you want to change it per individual case type, update Case Type rule.
(2) If you want to apply the change system-wide, update Data-Admin-System (pega) instance.
* If you update both of them, Case Type rule takes precedence over Data-Admin-System instance.
3. Pessimistic locking vs Optimistic locking
The Pega level object locking explained above is called "Pessimistic locking", as opposed to "Optimistic locking". Optimistic locking is introduced in Pega 7. In my opinion, Optimistic locking is not recommended from data integrity perspectives.
"Allow one user" means Pessimistic Locking.
"Allow multiple users" means Optimistic Locking.
Pessimistic locking (Default locking)
Pega level object locking using PR_SYS_LOCKS table. This approach prevents other users from overwriting data by locking the work object exclusively. A single user is allowed to open an assignment, and in that sense, this is a safer solution.
This approach doesn't use PR_SYS_LOCKS table. Multiple users can open the same assignment at the same time. Whoever saves data latter overwrites data. Be noted that if latter users try to save or submit with updated data, they will be notified that someone else has already updated earlier. They need to reload the section to display the latest information first, and then re-enter their own updates.
* Below is the message notified for latter users. They can't submit or save new data until they refresh the section.
4. Lock mechanism with case hierarchy
Case hierarchy affects lock mechanism in a certain way.
(1) A child case type inherits the locking strategy of its parent case type. For example, if parent case is configured to use Pessimistic locking, then child case will also use Pessimistic locking. It is not possible to use different locking between parent / child cases.
(2) If Pessimistic locking is used, when child case is locked, parent case is also locked by itself. This is because parent case holds child case status, such as "pxCoveredCount" and "pxCoveredCountOpen" and these data need to be updated per child case update accordingly. This is the default behavior but it is possible to change the settings so it won't lock parent case when child case is locked although I wouldn't really recommend it for the data consistency.
5. Allow locking settings at Class Group / Class level
I explained how to set Pessimistic Locking or Optimistic Locking above but actually there is one more prerequisite setting in a lower level - Class Group or Class form. For Work instance, locking must be enabled at Class Group form. For Non-Work (≈Data) instance, locking must be enabled at Class form.
(1) Work (Class Group)
When you define a case type, this checkbox is turned on by default. You can always keep it checked. By checking this off, Pega level lock is disabled but you can also turn it off by using Optimistic locking. Therefore, there is no advantage in check this off.
(2) Data (Class)
Unlike Work, Data has "Locking" tab in the Class form. When you define a data type and configure local data storage, this checkbox gets turned on by itself. When it is checked, Data instance locking works in similar manner as Work and PR_SYS_LOCKS table is used. The difference between Work and Data is, Data instance is not locked when assignment is opened, but it is locked when data row is being edited (ex. inline row editing). If you check this off, it behaves like Optimistic Locking.
* If Allow locking is checked for Data instance, when someone starts editing the record, lock is obtained. A message is shown as below if you try to update a locked instance.
6. Controlling lock in an activity
When end user operates on screen, object locking is managed by the system automatically, but when we use activity, lock must be controlled by developer with care.
In Obj-Open method or Obj-Open-By-Handle method, there are two checkboxes - "Lock" and "ReleaseOnCommit". If you check "Lock", lock is obtained in this method. If you check "ReleaseOnCommit", lock is released when Commit method is executed. When you are planning to update the instance, check both of them. In most cases, check / uncheck both of them. In the unusual case, you may want to check only "Lock", and not "ReleaseOnCommit", to retain the lock after Commit (perhaps because additional changes are expected). Then you should manually release lock later using the Page-Unlock method etc (*).
Another method, Obj-Refresh-And-Lock also can obtain a lock. While Obj-Open and Obj-Open-By-Handle grabs data from database every time, Obj-Refresh-And-Lock is used when you already have a page on clipboard. If the object is not locked, this method acquires a lock and ensures that the version you have on a clipboard page is the same as or newer than the version currently in the database. If the object is locked, this method has no effect.
* I wouldn't recommend to implement programmatic solution but if you need to manually release a lock, do so using either of below approach.
***Edited by Moderator: Pooja to update platform capability tag, add developer knowledge share tag***
java.sql.SQLIntegrityConstraintViolationException / OR ***** unique constraint (PR_SYS_LOCKS_PK) violated
it is to do with when pega does INSERT into pr_sys_locks with duplicated primary key value (on pzinskey) . After some research I guess this happens in somewhere in our activities that didn't handle the locking properly.
I guess this may be a failure you would get if lock is not release and another user tries to commit the same case.
Posted: 2 months ago
Updated: 2 months ago
Posted: 18 Nov 2020 1:32 EST Updated: 18 Nov 2020 1:45 EST
Let's say, developer manually opens a work object using Obj-Open or Obj-Open-By-Handle method with "Lock" checked, but "ReleaseOnCommit" not checked, and activity ends after committed. Sample code is shown below (bad practice).
In this case, it behaves just like end user closes Window in the middle of perform mode without properly logging off - any changes saved to the database is valid, but lock won't get released (lock is held by the developer who ran the activity). That means, other user won't be able to open the assignment by perform mode for the next 30mins (until hard lock expires), or system restarts. This isn't an ideal situation, and hence it is advised to check both "Lock" and "ReleaseOnCommit" checkbox.