📄 rlliso2multi.out
字号:
ij> ---------------------------------------------------------------------------------- Test multi user lock interaction under isolation level 2. default isolation-- level has been set as a property to serializable.--------------------------------------------------------------------------------run resource 'createTestProcedures.subsql';ij> CREATE FUNCTION PADSTRING (DATA VARCHAR(32000), LENGTH INTEGER) RETURNS VARCHAR(32000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Formatters.padString' LANGUAGE JAVA PARAMETER STYLE JAVA;0 rows inserted/updated/deletedij> CREATE PROCEDURE WAIT_FOR_POST_COMMIT() DYNAMIC RESULT SETS 0 LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.T_Access.waitForPostCommitToFinish' PARAMETER STYLE JAVA;0 rows inserted/updated/deletedij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');0 rows inserted/updated/deletedij> autocommit off;ij> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.NoHoldForConnection;ij> ---------------------------------------------------------------------------------- Test 0: verify isolation level by seeing if a read lock is released or not.--------------------------------------------------------------------------------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 writer;ij(WRITER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.NoHoldForConnection;ij(WRITER)> -- set upset connection scanner;ij(SCANNER)> autocommit off;ij(SCANNER)> create table test_0 (a int);0 rows inserted/updated/deletedij(SCANNER)> insert into test_0 values (1);1 row inserted/updated/deletedij(SCANNER)> commit;ij(SCANNER)> set connection writer;ij(WRITER)> autocommit off;ij(WRITER)> -- isolation 2 scanner should release read lock on table after statement.set connection scanner;ij(SCANNER)> select * from test_0;A -----------1 ij(SCANNER)> -- writer should be able to insert into table - scanner released read lock.set connection writer;ij(WRITER)> insert into test_0 values (2);1 row inserted/updated/deletedij(WRITER)> -- scanner will now block on uncommitted insert, and get lock timeoutset connection scanner;ij(SCANNER)> select * from test_0;A -----------ERROR 40XL1: A lock could not be obtained within the time requestedij(SCANNER)> commit;ij(SCANNER)> -- commit writer - releasing all locks.set connection writer;ij(WRITER)> commit;ij(WRITER)> -- scanner will now see 2 rowsset connection scanner;ij(SCANNER)> select * from test_0;A -----------1 2 ij(SCANNER)> commit;ij(SCANNER)> -- cleanupset connection scanner;ij(SCANNER)> drop table test_0;0 rows inserted/updated/deletedij(SCANNER)> commit;ij(SCANNER)> disconnect;ij> set connection writer;ij(WRITER)> disconnect;ij> ---------------------------------------------------------------------------------- Test 1: make sure a leaf root growing get's the right lock.------------------------------------------------------------------------------------------------------------------------------------------------------------------ Test setup - create a 1 page btree, 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 rootgrower;ij(ROOTGROWER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.NoHoldForConnection;ij(ROOTGROWER)> set connection scanner;ij(SCANNER)> autocommit off;ij(SCANNER)> create table a (a varchar(1200), b varchar(1000)) ;0 rows inserted/updated/deletedij(SCANNER)> insert into a values (PADSTRING('a',1200), PADSTRING('a',1000));1 row inserted/updated/deletedij(SCANNER)> insert into a values (PADSTRING('b',1200), PADSTRING('b',1000));1 row inserted/updated/deletedij(SCANNER)> insert into a values (PADSTRING('c',1200), PADSTRING('c',1000));1 row inserted/updated/deletedij(SCANNER)> create index a_idx on a (a) ;0 rows inserted/updated/deletedij(SCANNER)> commit;ij(SCANNER)> set connection rootgrower;ij(ROOTGROWER)> autocommit off;ij(ROOTGROWER)> commit;ij(ROOTGROWER)> ---------------------------------------------------------------------------------- Set up scanner to be doing a row locked covered scan on the index.--------------------------------------------------------------------------------set connection scanner;ij(SCANNER)> autocommit off;ij(SCANNER)> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');0 rows inserted/updated/deletedij(SCANNER)> get cursor scan_cursor as 'select a from a where a >= PADSTRING(''a'',1200) and a < PADSTRING(''c'',1200) ';ij(SCANNER)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');0 rows inserted/updated/deletedij(SCANNER)> next scan_cursor;A --------------------------------------------------------------------------------------------------------------------------------a &ij(SCANNER)> ---------------------------------------------------------------------------------- This should block and timeout on the scan lock held by the scanner on the first page.--------------------------------------------------------------------------------set connection rootgrower;ij(ROOTGROWER)> autocommit off;ij(ROOTGROWER)> insert into a values (PADSTRING('d',1200), PADSTRING('d',1000));ERROR 40XL1: A lock could not be obtained within the time requestedij(ROOTGROWER)> ---------------------------------------------------------------------------------- The scan should continue unaffected.--------------------------------------------------------------------------------set connection scanner;ij(SCANNER)> next scan_cursor;A --------------------------------------------------------------------------------------------------------------------------------b &ij(SCANNER)> next scan_cursor;No current rowij(SCANNER)> ---------------------------------------------------------------------------------- This insert will block on the previous key lock of the scanner.--------------------------------------------------------------------------------set connection rootgrower;ij(ROOTGROWER)> insert into a values (PADSTRING('ab',1200), PADSTRING('ab',1000));1 row inserted/updated/deletedij(ROOTGROWER)> ---------------------------------------------------------------------------------- Now the grow root should be allowed (note that cursor scan has locks-- on the leaf page being grown - just not the scan lock).--------------------------------------------------------------------------------set connection rootgrower;ij(ROOTGROWER)> insert into a values (PADSTRING('d',1200), PADSTRING('d',1000));1 row inserted/updated/deletedij(ROOTGROWER)> select a from a;A --------------------------------------------------------------------------------------------------------------------------------a &ab &b &c &d &ij(ROOTGROWER)> ---------------------------------------------------------------------------------- cleanup.--------------------------------------------------------------------------------set connection rootgrower;ij(ROOTGROWER)> commit;ij(ROOTGROWER)> disconnect;ij> set connection scanner;ij(SCANNER)> commit;ij(SCANNER)> drop table a;0 rows inserted/updated/deletedij(SCANNER)> commit;ij(SCANNER)> disconnect;ij> ---------------------------------------------------------------------------------- Test 2: make sure previous key locks are gotten correctly.--------------------------------------------------------------------------------connect 'wombat' as client_1;ij(CLIENT_1)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.NoHoldForConnection;ij(CLIENT_1)> connect 'wombat' as client_2;ij(CLIENT_2)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.NoHoldForConnection;ij(CLIENT_2)> 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)> insert into a values (PADSTRING('b',1000), PADSTRING('b',1000));1 row inserted/updated/deletedij(CLIENT_1)> insert into a values (PADSTRING('c',1000), PADSTRING('c',1000));1 row inserted/updated/deletedij(CLIENT_1)> insert into a values (PADSTRING('e',1000), PADSTRING('e',1000));1 row inserted/updated/deletedij(CLIENT_1)> insert into a values (PADSTRING('f',1000), PADSTRING('f',1000));1 row inserted/updated/deletedij(CLIENT_1)> insert into a values (PADSTRING('g',1000), PADSTRING('g',1000));1 row inserted/updated/deletedij(CLIENT_1)> commit;ij(CLIENT_1)> set connection client_2;ij(CLIENT_2)> autocommit off;ij(CLIENT_2)> ---------------------------------------------------------------------------------- client 1 will get exclusive locks on 'c'.--------------------------------------------------------------------------------set connection client_1;ij(CLIENT_1)> update a set b = 'new value' where a > 'b' and a <= 'd';1 row inserted/updated/deletedij(CLIENT_1)> -- run resource 'LockTableQuery.subsql';set connection client_2;ij(CLIENT_2)> ---------------------------------------------------------------------------------- the following will not time out, the insert-- will get a previous key insert lock which will not conflict with the-- non-insert read-committed exclusive lock on 'c'.--------------------------------------------------------------------------------insert into a values (PADSTRING('d',1000), PADSTRING('d',1000));1 row inserted/updated/deletedij(CLIENT_2)> ---------------------------------------------------------------------------------- the following should NOT cause a time out--------------------------------------------------------------------------------insert into a values (PADSTRING('a',1000), PADSTRING('a',1000));1 row inserted/updated/deletedij(CLIENT_2)> ---------------------------------------------------------------------------------- the following will block because it is a unique index, and the insert is of-- the same row being locked by client_1--------------------------------------------------------------------------------insert into a values (PADSTRING('c',1000), PADSTRING('c',1000));ERROR 40XL1: A lock could not be obtained within the time requestedij(CLIENT_2)> -- run resource 'LockTableQuery.subsql';---------------------------------------------------------------------------------- cleanup.--------------------------------------------------------------------------------set connection client_1;ij(CLIENT_1)> select * from a;A |B -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------b &|b &c &|new value e &|e &f &|f &g &|g &ij(CLIENT_1)> commit;ij(CLIENT_1)> set connection client_2;ij(CLIENT_2)> commit;ij(CLIENT_2)> select * from a;A |B -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------b &|b &c &|new value e &|e &f &|f &g &|g &ij(CLIENT_2)> drop table a;0 rows inserted/updated/deletedij(CLIENT_2)> commit;⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -