Configure Artifactory to Use Advisory Locks in PostgreSQL

Starting with Artifactory 7.77.3, self-hosted users with PostgreSQL have the option of using a database locking mechanism based on advisory locks instead of the previous mechanism, which is based on distributed table locks. Advisory locks (also referred to as native locks) is a PostgreSQL feature that enables applications such as Artifactory to create locks whose use is enforced by the application, not by PostgreSQL.

Advisory locks help reduce the problem of database table bloating, which increases database storage consumption and has been known, in some cases, to overload the database server CPU and increase the duration of lock/unlock operations.

Prerequisites

Users who wish to implement advisory locks for PostgreSQL must meet the following prerequisites:

All Artifactory servers must be upgraded to version 7.77.3 or later.
Advisory locks require a continuous connection between Artifactory and PostgreSQL.
Note
If you see the following warning message, please consult with JFrog before activating this feature:
```
Detected native locks DB connection dropped! Attempting to re-acquire all previous locks with new connection...
```
Advisory locks cannot be enabled when Artifactory is run with PgBouncer in transaction mode.

Activation

Use the following cURL command to activate advisory locks:

curl -u admin:{password} -H "Content-Type: application/yaml" -d 'forceNativeDbLocksMechanismInAutoMode: true' -XPATCH http://{IP_address}/artifactory/api/system/configuration

No downtime is required to make the transition to advisory locks. However, it is recommended to perform the transition during off-peak times. In addition, it is recommended to perform a rolling restart after activation to prevent excess overhead.

Note

The new advisory locks mechanism does not include Artifactory’s background jobs synchronization. These operations will still use the old distributed_locks table even after the advisory locks mechanism is activated. All other relevant system operations (upload/download, etc.) will be synchronized using the advisory locks mechanism.

Validation

When advisory locks are activated, the following message appears in artifactory-service.log:

"Criteria for native DB locks met"

To view the current status of the advisory locks, enter the following:

select * FROM pg_locks;

Improving Performance in Self-Hosted Environments

Users working in a large, self-hosted environment containing millions of artifacts can improve Artifactory performance by changing the value of the following system property:

artifactory.db.lock.native.pool.size = 5

Increasing the pool size to 5 from its default value can help prevent the system from slowing down during periods of intense activity.