Foreign Key Lock Enhancement

Availability

This feature is available since openGauss 3.0.0.

Introduction

Two types of row-level locks are added, which are extended from the SHARE and UPDATE locks to the KEY SHARE, SHARE, NO KEY UPDATE, and UPDATE locks. The update of a non-primary key obtains the NO KEY UPDATE lock, and the row-level lock obtained by a foreign key trigger is the KEY SHARE lock. The two types of locks do not conflict with each other, thereby improving concurrency of foreign key locks.

Benefits

Most table update operations are updates of non-primary keys. This feature effectively reduces the blocking of concurrent updates in scenarios with foreign key constraints and thus improves efficiency.

Description

When the non-primary key column of a tuple in the parent table is updated, the NO KEY UPDATE lock is obtained. When the corresponding tuple in the child table is updated or inserted, the foreign key trigger is triggered to obtain the KEY SHARE lock of the tuple in the parent table. They do not block each other.

Because row-level locks that do not conflict with each other are added, multiple transactions are not composed of only the SHARE locks. Instead, there are multiple combination modes of different row-level locks according to the following conflicting lock modes.

Lock Mode	KEY SHARE	SHARE	NO KEY UPDATE	UPDATE
KEY SHARE				X
SHARE			X	X
NO KEY UPDATE		X	X	X
UPDATE	X	X	X	X

Enhancements

None.

Constraints

The new row-level locks do not support the Ustore table.

Dependencies

None.