How can user Pick a DB row, and each user get a unique DB row from DB2 Database?
Solution Steps
- Connection one queries the database table for a row. Reads first row and locks it while reading for update.
- 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.
- 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
- Read committed [read stability (RS)]
- Read uncommitted (UR)
- Serializable (CS) : CS is specified the transaction will never read data that is not yet committed; only committed data can be read.
- 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
