2. PGT Computer Science Question Discussion

2. PGT Computer Science Question Discussion

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

1. Page
2. Table
3. Row
4. Page, table and row level locking allow the same degree of concurrency

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.

So Correct Answer is option (C)