Learn about the DB2 lock types

What types of DB2 locks are available? This is a problem that many people have mentioned. The following describes the DB2 lock types in detail and hopes to help you learn the DB2 lock.

The lock status determines the type of access allowed by the lock owner and the type of access allowed by concurrent users who lock data resources. The following list shows the available lock statuses, sorted by incremental control:

Lock status mode): Intent None, IN)
Target objects: tablespaces and tables
Description: The lock owner can read data from the locked table, including uncommitted data. However, the data cannot be changed. In this mode, the lock owner does not obtain row-level locks. Therefore, other concurrent applications can read and change table data.

Lock status mode): Intent Share, IS)
Target objects: tablespaces and tables
Description: The lock owner can read the data in the locked table, but cannot modify the data. Similarly, because the lock owner does not obtain the row-Level Lock, other concurrent applications can still read and change the data in the table. When a transaction has an intention to share the lock on the table, it will share the lock on each row it reads .) This lock is obtained when the transaction does not convey the intention to update the row in the table.

Lock status mode): one-click Next Key Share, NS)
Target objects: rows
Description: lock owner and all concurrent transactions can be read but cannot be changed.) Lock data in the row. This lock is used to replace the shared lock on data read at the transaction isolation level using "read stability" or "cursor stability.

Lock status mode): Shared S)
Target objects: Tables and rows
Description: The lock owner and any other concurrent transactions can read but cannot be changed.) data in the locked table or row. As long as the table is not locked by a shared lock, a single row in the table can be locked by the shared lock. However, if the table is locked by share, the lock owner cannot obtain the row-level shared lock from the table. If the table or row is locked by a shared lock, other concurrent transactions can read data, but cannot modify it.

Lock status mode): Intent Exclusive, IX)
Target objects: tablespaces and tables
Description: The lock owner and any other concurrent applications can read and modify data in the locked table. When the lock owner reads data from the table, it obtains a shared lock on each row it reads, and obtains updates and mutex locks on each row it updates. Other concurrent applications can read and update locked tables. This lock is obtained when the transaction conveys the intention to update the row in the table. Select for update, UPDATE... WHERE and INSERT statements convey the UPDATE intent .)

Lock status mode): Share With Intent Exclusive, SIX)
Target objects: tables
Description: The lock owner can read and change data in the locked table. The lock owner acquires the mutex lock on the row it updates, but does not obtain the lock on the row it reads. Therefore, other concurrent applications can read but cannot update the data in the locked table.

Lock status mode): Update, U)
Target objects: Tables and rows
Description: The lock owner can update the data in the locked table, and the lock owner automatically obtains the mutex lock on any row it updates. Other concurrent applications can but cannot update the data in the locked table.

Lock status mode): the Next Key Exclusive, NX)
Target objects: rows
Description: The lock owner can read but cannot update the locked row. When a row is inserted or deleted in the index of a table, the lock is obtained on the next row of the table.

Lock status mode): Weak mutual exclusion Next Key Weak Exclusive, NW)
Target objects: rows
Description: The lock owner can read but cannot update the locked row. When a row is inserted into an index of a non-directory table, this lock is obtained in the next row of the table.

Lock status mode): Exclusive, X)
Target objects: Tables and rows
Description: The lock owner can read and change the data in the locked table or row. If a mutex lock is obtained, only applications at the "uncommitted read" isolation level are allowed to access the locked table or multiple rows ). The mutex lock is obtained for data resources operated using INSERT, UPDATE, and/or DELETE statements.

Lock status mode): Weak mutual exclusion Weak Exclusive, WE)
Target objects: rows
Description: The lock owner can read and change the locked row. This lock is obtained on this row when a row is inserted into a non-directory table.

Lock status mode): Super mutex Super Exclusive, Z)
Target objects: tablespaces and tables
Description: The owner of a lock can modify, delete, create, or delete an index. When a transaction attempts to execute any of the preceding operations, the table automatically acquires the lock. Before removing the lock, other concurrent transactions are not allowed to read or update the table.

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