def test_1(act: Action): stm1 = """create or alter procedure sp_test as begin exit; end """ stm2 = """create or alter procedure sp_test as declare x int; begin exit; end """ custom_tpb = tpb(isolation=Isolation.CONCURRENCY) with act.db.connect() as con1, act.db.connect() as con2: con1.begin(custom_tpb) cur1 = con1.cursor() cur2 = con2.cursor() cur1.execute(stm1) con1.commit() con2.begin(custom_tpb) cur2.execute(stm2) con2.commit() con1.begin(custom_tpb) cur1.execute(stm1) con1.commit()
def test_1(act: Action, capsys): act.isql(switches=[], input=ddl_script) # custom_tpb = tpb(isolation=Isolation.READ_COMMITTED_NO_RECORD_VERSION, lock_timeout=0) with act.db.connect() as con: cur1 = con.cursor() cur1.execute('select x from sp_test(21)').fetchall() drop_commands = [ 'drop procedure sp_test', 'drop procedure sp_worker', 'drop view v_test', 'drop table test2', 'drop index test1_id', 'drop index test2_id_x_desc' ] for cmd in drop_commands: with act.db.connect() as con2: tx = con2.transaction_manager(custom_tpb) tx.begin() cur2 = tx.cursor() try: cur2.execute(cmd) except Exception as exc: print(exc) # act.reset() act.expected_stdout = expected_stdout act.stdout = capsys.readouterr().out assert act.clean_stdout == act.clean_expected_stdout
def test_1(act: Action): custom_tpb = tpb(isolation=Isolation.CONCURRENCY, lock_timeout=0) with act.db.connect() as con1, act.db.connect() as con2: con2.begin(custom_tpb) con1.execute_immediate('create table a (id int primary key)') con1.execute_immediate( 'create table b (id int primary key, id_a int, constraint fk_b__a foreign key(id_a) references a(id) on update cascade on delete cascade)' ) con1.commit() con1.begin(custom_tpb) cur1 = con1.cursor() cur1.execute('insert into a(id) values( ? )', [1]) con2.commit() con2.begin(custom_tpb) cur2 = con2.cursor() cur2.execute('select id from a') con1.commit() with pytest.raises(DatabaseError, match='.*violation of FOREIGN KEY constraint.*'): cur2.execute('insert into b (id, id_a) values (?, ?)', [1, 1])
def test_1(act: Action, capsys): # # NB: adding this timeout does NOT change WAIT-nature of transaction as it is considered by engine. # # (in other words: such transaction will not became 'no wait' which must not be used in this test): custom_tpb = tpb(isolation=Isolation.SNAPSHOT, lock_timeout=5) # with act.db.connect() as con1, act.db.connect() as con2: con1.main_transaction.default_tpb = custom_tpb con2.main_transaction.default_tpb = custom_tpb # con1.execute_immediate('update t_main set id=-id') con1.commit() con2.execute_immediate('drop table t_detl') con2.commit() cur = con2.cursor() cur.execute( "select r.rdb$relation_name from rdb$database d left join rdb$relations r on r.rdb$relation_name = upper('t_detl')" ) for r in cur: print(r[0]) print('Passed.') # Check act.expected_stdout = expected_stdout act.stdout = capsys.readouterr().out assert act.clean_stdout == act.clean_expected_stdout
def test_1(act: Action): with act.db.connect() as con: txparam_read = tpb(isolation=Isolation.READ_COMMITTED_RECORD_VERSION, lock_timeout=0, access_mode=TraAccessMode.READ) txparam_write = tpb(isolation=Isolation.READ_COMMITTED_RECORD_VERSION, lock_timeout=0) tx_read = con.transaction_manager(txparam_read) cur_read = tx_read.cursor() cur_read.execute("select sid, sname from FU_X1") tx_write = con.transaction_manager(txparam_write) cur_write = tx_write.cursor() cur_write.callproc("save_x1", ['2', 'foo']) tx_write.commit() cur_read.execute("select sid, sname from FU_X1") cur_read.fetchall() # If this does not raises an exception, the test passes
def test_1(act: Action): with act.db.connect() as con: cust_tpb = tpb(isolation=Isolation.READ_COMMITTED_RECORD_VERSION, lock_timeout=0) con.begin(cust_tpb) with con.cursor() as c: c.execute("update master_table set UF=10 WHERE ID=1") #Create second connection for change detail table with act.db.connect() as con_detail: con_detail.begin(cust_tpb) with con_detail.cursor() as cd: cd.execute("INSERT INTO DETAIL_TABLE (ID, FKEY) VALUES (1,1)") con_detail.commit()
def test_1(act: Action): with act.db.connect() as con: cust_tpb = tpb(isolation=Isolation.READ_COMMITTED_RECORD_VERSION, lock_timeout=0) con.begin(cust_tpb) with con.cursor() as c: c.execute("UPDATE MASTER_TABLE SET INT_F=10") #Create second connection for change detail table with act.db.connect() as con_detail: con_detail.begin(cust_tpb) with con_detail.cursor() as cd: cd.execute("UPDATE DETAIL_TABLE SET ID=2 WHERE ID=1") con_detail.commit()
def __init__(self, connection: Connection): """ Arguments: connection: Connection that should be used to access monitoring tables. """ self._con: Connection = connection self._ic: Cursor = self._con.transaction_manager( tpb(Isolation.READ_COMMITTED_RECORD_VERSION, access=TraAccessMode.READ)).cursor() self._ic._logging_id_ = 'monitor.internal_cursor' self.__internal: bool = False self._con_id: int = connection.info.id self.clear()
def test_1(act: Action): with act.db.connect() as con: cust_tpb = tpb(isolation=Isolation.READ_COMMITTED_RECORD_VERSION, lock_timeout=0) con.begin(cust_tpb) with con.cursor() as c: c.execute('UPDATE MASTER_TABLE SET ID_1=2 WHERE ID_1=1') con.commit() #Create second connection for change detail table with act.db.connect() as con_detail: con_detail.begin(cust_tpb) with con_detail.cursor() as cd: cd.execute("INSERT INTO DETAIL_TABLE (ID, FKEY_1, FKEY_2) VALUES (1, 2, 'one')") con_detail.commit()
def test_1(act: Action): with act.db.connect() as con: cust_tpb = tpb(isolation=Isolation.READ_COMMITTED_RECORD_VERSION, lock_timeout=0) con.begin(cust_tpb) with con.cursor() as c: c.execute("UPDATE MASTER_TABLE SET UF=2 WHERE ID=1") #Create second connection for change detail table with act.db.connect() as con_detail: con_detail.begin(cust_tpb) with con_detail.cursor() as cd: with pytest.raises(DatabaseError, match='.*Foreign key reference target does not exist.*'): cd.execute("INSERT INTO DETAIL_TABLE (ID, FKEY) VALUES (1,2)") con_detail.commit()
def test_1(act: Action): with act.db.connect() as con: cust_tpb = tpb(isolation=Isolation.READ_COMMITTED_RECORD_VERSION, lock_timeout=0) con.begin(cust_tpb) with con.cursor() as c: c.execute("update master_table set int_f = 10 WHERE ID=1") #Create second connection for change detail table with act.db.connect() as con_detail: con_detail.begin(cust_tpb) with con_detail.cursor() as cd: cd.execute("INSERT INTO DETAIL_TABLE (ID, FKEY) VALUES (1,1)") con_detail.commit() with pytest.raises(DatabaseError, match='.*Foreign key references are present for the record.*'): c.execute("update master_table set UF=10 WHERE ID=1")
def test_1(act: Action): with act.db.connect() as con: cust_tpb = tpb(isolation=Isolation.READ_COMMITTED_RECORD_VERSION, lock_timeout=0) con.begin(cust_tpb) with con.cursor() as c: c.execute('UPDATE MASTER_TABLE SET INT_F=2') con.savepoint('A') c.execute('DELETE FROM MASTER_TABLE WHERE ID=1') con.rollback(savepoint='A') #Create second connection for change detail table with act.db.connect() as con_detail: con_detail.begin(cust_tpb) with con_detail.cursor() as cd: cd.execute("INSERT INTO DETAIL_TABLE (ID, FKEY) VALUES (1,1)") con_detail.commit()
def test_1(act: Action): with act.db.connect() as con: cust_tpb = tpb(isolation=Isolation.READ_COMMITTED_RECORD_VERSION, lock_timeout=0) con.begin(cust_tpb) with con.cursor() as c: c.execute("SELECT INT_F FROM MASTER_TABLE WHERE ID=1 WITH LOCK" ).fetchall() #Create second connection for change detail table with act.db.connect() as con_detail: con_detail.begin(cust_tpb) with con_detail.cursor() as cd: cd.execute( "INSERT INTO DETAIL_TABLE (ID, FKEY) VALUES (1,1)") con_detail.commit()
def test_1(act: Action): custom_tpb = tpb(isolation=Isolation.CONCURRENCY, lock_timeout=0) with act.db.connect() as con1: tx1a = con1.transaction_manager(custom_tpb) tx1a.begin() cur1 = tx1a.cursor() cur1.execute('insert into tsn (sn) values( -2 )') tx1a.commit() # sql_get_sn = """ execute block returns(o_sn bigint) as begin o_sn = RDB$GET_CONTEXT('SYSTEM', 'SNAPSHOT_NUMBER'); suspend; in autonomous transaction do insert into tsn(sn) values( -1 ); end """ tx1b = con1.transaction_manager(custom_tpb) cur1 = tx1b.cursor() snap_num = cur1.execute(sql_get_sn).fetchone()[0] # for msg, expect_out, expect_err in [ ('yet exists', expected_stdout_a, ''), ('does not exists', expected_stdout_b, expected_stderr) ]: sql_chk_sn = f""" -- NB!! looks strange but it seems that this 'SET BAIL ON' does not work here because -- both records will be extracted in any case. // todo later: check it! --set bail on; set count on; commit; set transaction snapshot; select 'Tx base snapshot: {msg}' as msg, t.sn as set_tx_snapshot_without_num from tsn t order by sn; commit; set transaction snapshot at number {snap_num}; select 'Tx base snapshot: {msg}' as msg, t.sn as set_tx_snapshot_at_number_N from tsn t order by sn; commit; quit; """ act.reset() act.expected_stdout = expect_out act.expected_stderr = expect_err act.isql(switches=['-q'], input=sql_chk_sn) if tx1b.is_active(): tx1b.commit() assert act.clean_stdout == act.clean_expected_stdout
def test_1(act: Action): with act.db.connect() as con: cust_tpb = tpb(isolation=Isolation.READ_COMMITTED_RECORD_VERSION, lock_timeout=0) con.begin(cust_tpb) with con.cursor() as c: c.execute("SELECT INT_F FROM MASTER_TABLE WHERE ID=1 WITH LOCK") #Create second connection for change detail table with act.db.connect() as con_detail: con_detail.begin(cust_tpb) with con_detail.cursor() as cd: cd.execute("INSERT INTO DETAIL_TABLE (ID, FKEY) VALUES (1,1)") con_detail.commit() with pytest.raises(DatabaseError, match='.*Foreign key references are present for the record.*'): c.fetchall() c.execute("UPDATE MASTER_TABLE SET ID=2") con.commit()
def test_1(act: Action): custom_tpb = tpb(isolation=Isolation.CONCURRENCY) with act.db.connect(no_gc=True) as con: tx1 = con.transaction_manager(custom_tpb) tx2 = con.transaction_manager(custom_tpb) tx1.begin() tx2.begin() cur1 = tx1.cursor() cur2 = tx2.cursor() # Test starts here, no exception should occur # Tx-1: insert big number of rows. cur1.execute("insert into tbig(s) select rpad('', 50, uuid_to_char(gen_uuid())) from rdb$types,rdb$types,(select 1 k from rdb$types rows 2) rows 120000") cur1.execute("insert into test(id, who) values(1, 'Tx-1')") # Tx-1: rollback via TIP. tx1.rollback() # Tx-2: insert single test record. cur2.execute("insert into test(id, who) values(1, 'Tx-2')") tx2.rollback()
def test_1(act: Action): cust_tpb = tpb(isolation=Isolation.READ_COMMITTED_RECORD_VERSION, lock_timeout=0) with act.db.connect() as con: con.begin(cust_tpb) with con.cursor() as c: c.execute("UPDATE MASTER_TABLE SET ID = 2 WHERE ID=1") #Create second connection for change detail table with act.db.connect() as con_detail: con_detail.begin(cust_tpb) with con_detail.cursor() as cd: with pytest.raises( DatabaseError, match= '.*violation of FOREIGN KEY constraint "FK_DETAIL_TABLE" on table "DETAIL_TABLE".*' ): cd.execute( "INSERT INTO DETAIL_TABLE (ID, FKEY) VALUES (1,1)") con_detail.commit()
def test_2(act: Action, user_srp: User, user_leg: User, capsys): act.expected_stdout = expected_stdout_2 check_sql = 'select mon$user as who_am_i, mon$auth_method as auth_method from mon$attachments' custom_tpb = tpb(isolation=Isolation.READ_COMMITTED_RECORD_VERSION, lock_timeout=0) # with act.db.connect() as con1: trn1 = con1.transaction_manager(custom_tpb) cur1 = trn1.cursor() cur1.execute('select sec$user_name from sec$users').fetchall() with act.db.connect(user=user_leg.name, password=user_leg.password) as con2, \ act.db.connect(user=user_srp.name, password=user_srp.password) as con3: trn2 = con2.transaction_manager(custom_tpb) cur2 = trn2.cursor() act.print_data_list(cur2.execute(check_sql)) # trn3 = con3.transaction_manager(custom_tpb) cur3 = trn3.cursor() act.print_data_list(cur3.execute(check_sql)) act.stdout = capsys.readouterr().out assert act.clean_stdout == act.clean_expected_stdout
def test_1(act: Action): act.isql(switches=[], input=ddl_script) custom_tpb = tpb(isolation=Isolation.CONCURRENCY) # with act.db.connect() as con1: tx1 = con1.transaction_manager(custom_tpb) tx1.begin() cur1 = tx1.cursor() cur1.execute( "select current_transaction, rdb$get_context('SYSTEM', 'ISOLATION_LEVEL') from rdb$database" ) cur1.fetchall() with act.db.connect() as con2: tx2 = con2.transaction_manager(custom_tpb) tx2.begin() cur2 = tx2.cursor() cur2.callproc('p_gen_tx', [GEN_ROWS]) tx2.commit() # tx2.begin() cur2.execute('insert into a values (current_transaction)') tx2.commit() # tx2.begin() cur2.execute('set statistics index idx_a') tx2.commit() # tx2.begin() cur2.execute('select * from a where id > 0') cur2.fetchall() tx2.commit() # tx2.begin() cur2.callproc('p_gen_tx', [GEN_ROWS]) tx2.commit() # --- tx1.commit() cur1.execute('select * from a where id > 0') cur1.fetchall() # WI-V2.5.8.27089 crashed here tx1.commit()
def test_1(act: Action, capsys): custom_tpb = tpb(isolation=Isolation.READ_COMMITTED_RECORD_VERSION, access_mode=TraAccessMode.WRITE, lock_timeout=0) with act.db.connect() as con1: tx1a = con1.transaction_manager(custom_tpb) cur1a = tx1a.cursor() tx1b = con1.transaction_manager(custom_tpb) cur1b = tx1b.cursor() try: cur1a.execute( "insert into gtt_session select rand()*10, rand()*10 from rdb$types" ) cur1b.execute( "create index gtt_session_x_y on gtt_session computed by (x+y)" ) tx1b.commit( ) # WI-V2.5.6.27013 issues here: lock conflict on no wait transaction unsuccessful metadata update object TABLE "GTT_SESSION" is in use -901 335544345 tx1a.commit() except DatabaseError as e: print('Error-1:') msg = e.args[0] print(msg) # if not msg.split(): # 2.5.5: control should NOT pass here at all! with act.db.connect() as con2: try: tx2a = con2.transaction_manager() cur2a = tx2a.cursor() cur2a.execute( "insert into gtt_session select rand()*11, rand()*11 from rdb$types" ) except DatabaseError as e: print('Error-2:') print(e.args[0]) # act.expected_stdout = expected_stdout act.stdout = capsys.readouterr().out assert act.clean_stdout == act.clean_expected_stdout
def test_1(act: Action, db_b: Database, capsys): dt_list = [] custom_tpb = tpb(isolation=Isolation.READ_COMMITTED_RECORD_VERSION, lock_timeout=0) with act.db.connect() as con1, db_b.connect() as con2: for i in range(LIMBO_COUNT): dt = DistributedTransactionManager([con1, con2], custom_tpb) dt_list.append(dt) cur1 = dt.cursor(con1) cur1.execute("insert into test (id, x) values (?, ?)", [i, i * 11111]) cur1.close() cur2 = dt.cursor(con2) cur2.execute("insert into test (id, x) values (?, ?)", [-i, i * -2222]) cur2.close() for dtc in dt_list: # Initiate distributed commit: phase-1 dtc.prepare() # Shut down the first database with act.connect_server() as srv: srv.database.shutdown(database=act.db.db_path, mode=ShutdownMode.FULL, method=ShutdownMethod.FORCED, timeout=0) # while dt_list: dtc = dt_list.pop() dtc._tra = None # Needed hack to bypass commit and exception dtc.close() # with act.connect_server() as srv: srv.database.bring_online(database=act.db.db_path) # act.gfix(switches=['-list', act.db.dsn]) gfix_log = act.stdout # act.reset() # Set EXPECTED_STDERR so we can get over "unavailable database" error and fail on assert # Remove when svcmgr issue is resolved act.expected_stderr = "We expect errors" act.svcmgr(switches=[ 'action_repair', 'rpr_list_limbo_trans', 'dbname', act.db.dsn ]) mngr_log = act.stdout # Show error returned, remove when svcmgr issue is resolved print(act.stderr) # pattern_for_gfix_output = re.compile('Transaction\\s+\\d+\\s+.*limbo', re.IGNORECASE) pattern_for_fsvc_output = re.compile('Transaction\\s+in\\s+limbo:\\s+\\d+', re.IGNORECASE) # for log_name, limbo_log, pattern in [ ('gfix -list', gfix_log, pattern_for_gfix_output), ('fbsvcmgr rpr_list_limbo_trans', mngr_log, pattern_for_fsvc_output) ]: lines_with_limbo_info = 0 msg = f"Number of lines related to limbo Tx in '{log_name}' output: " for line in limbo_log.splitlines(): if pattern.search(line): lines_with_limbo_info += 1 #else: #print(f'Additional output from {log_name}: {line}') print(msg + str(lines_with_limbo_info)) # Check act.reset() act.expected_stdout = expected_stdout act.stdout = capsys.readouterr().out assert act.clean_stdout == act.clean_expected_stdout
def test_1(act: Action): # CONCURRENCY | WAIT | READ_WRITE dml_tpb = tpb(isolation=Isolation.CONCURRENCY) # READ_COMMITTED | NO_REC_VERSION | WAIT | READ_WRITE ddl_tpb = tpb(isolation=Isolation.READ_COMMITTED_NO_RECORD_VERSION) # with act.db.connect() as con: con.execute_immediate('recreate table a (id int)') con.commit() con.execute_immediate('create index idx_a on a(id)') con.commit() sql = """ create or alter procedure p_gen_tx(n int) as declare i int = 0; begin while (i < n) do in autonomous transaction do i = i + 1; end """ con.execute_immediate(sql) con.commit() # Test con = act.db.connect() tx1a = con.transaction_manager(dml_tpb) tx1a.begin() cur1 = tx1a.cursor() cur1.execute('delete from a') tx1a.commit() # tx1a.begin() cur1.execute( "select current_transaction, rdb$get_context('SYSTEM', 'ISOLATION_LEVEL') from rdb$database" ) cur1.fetchall() # --- con2 = act.db.connect() tx2a = con2.transaction_manager(dml_tpb) tx2b = con2.transaction_manager(ddl_tpb) # tx2a.begin() tx2b.begin() cur2 = tx2a.cursor() cur2.callproc('p_gen_tx', [33000]) tx2a.commit() tx2b.commit() # tx2a.begin() tx2b.begin() cur2.execute('insert into a (id) values (?)', [tx2a.info.id]) tx2a.commit() tx2b.commit() # tx2a.begin() tx2b.begin() cur2.execute('set statistics index idx_a') tx2a.commit() tx2b.commit() # tx2a.begin() tx2b.begin() cur2.execute( 'select rdb$index_name, rdb$record_version from rdb$indices where rdb$relation_name = ?', ['A']) cur2.fetchall() cur2.execute('select id from a where id > ?', [0]) cur2.fetchall() tx2a.commit() tx2b.commit() # tx2a.begin() tx2b.begin() cur2 = tx2a.cursor() cur2.callproc('p_gen_tx', [33000]) tx2a.commit() tx2b.commit() # --- tx1a.commit() # --- tx2a.begin() tx2b.begin() cur2.execute('select id from a where id > ?', [0]) cur2.fetchall() # --- tx1a.begin() cur1.execute('select id from a where id > ?', [0]) cur1.fetchall() # cur1.close() tx1a.rollback() con.close() # cur2.close() tx2a.rollback() tx2b.rollback() con2.close()