Chapter 7. Database Concurrency

Eleven percent (11%) of the DB2 UDB V8.1 Family Fundamentals certification exam (Exam 700) is designed to test your knowledge of the instruments DB2 Universal Database uses to allow multiple users and/or applications to interact with a database simultaneously without negatively affecting data consistency. The questions that make up this portion of the exam are intended to evaluate the following:

• Your ability to identify the appropriate isolation level to use for a given situation.

• Your ability to identify the characteristics of DB2 UDB locks.

• Your ability to list objects for which DB2 UDB locks can be acquired .

• Your ability to identify factors that can influence locking.

This chapter is designed to introduce you to the concept of data consistency and to isolation levels and locks ”the mechanisms DB2 Universal Database uses to maintain data consistency in both single- and multiple- user database environments.

Terms you will learn:

Data Consistency

Inconsistency

Interleaved Transaction

Serializable Transaction

Concurrency

Lost Update

Dirty Read

Nonrepeatable Read

Phantom

Isolation Level

Repeatable Read

Read Stability

Cursor Stability

Uncommitted Read

Lock

Lock State

Lock Compatibility

Lock Conversion

Lock Escalation

Lock Wait

Deadlock Cycle

Conversion Deadlock

Techniques you will master:

Understanding how activities performed by transactions are isolated from each other in a multiuser environment.

Understanding how DB2 Universal Database provides concurrency control with isolation levels and locks.

Recognizing the types of isolation levels available and understanding when each is to be used.

Understanding what types of locks are available and how locks are acquired.