GATE IT 2004 | Question: 21

Question

Which level of locking provides the highest degree of concurrency in a relational database ?

• A. Page
• B. Table
• C. Row
• D. Page, table and row level locking allow the same degree of concurrency

Comments

References : Navathe ( same concept also in Korth )

Note : Read the highlighted part 

Answer 1

Row level locking provides more concurrency, because different transactions can access different rows in a table / page at same time,

Correct Answer: $C$

Comments

Finer the granularity higher the concurrency and overhead.

---

I might be saying the same thing differently. But I believe the degree of concurrency in the context of DBMS is the number of transactions that can run concurrently.

If a transaction locks a complete table then a higher number of transactions would be blocked compared to the transaction locking a few rows of the table.

---

Nicely framed @mayankso :)

Answer 2

 Page level locking locks whole page i.e., all rows therefore highly restrictive.
 Table level locking is mainly used for concurrency control with DDL operations.
 A row share table lock is the least restrictive, and has the highest degree of concurrency for a table. It indicates the transaction has locked rows in the table and intends to update them.

Comments

Nice explanaiton !!!

---

very well explained

Answer 3

Correct Ans is option (C)

Explanation:

A database lock is used to “lock” some data in a database so that only one database user/session may update that particular data. So, database locks exist to prevent two or more database users from updating the same exact piece of data at the same exact time. When data is locked, then that means that another database session can NOT update that data until the lock is released (which unlocks the data and allows other database users to update that data. Locks are usually released by either a ROLLBACK or COMMIT SQL statement.

Database locking techniques
Database locks can actually be placed at different levels – also known as lock granularity – within the database.

Database level locking in lowest to highest order of degree of concurrency

With database level locks, the entire database is locked – which means that only one database session can apply any updates to the database. It has the lowest Degree of concurrency

File level locking
With a file lock level, an entire database file is locked. What exactly is a file in a database? Well, a file can have a wide variety of data – inside a file there could be an entire table, a part of a table, or even parts of different tables. 

Table level locking

It means that an entire table is locked as a whole. This lock level use when making a change that affects an entire table, like updating all the rows in a table, or modifying the table to add or remove columns. In Oracle, this is known as a DDL lock, because it’s used with DDL (Data Definition Language) statements like CREATE, ALTER, and DROP – basically statements that modify the entire table somehow or the other.

Page or block level locking
Block, or page, level locking occurs when a block or page that is part of a database file is locked. Because the data that can be stored in blocks/pages can be wide and varied, page/block locking is less favored in databases today.

Column level locking

A column level lock just means that some columns within a given row in a given table are locked. This form of locking is not commonly used because it requires a lot of resources to enable and release locks at this level. Also, there is very little support for column level locking in most database vendors.

Row level locking
A row level lock applies to a row in a table. This is also the most commonly locking level, and practically all major database vendors support row level locks. It has highest level of degree of concurrency.

Comments

Nice ans

Answer 4

BEST ANSWER

Database concurrency is a unique characteristic enabling two or more users to retrieve information from the database at the same time without affecting data integrity

Degree of Concurrency. • The number of tasks that can be executed in parallel is the degree of concurrency of a decomposition. 

                                      • the maximum degree of concurrency is the maximum number of such tasks at any point during execution.

Row-level locking. This type of locking allows multiple transactions to access different rows of the same table concurrently, minimizing conflicts and maximizing throughput

Answer 5

https://en.wikipedia.org/wiki/Record_locking

A higher degree of granularity is achieved if each individual account may be taken by a clerk. This would allow any customer to be serviced without waiting for another customer who is accessing a different account. This is analogous to a record level lock and is normally the highest degree of locking granularity in a database management system.

In a SQL database, a record is typically called a "row."

Answer 6

The correct answer is (c) Row, as row-level locking provides the highest concurrency because it locks only the specific row being accessed, allowing other transactions to freely access different rows in the same table or page, offering the finest granularity of control compared to page or table locking.

Row-Level Locking (c): Most granular, locking individual rows. Best concurrency.

Page-Level Locking (a): Locks a whole page of rows. More restrictive than row-level.

Table-Level Locking (b): Locks the entire table. Least concurrent, used for DDL.