📄 heapscan.out
字号:
0 rows inserted/updated/deletedij(SCANNER)> get cursor scan_cursor as 'select keycol from data';ij(SCANNER)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');0 rows inserted/updated/deletedij(SCANNER)> next scan_cursor;KEYCOL -----------0 ij(SCANNER)> next scan_cursor;KEYCOL -----------10 ij(SCANNER)> -- scan is now positioned on row (10, 100), as it group fetched 2 rows.-- in deleter1 thread delete the last row on the page, but don't commit.-- in the other deleter thread delete the rest of the rows on the page and-- commit it, which will result in a post commit to try and reclaim all the-- rows on the page, but it won't be able to reclaim the one that has not-- been committed by deleter1.-- delete in this transaction keycol (30, 300).set connection deleter1;ij(DELETER1)> delete from data where keycol = 30;1 row inserted/updated/deletedij(DELETER1)> -- delete in this transaction the rest of rows on the page.set connection deleter2;ij(DELETER2)> delete from data where keycol = 0;1 row inserted/updated/deletedij(DELETER2)> delete from data where keycol = 10;1 row inserted/updated/deletedij(DELETER2)> delete from data where keycol = 20;1 row inserted/updated/deletedij(DELETER2)> commit;ij(DELETER2)> -- block deleter threads on a lock to give post commit a chance to run.set connection deleter2;ij(DELETER2)> select * from just_to_block_on;ERROR 40XL1: A lock could not be obtained within the time requestedij(DELETER2)> set connection deleter1;ij(DELETER1)> select * from just_to_block_on;ERROR 40XL1: A lock could not be obtained within the time requestedij(DELETER1)> -- now assume post commit has run, roll back deleter1 so that one non-deleted-- row remains on the page.set connection deleter1;ij(DELETER1)> rollback;ij(DELETER1)> -- the scanner gets a chance to run.set connection scanner;ij(SCANNER)> -- now at this point the scanner will resume and find the row it is positioned-- on has been purged, and it will reposition automatically to (30, 300) on-- the same page.next scan_cursor;KEYCOL -----------30 ij(SCANNER)> next scan_cursor;KEYCOL -----------40 ij(SCANNER)> next scan_cursor;KEYCOL -----------50 ij(SCANNER)> commit;ij(SCANNER)> select * from data;KEYCOL |DATA --------------------------------------------------------------------------------------------------------------------------------------------30 |300 &40 |400 &50 |100 &60 |200 &70 |300 &80 |400 &ij(SCANNER)> commit;ij(SCANNER)> ---------------------------------------------------------------------------------- cleanup--------------------------------------------------------------------------------set connection scanner;ij(SCANNER)> disconnect;ij> set connection deleter1;ij(DELETER1)> disconnect;ij> set connection deleter2;ij(DELETER2)> disconnect;ij> set connection lockholder;ij(LOCKHOLDER)> disconnect;ij> ---------------------------------------------------------------------------------- Test 2: position scanner in the middle of the dataset using group commit-- in a read commited scan which uses zero duration locks. Now arrange-- for the row the scan is positioned on to be purged by post commit,-- but leave a row on the page for scan to reposition to, as did Test 1.-- This time make the row left on the page be deleted, so when the-- scan repositions, it should jump over the deleted row.------------------------------------------------------------------------------------------------- setup---------------connect 'wombat' as deleter1;ij(DELETER1)> connect 'wombat' as deleter2;ij(DELETER2)> connect 'wombat' as scanner;ij(SCANNER)> connect 'wombat' as lockholder;ij(LOCKHOLDER)> -- set upset connection scanner;ij(SCANNER)> set isolation read committed;0 rows inserted/updated/deletedij(SCANNER)> autocommit off;ij(SCANNER)> drop table data;0 rows inserted/updated/deletedij(SCANNER)> -- create a table with 4 rows per page.create table data (keycol int, data varchar(900)) ;0 rows inserted/updated/deletedij(SCANNER)> insert into data values (0, PADSTRING('0',900));1 row inserted/updated/deletedij(SCANNER)> insert into data values (10, PADSTRING('100',900));1 row inserted/updated/deletedij(SCANNER)> insert into data values (20, PADSTRING('200',900));1 row inserted/updated/deletedij(SCANNER)> insert into data values (30, PADSTRING('300',900));1 row inserted/updated/deletedij(SCANNER)> insert into data values (40, PADSTRING('400',900));1 row inserted/updated/deletedij(SCANNER)> insert into data values (50, PADSTRING('100',900));1 row inserted/updated/deletedij(SCANNER)> insert into data values (60, PADSTRING('200',900));1 row inserted/updated/deletedij(SCANNER)> insert into data values (70, PADSTRING('300',900));1 row inserted/updated/deletedij(SCANNER)> insert into data values (80, PADSTRING('400',900));1 row inserted/updated/deletedij(SCANNER)> create unique index idx on data (keycol);0 rows inserted/updated/deletedij(SCANNER)> commit;ij(SCANNER)> set connection deleter1;ij(DELETER1)> set isolation read committed;0 rows inserted/updated/deletedij(DELETER1)> autocommit off;ij(DELETER1)> commit;ij(DELETER1)> set connection deleter2;ij(DELETER2)> set isolation read committed;0 rows inserted/updated/deletedij(DELETER2)> autocommit off;ij(DELETER2)> commit;ij(DELETER2)> set connection lockholder;ij(LOCKHOLDER)> set isolation read committed;0 rows inserted/updated/deletedij(LOCKHOLDER)> autocommit off;ij(LOCKHOLDER)> commit;ij(LOCKHOLDER)> ---------------- run the test--------------set connection lockholder;ij(LOCKHOLDER)> create table just_to_block_on (a int);0 rows inserted/updated/deletedij(LOCKHOLDER)> set connection scanner;ij(SCANNER)> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','2');0 rows inserted/updated/deletedij(SCANNER)> get cursor scan_cursor as 'select keycol from data';ij(SCANNER)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');0 rows inserted/updated/deletedij(SCANNER)> next scan_cursor;KEYCOL -----------0 ij(SCANNER)> next scan_cursor;KEYCOL -----------10 ij(SCANNER)> -- scan is now positioned on row (10, 100), as it group fetched 2 rows.-- In the deleter1 thread delete the last row on the page, but don't commit.-- in the other deleter thread delete the rest of the rows on the page and-- commit it, which will result in a post commit to try and reclaim all the-- rows on the page, but it won't be able to reclaim the one that has not-- been committed by deleter1.-- delete in this transaction keycol (30, 300).set connection deleter1;ij(DELETER1)> delete from data where keycol = 30;1 row inserted/updated/deletedij(DELETER1)> -- delete in this transaction the rest of rows on the page.set connection deleter2;ij(DELETER2)> delete from data where keycol = 0;1 row inserted/updated/deletedij(DELETER2)> delete from data where keycol = 10;1 row inserted/updated/deletedij(DELETER2)> delete from data where keycol = 20;1 row inserted/updated/deletedij(DELETER2)> commit;ij(DELETER2)> -- block deleter threads on a lock to give post commit a chance to run.set connection deleter2;ij(DELETER2)> select * from just_to_block_on;ERROR 40XL1: A lock could not be obtained within the time requestedij(DELETER2)> -- now assume post commit has run, commit deleter1 so that one deleted-- row remains on the page after the positioned row.set connection deleter1;ij(DELETER1)> commit;ij(DELETER1)> -- the scanner gets a chance to run.set connection scanner;ij(SCANNER)> -- now at this point the scanner will resume and find the row it is positioned-- on has been purged, the only rows following it to be deleted and it will -- reposition automatically to (40, 400) on the next page.next scan_cursor;KEYCOL -----------40 ij(SCANNER)> next scan_cursor;KEYCOL -----------50 ij(SCANNER)> next scan_cursor;KEYCOL -----------60 ij(SCANNER)> commit;ij(SCANNER)> select * from data;KEYCOL |DATA --------------------------------------------------------------------------------------------------------------------------------------------40 |400 &50 |100 &60 |200 &70 |300 &80 |400 &ij(SCANNER)> commit;ij(SCANNER)> ---------------------------------------------------------------------------------- cleanup--------------------------------------------------------------------------------set connection scanner;ij(SCANNER)> disconnect;ij> set connection deleter1;ij(DELETER1)> disconnect;ij> set connection deleter2;ij(DELETER2)> disconnect;ij> set connection deleter2;IJ ERROR: No connection exists with the name DELETER2ij> disconnect;IJ ERROR: Unable to establish connectionij> set connection lockholder;ij(LOCKHOLDER)> disconnect;ij> ---------------------------------------------------------------------------------- Test 3: position scanner in the middle of the dataset using group commit-- in a read commited scan which uses zero duration locks. Now arrange-- for the row the scan is positioned on, and all rows following it on-- the page to be purged by post commit, but leave at least one row on-- the page so that the page is not removed. The reposition code will-- position on the page, find the row has disappeared, ask for the-- "next" row on the page, find that no such row exists on the page,-- and finally move to the next page.------------------------------------------------------------------------------------------------- setup---------------connect 'wombat' as deleter1;ij(DELETER1)> connect 'wombat' as deleter2;ij(DELETER2)> connect 'wombat' as scanner;ij(SCANNER)> connect 'wombat' as lockholder;ij(LOCKHOLDER)> -- set upset connection scanner;ij(SCANNER)> set isolation read committed;0 rows inserted/updated/deletedij(SCANNER)> autocommit off;ij(SCANNER)> drop table data;0 rows inserted/updated/deletedij(SCANNER)> -- create a table with 4 rows per page.create table data (keycol int, data varchar(900)) ;0 rows inserted/updated/deletedij(SCANNER)> insert into data values (0, PADSTRING('0',900));1 row inserted/updated/deletedij(SCANNER)> insert into data values (10, PADSTRING('100',900));1 row inserted/updated/deletedij(SCANNER)> insert into data values (20, PADSTRING('200',900));1 row inserted/updated/deletedij(SCANNER)> insert into data values (30, PADSTRING('300',900));1 row inserted/updated/deletedij(SCANNER)> insert into data values (40, PADSTRING('400',900));1 row inserted/updated/deleted⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -