Read Locks and Write Locks

Question

I am a little unsure about read and write locks and just need someone to check if these facts about read/write locks are correct.

This is in reference to databases in general.

Read Locks:

1. Multiple read locks can be acquired by multiple threads at the same time.
2. When a thread has a read lock on a row/table, no thread can update/insert/delete data from that table. (Even if the thread trying to write data doesn't require a write lock.)
3. A row/table cannot have a read and a write lock at the same time.

Write Locks:

1. When a row/table has a write lock, it cannot be read by another thread if they have a read lock implemented in them but can be read by other threads if no read lock is implemented (i.e simple Select query)

Thanks for the clarification. I cant find direct assertions to these statements on the internets.

Answer 1

In database management theory, locking is used to implement isolation among multiple database users. This is the "I" in the acronym ACID (Atomicity, Consistency, Isolation, Durability). Locks are applied by a TX (transaction) to data, which may block other TXs from accessing the same data during the TX's life.

Simple Locking: Two main types of locks can be requested:

• Shared lock: Read lock i.e. Any other TX can read but not write
• Exclusive lock: Write lock i.e. No other TX can read or write

Multiple Locking: Two Phase Locking (2PL) is a concurrency control method that guarantees serializability.

• A Growing/Expanding/First Phase: locks are acquired and no locks are released.
• A Shrinking/Contracting/Second Phase: locks are released and no locks are acquired.

Answer 2

Read Locks:

1. Multiple read locks can be acquired by multiple threads at the same time.

   True. Multiple read locks can exist at the same time. (Read lock has another name: Shared lock)

2. When a thread has a read lock on a row/table, no thread can update/insert/delete data from that table. (Even if the thread trying to write data doesn't require a write lock.)

   True. The write transaction should wait for read locks to finish reading.

3. A row/table cannot have a read and a write lock at the same time.

   True. If you have the write lock before the read lock, the write lock will block other transactions to read or write the same table. If you have the read lock before the write lock, the read lock will block the write transactions until the reading transaction finishes.

Write Locks:

1. When a row/table has a write lock, it cannot be read by another thread if they have a read lock implemented in them but can be read by other threads if no read lock is implemented (i.e simple Select query)

   Sorry, I don't understand this statement. But when a table has a write lock (Exclusive Lock), it can not be read or written by another transaction.

Answer 3

It depends on the isolation level used.