Locking in TypeORM involves controlling access to database rows to prevent concurrency issues when multiple transactions or processes try to modify the same data simultaneously. There are different lock modes that determine how a database row is locked, and TypeORM provides methods to apply these lock modes when using the QueryBuilder.

Here’s an in-depth explanation of the locking mechanisms and how to use them with TypeORM:

1. Pessimistic Read Locking (pessimistic_read):

• Locks the selected rows for reading, preventing other transactions from acquiring write locks on those rows until the lock is released.
• Supported by various databases, including MySQL, PostgreSQL, Oracle, and CockroachDB.

2. Pessimistic Write Locking (pessimistic_write):

• Locks the selected rows for writing, preventing other transactions from acquiring both read and write locks on those rows until the lock is released.
• Supported by databases like MySQL, PostgreSQL, and MariaDB.

3. Dirty Read Locking (dirty_read):

• Does not apply any locks, allowing the transaction to read uncommitted changes from other transactions.
• Supported by databases like MySQL with the WITH NOLOCK clause.

4. Optimistic Locking (optimistic):

• Relies on the use of versioning columns (usually annotated with @Version) to detect and prevent concurrent updates.
• Uses optimistic concurrency control by checking the version value before updating a row.
• Works with any database that supports versioning columns.

5. Setting onLocked Behavior:

• Allows you to define how the transaction behaves when it encounters locked rows.
• Common options are nowait (don’t wait for the lock) and skip_locked (skip locked rows and continue with other rows).
• Supported by PostgreSQL and MySQL (for certain lock modes).

Now, let’s illustrate how to use locking in TypeORM’s QueryBuilder using examples:

Pessimistic Read Locking:

const users = await dataSource
.getRepository(User)
.createQueryBuilder("user")
.setLock("pessimistic_read")
.getMany();

Pessimistic Write Locking:

const users = await dataSource
.getRepository(User)
.createQueryBuilder("user")
.setLock("pessimistic_write")
.getMany();

Dirty Read Locking (MySQL with NOLOCK):

const users = await dataSource
.getRepository(User)
.createQueryBuilder("user")
.setLock("dirty_read")
.getMany();

Optimistic Locking (with Version Column):

const users = await dataSource
.getRepository(User)
.createQueryBuilder("user")
.setLock("optimistic", existingUser.version) // existingUser is the loaded entity
.getMany();

Setting onLocked Behavior (PostgreSQL and MySQL 8+):

const users = await dataSource
.getRepository(User)
.createQueryBuilder("user")
.setLock("pessimistic_write")
.setOnLocked("nowait")
.getMany();

Note: The supported lock modes and onLocked behaviors depend on the database you’re using. Be sure to refer to the compatibility matrix in the original code snippet for details.

Locking mechanisms are crucial for maintaining data integrity and handling concurrency scenarios. Choose the appropriate locking mode based on your application’s requirements and database compatibility.

If you have a scenario where data is fetched using a read operation with a lock, and on another connected call data is written using a write operation, the behavior depends on the type of locking used and the timing of the operations. Let’s break down the possibilities:

1. Pessimistic Read Locking (pessimistic_read):
   • When data is fetched using a read operation with a pessimistic read lock, it means that the fetched rows are locked for reading by other transactions.
   • If another connected call attempts to write data while the read lock is held, it will be blocked and will wait until the read lock is released.
   • Once the read operation completes (and the lock is released), the write operation can proceed.
2. Pessimistic Write Locking (pessimistic_write):
   • When data is fetched using a read operation with a pessimistic write lock, it means that the fetched rows are locked for writing by other transactions.
   • If another connected call attempts to write data while the write lock is held, it will be blocked and will wait until the write lock is released.
   • The read operation with the write lock will also block any other read operations (with or without locks) until the write lock is released.
3. Optimistic Locking (optimistic with Version Column):
   • Optimistic locking relies on versioning columns to detect concurrent updates.
   • When data is fetched, the version of the retrieved row is also loaded.
   • If another connected call updates the same row, the version will be changed, and when the original call attempts to update the row, a version conflict will occur.
   • The behavior in case of a version conflict depends on your application’s implementation. Typically, the transaction with the older version will fail, and you’ll need to handle the conflict, possibly by refreshing the data and retrying the operation.

In all cases, locking mechanisms are designed to manage concurrent access to data and prevent issues like dirty writes and lost updates. The behavior you describe—where the fetched row is edited after the fetch—is one of the key scenarios that these locking mechanisms aim to address. The specific outcome will depend on the locking strategy you choose and how your application is designed to handle concurrency and conflicts.

It’s important to carefully consider the locking strategy based on your application’s requirements and the potential for concurrent updates. Additionally, handling concurrency conflicts is a crucial part of implementing a robust application that deals with concurrent access to shared data.