Question

1. Locking methods are one of the most common techniques used in concurrency control. There are different levels of lock use.

Make sure you clearly write down (a), (b) followed by your answers.

[6 marks] (a) Use the following scenario to illustrate and explain how a table-level lock and a page-level lock work.

Scenario:

Transaction 1 (T1) wants to update row 5 in Table A

Transaction 2 (T2) wants to update row 2 in Table A

Note: Page 1 contains rows 1-3 in Table A and Page 2 contains rows 4-6 in Table A

[4 marks] (b) Compare the advantages and disadvantages of a table-level lock and a page-level lock.

Answer 1

Answer Question (a) Use the following scenario to illustrate and explain how a table-level lock and a page-level lock work.

Scenario:

Transaction 1 (T1) wants to update row 5 in Table A

Transaction 2 (T2) wants to update row 2 in Table A

Note: Page 1 contains rows 1-3 in Table A & Page 2 contains rows 4-6 in Table A

Using Page Lock	Using Table Lock
Transaction 1	Transaction 2	Transaction 1	Transaction 2
X_LOCK(P2)	X_LOCK(P1)	X_LOCK(Table A)
Access Row 5	Access Row 2	Access Row 5
X_UNLOCK(P2)	X_UNLOCK(P1)	X_UNLOCK(Table A)
			X_LOCK(Table A)
			Access Row 2
			X_UNLOCK(Table A)

Answer to Question (b) Compare the advantages and disadvantages of a table-level lock and a page-level lock.

Characteristics of Table-level locking are:

Ø Table-level locking systems always lock entire tables.

Ø A transaction using a table-level locks will hold shared and/or exclusive table locks

Characteristics of Page-level locking are:

Ø Page-level locking systems always lock only the entire page of table that is currently required.

Ø A transaction using a page-level locks will hold shared and/or exclusive page locks

In the lock hierarchy, the table level hierarchy is the higher-level lock as compared to the page-level lock. Thus, page-level locking is expected to be better (in terms of the number of rows blocked) because when you lock on a larger table, you are locking more data. However, this is true with smaller tables, with very limited number of pages. Page-level can be slower than table-level locks when used on a large part of the table because of the following reasons:

Ø Require more memory than table-level locks.

Ø Needs to acquire many more locks as compared to table-level locks.

Ø Not suitable for table operations involving access to large number of table rows or pages.

A Table-level lock is better for a large table where major data modifications are taking place. This lets the system contend with a single lock on the table rather than having to deal with one for each page. Table locks are superior to page-level in the following cases:

Ø Most statements for the table are reads.

Ø Read and updates on strict keys.

Ø SELECT combined with concurrent INSERT statements.

Ø Many scans or GROUP BY operations on the entire table without any writers.

The RDBMS automatically escalates locking levels internally.