📄 rlliso2multi.out
字号:
ij(CLIENT_2)> ---------------------------------------------------------------------------------- Test 3: make sure an exact key insert into unique key index blocks.--------------------------------------------------------------------------------set connection client_1;ij(CLIENT_1)> autocommit off;ij(CLIENT_1)> create table a (a varchar(1000), b varchar(1000)) ;0 rows inserted/updated/deletedij(CLIENT_1)> create unique index a_idx on a (a) ;0 rows inserted/updated/deletedij(CLIENT_1)> commit;ij(CLIENT_1)> insert into a values (PADSTRING('b',1000), PADSTRING('b',1000));1 row inserted/updated/deletedij(CLIENT_1)> set connection client_2;ij(CLIENT_2)> autocommit off;ij(CLIENT_2)> ---------------------------------------------------------------------------------- the following should cause a time out, as the previous-- key lock will conflict with client_1's lock on 'b'--------------------------------------------------------------------------------insert into a values (PADSTRING('b',1000), PADSTRING('b',1000));ERROR 40XL1: A lock could not be obtained within the time requestedij(CLIENT_2)> ---------------------------------------------------------------------------------- Test 4: make sure that row lock wait in a heap scan works--------------------------------------------------------------------------------set connection client_1;ij(CLIENT_1)> autocommit off;ij(CLIENT_1)> create table test_4 (a int, b varchar(1000), c varchar(1000)) ;0 rows inserted/updated/deletedij(CLIENT_1)> commit;ij(CLIENT_1)> set connection client_2;ij(CLIENT_2)> autocommit off;ij(CLIENT_2)> commit;ij(CLIENT_2)> -- client_1 will get a single row lock in the heap.set connection client_1;ij(CLIENT_1)> insert into test_4 values (1, PADSTRING('a',1000), PADSTRING('b',1000));1 row inserted/updated/deletedij(CLIENT_1)> -- client_2 scans table, blocking on a row lock on the client_1 insert row, -- will get timeout message.set connection client_2;ij(CLIENT_2)> select * from test_4;A |B |C -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ERROR 40XL1: A lock could not be obtained within the time requestedij(CLIENT_2)> -- release the insert lock.set connection client_1;ij(CLIENT_1)> commit;ij(CLIENT_1)> -- reader should be able to see row now.set connection client_2;ij(CLIENT_2)> select * from test_4;A |B |C -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 |a &|b &ij(CLIENT_2)> commit;ij(CLIENT_2)> -- cleanupset connection client_1;ij(CLIENT_1)> drop table test_4;0 rows inserted/updated/deletedij(CLIENT_1)> commit;ij(CLIENT_1)> ---------------------------------------------------------------------------------- Test 5: make sure a that a group fetch through a secondary index correctly-- handles a row that is deleted after it has read a row from the index-- but before it has read the row from the base table.------------------------------------------------------------------------------------------------------------------------------------------------------------------ Test setup - create a 1 page btre, with the page ready to split.--------------------------------------------------------------------------------connect 'wombat' as scanner;ij(SCANNER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.NoHoldForConnection;ij(SCANNER)> connect 'wombat' as deleter;ij(DELETER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.NoHoldForConnection;ij(DELETER)> set connection scanner;ij(SCANNER)> autocommit off;ij(SCANNER)> create table test_5 (a int, a2 int, b varchar(1000), c varchar(1000)) ;0 rows inserted/updated/deletedij(SCANNER)> insert into test_5 values (1, 10, PADSTRING('a',1000), PADSTRING('a',1000));1 row inserted/updated/deletedij(SCANNER)> insert into test_5 values (2, 20, PADSTRING('b',1000), PADSTRING('b',1000));1 row inserted/updated/deletedij(SCANNER)> insert into test_5 values (3, 30, PADSTRING('c',1000), PADSTRING('c',1000));1 row inserted/updated/deletedij(SCANNER)> insert into test_5 values (4, 40, PADSTRING('d',1000), PADSTRING('d',1000));1 row inserted/updated/deletedij(SCANNER)> insert into test_5 values (5, 50, PADSTRING('e',1000), PADSTRING('e',1000));1 row inserted/updated/deletedij(SCANNER)> insert into test_5 values (6, 60, PADSTRING('f',1000), PADSTRING('f',1000));1 row inserted/updated/deletedij(SCANNER)> create index test_5_idx on test_5 (a);0 rows inserted/updated/deletedij(SCANNER)> commit;ij(SCANNER)> set connection deleter;ij(DELETER)> autocommit off;ij(DELETER)> commit;ij(DELETER)> ---------------------------------------------------------------------------------- Set up scanner to be doing a row locked index to base row scan on the index.-- By using group fetch it will read and release locks on multiple rows from-- the index and save away row pointers from the index.--------------------------------------------------------------------------------set connection scanner;ij(SCANNER)> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','10');0 rows inserted/updated/deletedij(SCANNER)> get cursor scan_cursor as 'select a, a2 from test_5 where a > 1 ';ij(SCANNER)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');0 rows inserted/updated/deletedij(SCANNER)> -- because of group locking will get locks on 1, 2, 3, 4, and 5 and then will-- release the locks on 1, 2, 3, and 4. The last one is released on close or-- on next call emptying the cursor.next scan_cursor;A |A2 -----------------------2 |20 ij(SCANNER)> ---------------------------------------------------------------------------------- Delete a row that the scanner has looked at but not reported back to the-- caller.--------------------------------------------------------------------------------set connection deleter;ij(DELETER)> delete from test_5 where a = 4;1 row inserted/updated/deletedij(DELETER)> ---------------------------------------------------------------------------------- The scan will requalify rows when it goes to the base table, thus it will-- see 3, but block when it gets to the key of deleted row (4).--------------------------------------------------------------------------------set connection scanner;ij(SCANNER)> next scan_cursor;A |A2 -----------------------3 |30 ij(SCANNER)> next scan_cursor;ERROR 40XL1: A lock could not be obtained within the time requestedij(SCANNER)> -- commit the deleteset connection deleter;ij(DELETER)> commit;ij(DELETER)> -- scanner should see 1,2,3,4,6set connection scanner;ij(SCANNER)> close scan_cursor;ij(SCANNER)> select a,b from test_5;A |B --------------------------------------------------------------------------------------------------------------------------------------------1 |a &2 |b &3 |c &5 |e &6 |f &ij(SCANNER)> ---------------------------------------------------------------------------------- cleanup.--------------------------------------------------------------------------------set connection deleter;ij(DELETER)> commit;ij(DELETER)> disconnect;ij> set connection scanner;ij(SCANNER)> commit;ij(SCANNER)> drop table test_5;0 rows inserted/updated/deletedij(SCANNER)> commit;ij(SCANNER)> disconnect;ij> ---------------------------------------------------------------------------------- Test 6: make sure a that heap scans which cross page boundaries release-- locks correctly.---------------------------------------------------------------------------------- Test setup - create a heap with one row per page.connect 'wombat' as scanner;ij(SCANNER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.NoHoldForConnection;ij(SCANNER)> connect 'wombat' as deleter;ij(DELETER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.NoHoldForConnection;ij(DELETER)> set connection scanner;ij(SCANNER)> autocommit off;ij(SCANNER)> create table test_6 (a int, a2 int, b varchar(2000), c varchar(2000)) ;0 rows inserted/updated/deletedij(SCANNER)> insert into test_6 values (1, 10, PADSTRING('a',2000), PADSTRING('a',2000));1 row inserted/updated/deletedij(SCANNER)> insert into test_6 values (2, 20, PADSTRING('b',2000), PADSTRING('b',2000));1 row inserted/updated/deletedij(SCANNER)> insert into test_6 values (3, 30, PADSTRING('c',2000), PADSTRING('c',2000));1 row inserted/updated/deletedij(SCANNER)> insert into test_6 values (4, 40, PADSTRING('d',2000), PADSTRING('d',2000));1 row inserted/updated/deletedij(SCANNER)> insert into test_6 values (5, 50, PADSTRING('e',2000), PADSTRING('e',2000));1 row inserted/updated/deletedij(SCANNER)> create index test_6_idx on test_6 (a);0 rows inserted/updated/deletedij(SCANNER)> commit;ij(SCANNER)> set connection deleter;ij(DELETER)> autocommit off;ij(DELETER)> commit;ij(DELETER)> ---------------------------------------------------------------------------------- Set up scanner to be doing a row locked index to base row scan on the index.-- By using group fetch it will read and release locks on multiple rows from-- the index and save away row pointers from the index.--------------------------------------------------------------------------------set connection scanner;ij(SCANNER)> get cursor scan_cursor as 'select a, a2 from test_6';ij(SCANNER)> next scan_cursor;A |A2 -----------------------1 |10 ij(SCANNER)> next scan_cursor;A |A2 -----------------------2 |20 ij(SCANNER)> next scan_cursor;A |A2 -----------------------3 |30 ij(SCANNER)> next scan_cursor;A |A2 -----------------------4 |40 ij(SCANNER)> next scan_cursor;A |A2 -----------------------5 |50 ij(SCANNER)> ---------------------------------------------------------------------------------- Delete all rows that the scanner has looked at, and should have released the-- lock on.--------------------------------------------------------------------------------set connection deleter;ij(DELETER)> delete from test_6 where a = 1;1 row inserted/updated/deletedij(DELETER)> delete from test_6 where a = 2;1 row inserted/updated/deletedij(DELETER)> delete from test_6 where a = 3;1 row inserted/updated/deletedij(DELETER)> delete from test_6 where a = 4;1 row inserted/updated/deletedij(DELETER)> ---------------------------------------------------------------------------------- The scan should either block on the delete or continue and not return the-- the deleted row.--------------------------------------------------------------------------------set connection scanner;ij(SCANNER)> next scan_cursor;No current rowij(SCANNER)> close scan_cursor;ij(SCANNER)> -- commit the deleteset connection deleter;ij(DELETER)> delete from test_6 where a = 5;1 row inserted/updated/deletedij(DELETER)> commit;ij(DELETER)> -- scanner should see no rows.set connection scanner;⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -