- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Explain the concept of LOCK PROMOTION with the help of an example
A DB2 LOCK PROMOTION is defined as the process of acquiring more restrictive locks on a particular resource. DB2 uses LOCK PROMOTION for the concurrent processes which are trying to access the same DB2 resource. Basically, there are three types of locks.
The concurrent processes can place a shared lock on a resource (DB2 table, row, page, etc) but cannot update the data. In order to update the data, concurrent processes have to promote their lock to UPDATE.
The concurrent process can read the data but cannot update it. Update lock indicates that the process is ready to update the resource but in order to make an actual update, any one of the processes can promote its lock to exclusive.
Only one process can hold exclusive lock on a resource at a time. The lock owner can read and update the resource. Other concurrent processes cannot acquire any lock in the resource neither can it access the resource.
We shall understand this with the following example.
If there are two COBOL-DB2 programs — PROG A and PROG B which are reading the same row of ORDERS DB2 table. Both the programs will acquire SHARED LOCK on the page in which that DB2 table row resides. Now PROG A wants to update this row then it will acquire an UPDATE LOCK on the page, this is known as LOCK PROMOTION as the lock is promoted from SHARED LOCK to UPDATE LOCK.
Further, if PROG A wants to update the ORDERS table, it will wait until PROG B releases the SHARED lock from the ORDERS table. As soon as PROG B releases its SHARED lock, PROG A will promote its UPDATE lock to EXCLUSIVE lock to update the table. Till the time PROG A holds the EXCLUSIVE lock in ORDERS DB2 table no other process or program can acquire any lock (shared, update or exclusive) on this table.
- Explain the concept of DYNAMIC SQL in DB2 with the help of an example
- Explain the concept of primary key with an example (DBMS)?
- Explain the concept of key attributes with an example in DBMS?
- Explain join operations with the help of an example in DBMS
- What is the purpose of COALESCE function? Explain with the help of an example.
- Explain the concept of magnetic disk with the help of a diagram(DBMS)
- Explain SHARED, UPDATE and EXCLUSIVE locks with the help of an example
- What are COLUMN functions in DB2? Explain with the help of an example
- What is the use of the VALUE function in a DB2? Explain with the help of an example
- What is the difference between DB2 JOIN and UNION? Explain with the help of an example
- What is Fixed-list SELECT? Give the syntax and explain with help of an example
- Explain the concept of amalgamation
- What is CLUSTERED INDEX in DB2? Explain with the help of practical example.
- Explain the concept of DBMS schema with examples?
- Purpose and usage of subqueries in DB with the help of an example