In scenario when sql has exclusive lock on a columnar organized
table, and then issued an uncommitted INSERT in that same table,
a concurrent SELECT statement on the same table which is run in
another session could return incorrect results.
Since the table is under an exclusive lock, the SELECT statement
waits.
Once the session that issued the uncommitted INSERT committed,
the exclusive lock is released,
which in turn allows the SELECT statement in the other session
to be executed.
The symptom that select could encounter: results from the
SELECT statement are not including the uncommitted INSERTS that
should have committed prior to the SELECT statement being
executed.
Setup:
db2 "create table columnartable (i int) organize by column"
db2 insert into columnartable values 1,2,3,4,5
[Session 1]
In one transaction:
lock table columnartable in exclusive mode;
delete from columnartable;
!sleep 10;
insert into columnartable values 1,2,3,4,5;
COMMIT;
[Session 2]
select count(*) from columnartable while session 1 is running
(during the 10 seconds sleep)
Observation: session 2 waited for session 1 to complete (as
session 2 was waiting for the exclusive table lock to be
released by session 1), and returned row count of 0 rows
Correct result is 5 rows. |
