Question

lock granularity specifies the level of lock use. List and explain the four levels of lock granularity?

Answer 1

A lock granularity guarantees exclusive use of a data item to a current transaction. It refers to how much of data is locked at a particular time. Granularity refers to the size of the data item allowed to lock Four levels levels of lock granularity 1. Database 2. Table 3. Page 4. Row 1.database level lock It refers to the entire database lock preventing the use of any tables in the database by transaction T2 while transaction Tl is being executed. It is ideal for batch processing. Database level locks are used to prevent concurrent updates to the database while running 2. Table level lock With table-level locking, when a transaction locks data in order to prevent any transaction anomalies, it always locks the entire table, not just those rows being accessed. Table-level locking works best with single-user applications or read-only applications. A transaction using a table level lock will hold shared and/or exclusive table locks

3. Page level lock In a page-level lock, the DBMS will lock an entire disk page. A disk page, or page, is the equivalent of a disk block, which can be described as a directly addressable section of a disk. A page has a fixed size, such as 4K, 8K, or 16K. For example, if you want to write only 80 bytes to a 4K page, the entire 4K page must be read from disk, updated in memory, and written back to disk. A table can span several pages, and a page can contain several rows of one or more tables. Page-level locks are currently the most frequently used multi user DBMS locking method. Note that T1 and T2 access the same table while locking different diskpages. If T2 requires the use of a row located on a page that is locked by T1, T2 must wait until the page is unlocked by T1. 4.Row level lock Row-level locking uses more memory but maximizes a greater level of concurrency, which works better in multi-user systems. The DBMS allows concurrent transactions to access different rows of the same table even when the rows are located on the same page.