For Update Support Outer Join

Availability

This feature is available since MogDB 5.0.0.

Introduction

This feature mainly supports locking the NULL-side table in a outer join (left join, right join, full join), but not locking the NULL-side table if the corresponding row is NULL.

Benefits

Supports For Update for locking outer join.

Description

Usually, Select statement will not lock the data, the query process will not have an impact on other DML and DDL operations. However, in daily use, we often encounter business-level scenarios that require data exclusivity, such as train booking scenarios, the screen shows that there is a ticket, if the query process does not lock the data exclusively, when the ticket is really issued, the data may have been modified by other clients, and there is no ticket to be issued. In this case, it is necessary to lock the data during the query process to monopolize the data and ensure that it is not modified by other data.

For update is a kind of row-level lock, once the user of a row of row-level locking, the user can query and update the locked data rows, other users can only query the data can not be modified, until the row of data is released until the lock. Therefore, the select...for update statement is commonly used to manually lock query statements. This feature supports for update to lock the NULL side of an outer join.

Example

-- create a table
CREATE TABLE tt01 (a INT, b INT);
CREATE TABLE tt02 (a INT, b INT);

-- 1)The two tables in the inner join are locked, the locking is successful, and no other transaction can modify it until the transaction ends.
-- Transaction 1:
SELECT * FROM tt01 INNER JOIN tt02 ON tt01.a = tt02.a FOR UPDATE;
-- Transaction 2:
UPDATE tt02 SET b = 200 WHERE a = 2;

-- The two left-joined tables are locked, the locking is successful, and no other transaction can modify them until the transaction ends.
-- Transaction 1:
SELECT * FROM tt01 LEFT JOIN tt02 ON tt01.a = tt02.a FOR UPDATE;
-- Transaction 2:
UPDATE tt02 SET b = 200 WHERE a = 2;

-- A lock is applied to the two right-joined tables, the lock is successful, and no other transaction can modify it until the transaction ends.
-- Transaction 1:
SELECT * FROM tt01 RIGHT JOIN tt02 ON tt01.a = tt02.a FOR UPDATE;
-- Transaction 2:
UPDATE tt01 SET b = 200 WHERE a = 2;

-- A lock is applied to two tables that are fully connected, the lock is successful, and no other transaction can modify it until the transaction is finished.
-- Transaction 1:
SELECT * FROM tt01 FULL JOIN tt02 ON tt01.a = tt02.a FOR UPDATE;
-- Transaction 2:
UPDATE tt01 SET b = 200 WHERE a = 2;