Prevent two connections from reading same row in DB2

 How can user Pick a DB row, and each user get a unique DB row from DB2 Database?

Solution Steps

1. Connection one queries the database table for a row. Reads first row and locks it while reading for update.
2. Connection two queries the database table for a row. Should not be able to read the first row, should read second row if available and lock it for update.
3. Similar logic for connection 3, 4, etc..

If we have 1000 users, and each user should select different row from Table1, we can archive that by add new column LOCKED to Table1 and select the unlocked row then lock it and return Row ID in one step.

SELECT ID FROM FINAL TABLE
(
update Table1
set "LOCKED"=1
where ID in (select ID from Table1 where "LOCKED"=0 FETCH FIRST 1 ROW ONLY)
)

How to get default data after insert new record?

CREATE TABLE EMPSAMP
  (EMPNO     INTEGER GENERATED ALWAYS AS IDENTITY,
   NAME      CHAR(30),
   SALARY    DECIMAL(10,2),
   DEPTNO    SMALLINT,
   LEVEL     CHAR(30),
   HIRETYPE  VARCHAR(30) NOT NULL WITH DEFAULT 'New Hire',
   HIREDATE  DATE NOT NULL WITH DEFAULT);

Retrieving generated column values

SELECT EMPNO, HIRETYPE, HIREDATE  FROM FINAL TABLE
(INSERT INTO EMPSAMP (NAME, SALARY, DEPTNO, LEVEL)                   
VALUES ('Mary Smith', 35000.00, 11, 'Associate'));

Another Example

select data while update data

SELECT SUM(SALARY) INTO :salary FROM FINAL TABLE
(
      UPDATE EMP SET SALARY = SALARY * 1.05    WHERE JOB = 'DESIGNER'
);

DB2 Isolation levels (start from DB2 9.7)

if you inside transaction and update DB and try to run sql that needs to select data before uncommuted updates!!

DB2 has 4 isolation levels

1. Read committed [read stability (RS)]
2. Read uncommitted (UR)
3. Serializable (CS) : CS is specified the transaction will never read data that is not yet committed; only committed data can be read.
4. Repeatable read (RR)

You also can use the WITH parameter on a SELECT statement to set the isolation level of a single SQL statement.

Select count(*) from table1 with RS   --this will count only the committed rows

Select count(*) from table1 with UR  --this will count all rows committed/ and uncommitted rows

Serializable (CS) 

 

CS is specified the transaction will never read data that is not yet committed; only committed data can be read.  Cursor stability is the current default isolation level if none is specified at BIND time.

Repeatable read (RR)

use RR page locking, consider a reporting program that scans a table to produce a detail report, and then scans it again to produce a summarized managerial report. If the program is bound using CS, the results of the first report might not match the results of the second.

If the program used an RR isolation level rather than CS, an UPDATE that occurs after the production of the first report but before the second would not have been allowed. The program would have maintained the locks it held from the generation of the first report and the updater would be locked out until the locks were released.

Read committed [read stability (RS)]

Read stability is similar in functionality to the RR isolation level, but a little less. A retrieved row or page is locked until the end of the unit of work; no other program can modify the data until the unit of work is complete, but other processes can insert values that might be read by your application if it accesses the row a second time.

Read uncommitted (UR)

 The UR isolation level provides read-through locks, also know as dirty read or read uncommitted. Using UR can help to overcome concurrency problems. When you're using an uncommitted read, an application program can read data that has been changed but is not yet committed. UR can be a performance booster, too, because application programs bound using the UR isolation level will read data without taking locks.

for more information

https://www.ibm.com/docs/en/db2/11.5?topic=issues-isolation-levels