Introduction to DB2 lock attributes

The attributes of DB2 locks are still unfamiliar to many new users who are new to DB2 databases. The following describes the attributes of DB2 locks in detail and hopes to help you.

In the chapter on concurrency and isolation level, we can see that DB2 Universal Database isolates transactions from each other by using locks. A lock is a mechanism used to associate data resources with a single transaction. Its purpose is to associate a resource with a transaction that owns it, controls how other transactions interact with the resource. The transaction associated with the locked resource holds or owns the lock .) The DB2 database manager uses locks to prevent transactions from accessing uncommitted data written by other transactions unless the "uncommitted read" isolation level is used ), other transactions are prohibited from updating these rows when the transaction with the lock uses the restricted isolation level. Once a transaction acquires a lock, it holds the lock until it is terminated. When the transaction terminates, it releases the lock and other transactions can use the unlocked data resources.

If a transaction attempts to access data resources in a way that is incompatible with the lock held by another transaction, we will study the lock compatibility later), the transaction must wait until the transaction with the lock is terminated. This is called lock wait. When a lock wait event occurs, all the transactions that attempt to access the data resources must stop the execution until the transaction with the lock is terminated and the incompatible lock is released.

All locks have the following basic DB2 lock attributes:

Object: the object attribute identifies the data resource to be locked. The DB2 database manager locks data resources such as tablespaces, tables, and rows as needed ).

Size: Specifies the physical size of the data resource to be locked. The lock does not always have to control the entire data resource. For example, DB2 database manager allows an application to exclusively control specific rows in a table, rather than letting the application exclusively control the entire table.

Duration: The duration Attribute specifies the length of time the lock is held. The isolation level of transactions usually controls the lock duration.

Mode: the mode attribute specifies the access type permitted by the lock owner and the access type permitted by concurrent users who lock data resources. This attribute is usually called the lock status.

Solution to db2 tablespace lock

SQL statement for creating tables in DB2 with Constraints

It is not allowed to set the db2 primary key to solve the problem.

How to Create a tablespace in DB2

Introduction to the DB2 user group in Linux