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()
