Overview

This user story is related to CPS-875 CM Handle State: Watchdog-process that syncs 'ADVISED' CM Handles wherein locking the database is an essential step of the process.

Recap of watchdog process:

In step 3 of the process, it is necessary to lock the database in order to avoid multiple instances of NCMP updating the records.

It is identified that a ‘lock’ can also be implemented in the DB plugin, CPS, and the client (NCMP) or combination of these.

Locking method scenarios

	Scenario	Description
1	Only anchor table row is locked	can be implemented through PostgreSQL Client options query for 'LOCKED' anchor every mutation handle failure for 'LOCKED' anchor
2	anchor and every fragment associated with the anchor is locked	locks every relevant fragment row in the fragment table associated with the anchor adds new exception to identify that fragment is 'LOCKED'

	Description	Issues	Notes	Decisions
1	Scenario #2 is chosen as the method for locking anchor	locks all fragment when we lock metadata anchor i.e. metadata of all cm handle will be locked metadata of every cm handle is frozen until the first lock is released	we don't need to lock the anchor but lock just the fragment this way client can lock another fragment in parallel client (NCMP) can pass the XPath to specify the fragment they want to lock Options for scenario 2 extend lock capability to the fragment change the model for storing metadata	the team decided to go for option #1 for scenario 2 don't need to specify anchor as it is given by FK you need the path for the fragment
2	To implement a lock we must do it inside a transaction	Session must be introduced in JAVA API (CPS CORE)	query and 'LOCK FOR UPDATE' relevant rows in fragment table inside a transaction no persistency this will be a local transaction between the CPS instance and the database	see CPS-899 Start and stop sessions on Java API