📄 partdml.out
字号:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Index Scan ResultSet for BASIC using index B1 at read committed isolation level using share row locking chosen by the optimizerBit set of columns fetched={0}Bit set of columns fetched=All ij> rollback;ij> ---- quickly confirm that we get all columns for updateable cursors--get cursor c as 'select cint from basic for update';ij> next c;CINT -----------11 ij> ----------------------------------------------------------------- -- now lets try some constraints----------------------------------------------------------------- ---- check constraints--alter table basic add constraint ck check (unindexed > cdec);ERROR X0X95: Operation 'ALTER TABLE' cannot be performed on object 'BASIC' because there is an open ResultSet dependent on that object.ij> commit;ij> update basic set unindexed = unindexed where cint = 11;1 row inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Index Scan ResultSet for BASIC using index B4 at read committed isolation level using exclusive row locking chosen by the optimizerBit set of columns fetched=All ij> rollback;ij> update basic set unindexed = unindexed;12 rows inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------TableScanBit set of columns fetched={5}ij> rollback;ij> update basic set cdec = cdec where cint = 11;1 row inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Index Scan ResultSet for BASIC using index B4 at read committed isolation level using exclusive row locking chosen by the optimizerBit set of columns fetched=All ij> rollback;ij> delete from basic where cint = 11;1 row inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Index Scan ResultSet for BASIC using index B4 at read committed isolation level using exclusive row locking chosen by the optimizerBit set of columns fetched=All ij> rollback;ij> -- one that isn't affected by contstraintupdate basic set ctime = ctime;12 rows inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Index Scan ResultSet for BASIC using index B3 at read committed isolation level using exclusive row locking chosen by the optimizerBit set of columns fetched={0, 1, 2}ij> rollback;ij> -- confirm it is working okupdate basic set unindexed = 0;12 rows inserted/updated/deletedij> rollback;ij> ---- foreign keys--alter table basic add constraint fk1 foreign key (cchar, ccharForBitData) references p;0 rows inserted/updated/deletedij> commit;ij> update basic set cchar = cchar, ccharForBitData = ccharForBitData;12 rows inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Index Scan ResultSet for BASIC using index B1 at read committed isolation level using exclusive row locking chosen by the optimizerBit set of columns fetched={0, 1, 2, 3}ij> update basic set cchar = cchar;12 rows inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Index Scan ResultSet for BASIC using index B1 at read committed isolation level using exclusive row locking chosen by the optimizerBit set of columns fetched={0, 1, 2, 3}ij> update basic set ccharForBitData = ccharForBitData;12 rows inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Index Scan ResultSet for BASIC using index B1 at read committed isolation level using exclusive row locking chosen by the optimizerBit set of columns fetched={0, 1, 2, 3}ij> rollback;ij> --pk update-- only this update should fail, does not satisfy fk1update p set ccharForBitData = x'22', cchar = CAST(unindexed as CHAR(10));ERROR 23503: UPDATE on table 'P' caused a violation of foreign key constraint 'FK1' for key (11 ,11). The statement has been rolled back.ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------TableScanBit set of columns fetched={0, 2, 3}ij> rollback;ij> update p set cdec = cdec + 1.1;4 rows inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Index Scan ResultSet for P using constraint PK2 at read committed isolation level using exclusive row locking chosen by the optimizerBit set of columns fetched={0, 1}ij> rollback;ij> update p set unindexed = 666, cchar = 'fail';ERROR 23503: UPDATE on table 'P' caused a violation of foreign key constraint 'FK1' for key (11 ,11). The statement has been rolled back.ij> rollback;ij> -- only this update should fail, does not satisfy fk1update p set ccharForBitData = x'66' where ccharForBitData = x'22';ERROR 23503: UPDATE on table 'P' caused a violation of foreign key constraint 'FK1' for key (22 ,22). The statement has been rolled back.ij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Index Scan ResultSet for P using constraint PK1 at read committed isolation level using exclusive row locking chosen by the optimizerBit set of columns fetched={0, 1, 2}ij> rollback;ij> alter table basic add constraint fk2 foreign key (cdec) references p(cdec);0 rows inserted/updated/deletedij> commit;ij> update p set cdec = cdec + 1.1;4 rows inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Index Scan ResultSet for P using constraint PK2 at read committed isolation level using exclusive row locking chosen by the optimizerBit set of columns fetched={0, 1}ij> rollback;ij> update basic set cdec = cdec, cint = cint, ccharForBitData = ccharForBitData, cchar = cchar;12 rows inserted/updated/deletedij> values getScanCols(SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS());1 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------TableScanBit set of columns fetched={0, 1, 2, 3, 4}ij> rollback;ij> update basic set cdec = cdec+1.1, cint = cint, ccharForBitData = ccharForBitData, cchar = cchar;ERROR 23503: UPDATE on table 'BASIC' caused a violation of foreign key constraint 'FK2' for key (4.40). The statement has been rolled back.ij> rollback;ij> delete from p where cdec = 1.1;ERROR 23503: DELETE on table 'P' caused a violation of foreign key constraint 'FK1' for key (11 ,11). The statement has been rolled back.ij> rollback;ij> -- clean updrop function getScanCols;0 rows inserted/updated/deletedij> drop table basic;0 rows inserted/updated/deletedij> drop table p;0 rows inserted/updated/deletedij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -