Foreign Key Lock Enhancement

Availability

This feature is available since openGauss 3.0.0.

Introduction

Two types of row locks are added, which are extended from share and update locks to key share, share, no key update, and update locks. A non-primary key update obtains a no key update lock, and a row lock obtained by a foreign key trigger is a 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 non-primary key updates. This feature effectively reduces the blocking of concurrent updates in scenarios with foreign key constraints and 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 locks that do not conflict with each other are added, multiple transactions are not composed of only share locks. Instead, there are multiple combinations of different row locks according to the following conflict table.

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 lock does not support the Ustore table.

Dependencies

None