Here are top concurrency and Locking interview questions in DB2. These questions are asked in many big companies IBM, Dell, L&T, Allstate, Infosys, Wipro etc.
1). What is concurrency in database environment?
A. Database system must ensure that data can be accessed concurrently by the users. Access should be granted while guaranteeing integrity of the data and good performance.
2). When database locking comes into picture?
A. For example, if three different users try to update the same row of data at the same time, how should the database system behave? DB2 utilizes locking mechanisms to handle such scenarios. A DB2 user can choose different locking behaviors depending on the needs of his application—an application that requires estimated values would have a different locking behavior than one that requires precise values.
3) How we dealt locking mechanism in DB2?
A. By using isolation levels
4) When we need to define isolation level?
A. An isolation level we can define both at Pre-compilation time and Bind time.
5) How to define isolation level in pre-compilation time?
A. SET CURRENT ISOLATION we can use to define isolation during pre-compilation time
6). How to define isolation level during bind?
A) During bind, we need to give isolation level either CS/RR etc
7). What is uncommitted read (UR)?
A. Uncommitted read (UR) is the lowest isolation level but provides the highest concurrency to the database applications. When you configure an application to perform uncommitted reads, the application does not acquire any row locks to read data.
UR-Lowest in Isolation. Highest in concurrency.
8) What is Cursor stability (CS)?
A. Cursor stability (CS) is the default DB2 isolation level. This isolation level works well with most applications because it uses a degree of locking sufficient to protect data, and at the same time it also provides a high level of concurrency. As the name of this isolation level implies, it uses a mechanism to provide a stable read on the latest row accessed. DB2 only locks the row currently being read.
9) What is read stability (RS)?
A. Read stability (RS) is another isolation level DB2 uses to protect data. Unlike CS, RS not only locks the current row that is being fetched; it also applies the appropriate locks to all rows that are in the result set. This ensures that within the same transaction, rows that have been previously read cannot be altered by other applications.
10) What is repeatable read(RR) ?
A. Repeatable read (RR) is the highest and most restrictive isolation level. It also gives you the lowest concurrency. Similar to RS, applications with RR force DB2 to lock all the rows in the result set as well as rows that are accessed to build the result set. For example, in a query that involves a two-table join, if DB2 decides to perform table scans on both tables to obtain the result, DB2 would lock all the rows in the two tables. If a row is read by the application using RR, no other application can alter it until the transaction is completed. This ensures that your result set is consistent throughout the duration of the unit of work. One consideration is that the additional locking can greatly reduce concurrency.
RR-Highest isolation. Lowest in concurrency
11). What will happen if we do not apply isolation level during pre-compilation time or Bind time?
A. Nothing will happen. It will take default isolation level.
12) How isolation levels will be stored ?
A. Isolation levels are at Session level not at Database level. So different applications can define different isolation levels. There will be no-conflict.
Related Posts
Srinimf 