Locks, Latches, Claims, & Drains Flashcards by Nicholas Peterson

Is locking more complicated in a Data Sharing Environment?

Yes

How well did you know this?

Not at all

Perfectly

Locking exists to ensure what?

Data Integrity

How well did you know this?

Not at all

Perfectly

What does A.C.I.D stand for & what do each mean?

Atomic - “All or Nothing” for UOW
Consistent - Database consistent after Commit
Isolation - Concurrent transactions not inter-dependent
Durable - Updates persistent once committed

How well did you know this?

Not at all

Perfectly

How much does memory cost in a non Data Sharing Env.?

Around 500-600 bytes of memory

How well did you know this?

Not at all

Perfectly

Regarding locks what is ideal?

Hold the lowest number of small locks for the least time

How well did you know this?

Not at all

Perfectly

What does IRLM stand for?

Internal Resource Lock Manager

How well did you know this?

Not at all

Perfectly

What is the IRLM address space naming format?

ssidIRLM

How well did you know this?

Not at all

Perfectly

Where are the locks held in a data sharing environment?

Coupling Facility

How well did you know this?

Not at all

Perfectly

Theoretical limit for a single DB2 subsystem?

Around 100m locks

How well did you know this?

Not at all

Perfectly

A typical 2GB IRLM hold just over how many locks?

3 million locks

How well did you know this?

Not at all

Perfectly

What are the 4 essential locking concepts?

Scope - How much data is locked
Mode - What type of lock has been requested
Duration - how long the lock is held
Isolation - Describe the impact of UOW on each other & works both ways - a UOW can impact and/or ignore others

How well did you know this?

Not at all

Perfectly

What is the Locking Hierarchy for Simple & Segmented?

Row/Page -> Table (Segmented Only) -> Tablespace

How well did you know this?

Not at all

Perfectly

What is the Locking Hierarchy for Partitioned & UTS?

Row/Page -> Partition

How well did you know this?

Not at all

Perfectly

What is LOCKMAX?

The maximum number of locks per user to acquire for the TB or TS before escalating to the next locking level.

How well did you know this?

Not at all

Perfectly

What is NUMLKTS?

Is a subsystem parm that specifies the default maximum number of page, row, or LOB locks that an application can hold simultaneously in a table or table space. If a application exceeds the maximum number of locks in a single TB or TS, lock escalation occurs. (Across all objects)

How well did you know this?

Not at all

Perfectly

What are the 3 Locks Modes for Page & Row Level?

S - (Shared) - Select, Fetch, Some Open Cursor
X - (Exclusive) - Any SQL that modifies data - Insert, Update, Delete, Merge
U - (Update) - Fetch for Update - Promoted to an X lock when the data is modified - Helps prevents deadlocks

What is an Intent Lock?

DB2 always places an Intent lock on all higher level objects in the locking hierarchy before using a lower level lock. — When a page or row is locked, the table, partition, or table space that contains it is also locked. This intent lock indicates the plan that the application process has for accessing the data.

Where can I find the maximum wait time for a lock?

DSNZPARM IRLMRWT - Default is 30 seconds - CS is 10 (which means 10 seconds)

What is the actual maximum time a lock could wait (for non data sharing environments) ?

IRLMRWT + Deadlock detection time

What is a deadlock?

A deadlock is a situation in which two computer programs sharing the same resource are effectively preventing each other from accessing the resource, resulting in both programs ceasing to function.

What is the Deadlock Detection Cycle DSNZPARM?

DEADLOCK - Default is 1 second

What is the Deadlock Detection Cycle DSNZPARM?

DEADLOCK - Default is 1 second

What is the one scenario at which DB2 will choose to abort if there is a deadlock?

If one of the objects is NOT LOGGED DB2 will choose the other so it doesn’t end up in a recovery situation.

What are 3 ways to avoid Deadlocks?

1 - Ensure you access objects in the same order
2 - Use FOR UPDATE clause when you know you’re going to Update
3 - Row locking can help but it causes increased locking

How are Locks Controlled for lower level locks (Page & Row) in App?

Controlled by SQL COMMIT

RELEASE(COMMIT) releases what kind of locks at COMMIT?

Releases TS, TB, & Partition locks - Except those needed to maintain Cursor position if WITH HOLD used . -- In this case gross locks may be downgraded back to Intent Locks -- For Ex. if Lock Escalation has occurred.

RELEASE(DEALLOCATE) ?

Releases resources when thread terminates & cheaper but naturally holds locks for longer

What does PKGREL_COMMIT do if package is set to RELEASE(DEALLOCATE) ?

1. At COMMIT DB2 checks for waiters on the Package lock. 2. If any exist temporarily switches Package to RELEASE(COMMIT) 3. Allows (RE)REBIND, DDL, or REORG to proceed. 4. Restrictions involved but detailed discussion is out of scope. (CS = YES)

What are the two aspects of Isolation?

1. What you do if you encounter locks taken by another UOW | 2. What others can do to data you've read but not updated.

What are the 4 Isolation Options?

1. RR - Repeatable Read 2. RS - Read Stability 3. CS - Cursor Stability 4. UR - Uncommitted Read (also NC - No Commit)

What is RR?

You can only read fully committed updates. All your locks are retained until COMMIT DB2 locks anything it touches including any non-qualifying rows Re-reading Cursor within a UOW guaranteed to return identical results

What is RS?

Similar to RR but locks are only held on qualifying rows. Others can read and update non-qualifying rows as well as INSERT data Re-reading Cursor will get at least the same data but potentially more

What is CS?

You can only read fully committed updates. -- Any data you read will be both consistent and persistent Your non-exclusive locks are only held while CURRENT OF Cursor -- Released when you Fetch the next row (depends on CURRENTDATA) X-Locks are retained until COMMIT If you re-read the same row twice within a UOW it may have changed

What is UR?

You can read any data, even if locked, as well as uncommitted Updates. -- Data could be inconsistent and not persistent. You take no Read locks so others can read and update same data. Isolation CS used for any Updates to ensure data integrity Important to note UR does NOT mean no locking. -- Claims are still used, along with Update, XML and LOB locks!

What is a fuzzy read?

Fuzzy or non-repeatable reads: Fuzzy reads occur when a database transaction re-reads data it has already read and then finds that another committed transaction has modified or deleted the same data.

What does CURRENTDATA only relate to?

ISOLATION(CS)

What is CURRENTDATA?

Determines locking for Read Only or Ambiguous Cursors Yes = Retains Locks No = Releases locks as normal If Yes, It will not release locks with CS (when cursor is done with the row)

What is recommended for CURRENTDATA? Yes or No?

Does DB2 lock Indexes?

No, locks are only taken on underlying data pages or rows.