Artifactory Advisory Locks Configuration for PostgreSQL

Starting with Artifactory 7.77.3, self-hosted users with PostgreSQL can use a database locking mechanism based on advisory locks instead of the previous mechanism based on distributed table locks.

What Are Advisory Locks

Advisory locks (also referred to as native locks) are a PostgreSQL feature that enables applications such as Artifactory to create locks enforced by the application, not by PostgreSQL.

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

Prerequisites

To implement advisory locks for PostgreSQL:

• 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, consult JFrog before you activate this feature:

Text

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 transition to advisory locks. We recommend performing the transition during off-peak times, and performing a rolling restart after activation to prevent excess overhead.

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;

📘

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.

select * FROM pg_locks;

Improving Performance in Self-Hosted Environments

Users in large, self-hosted environments containing millions of artifacts can improve Artifactory performance by setting the system property artifactory.db.lock.native.pool.size to 5. Increasing the pool size to 5 from its default value helps prevent system slowdown during periods of intense activity.