Submitted by: @pavel-zotov

Attachments:
unable_querying_mon_tables_when_huge_table_is_updated_-_logs.zip

Votes: 1

/* I've opened this ticked instead of continuing reports to old CORE3977;
Dmitry told me that this should be new one.
*/

The following scenario leads any new attempts to establish new connect to be failed.

1. database with single table that have 10E9 rows:

SQL> show table t; show index;
ID INTEGER Nullable
S01 VARCHAR(8) Nullable
S02 VARCHAR(8) Nullable
T_ID INDEX ON T(ID)
T_S01_S02 INDEX ON T(S01, S02)

2. Start trace
3. Five ISQLs that all connects to this database via TCP and started (almost at one time) commands:

session #⁠ 1
set plan on; set stat on; update t set s01=s02, s02=s01 where id between 1 and 100000000;

session #⁠ 2
set plan on; set stat on; update t set s01=s02, s02=s01 where id between 100000001 and 200000000;

session #⁠ 3
set plan on; set stat on; update t set s01=s02, s02=s01 where id between 200000001 and 300000000;

session #⁠ 4
set plan on; set stat on; update t set s01=s02, s02=s01 where id between 300000001 and 400000000;

session #⁠ 5
set plan on; set stat on; update t set s01=s02, s02=s01 where id between 400000001 and 500000000;

4. After all these 5 connects reflects in the trace that they STARTED statements I run 6th ISQL with the following script as input:

set list off;
commit;
select current_timestamp dts, i.* from mon$io_stats i;
select current_timestamp dts, r.* from mon$record_stats r;
quit;

This "monitoring" ISQL hanged - no output was during ~20...30 minutes.

5. Take snapshot of firebird backtrace (via gdb) and lock print (via fb_lock_print -a -c -m)

6. Then I shutdown this database like this: gfix -shut -force 0.
The trace shows that database goes in shutdown mode but all ISQLs are still hangs. No output at all.

7. Attempt to connect via ISQL to another database on that host - and could not do this. Firebird did not answered.

All reports please see in attach.

Commits: dc7aad8 FirebirdSQL/fbt-repository@18b8726

====== Test Details ======

Huge table required. It's not possible to store such database on test machine.