One post tagged with "2pl"

In PostgreSQL, the FOR UPDATE lock is used to explicitly lock rows in a table while performing a SELECT query within a transaction. This lock mode is typically used to ensure that the selected rows do not change until the transaction is completed, preventing other transactions from modifying or locking these rows in a conflicting manner.

For example, it can be used to prevent other customers from changing data while a specific customer is going through the ticket booking process.

The cases we will examine in this article are somewhat special:

• How does select for update behave if there is a mix of locked reads and unlocked reads?
• If a lock is used initially, is it possible for other transactions to read?
• Can consistent reading of data be guaranteed even if reading methods are mixed?

In PostgreSQL, the select for update clause operates differently depending on the transaction isolation level. Therefore, it is necessary to examine how it behaves at each isolation level.

Let’s assume a scenario where data is being modified when the following data exists.