Submitted by: @pavel-zotov

Attachments:
trace_no_sweep_starts_when_select_for_update_with_lock.zip

session #⁠1
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠
C:\1INSTALL\FIREBIRD\Data>isql localhost:C:\1INSTALL\FIREBIRD\Data\t1.fdb -n
Database: localhost:C:\1INSTALL\FIREBIRD\Data\t1.fdb
SQL> show database;
Database: localhost:C:\1INSTALL\FIREBIRD\Data\t1.fdb
Owner: SYSDBA
PAGE_SIZE 4096
Number of DB pages allocated = 196
Sweep interval = 20000
Forced Writes are OFF
Transaction - oldest = 3
Transaction - oldest active = 4
Transaction - oldest snapshot = 4
Transaction - Next = 6
ODS = 11.2
Default Character set: NONE

recreate table tfix1(id int primary key, s01 varchar(1000));
recreate table tfix2(id int primary key, s01 varchar(1000));
recreate table tfix3(id int primary key, s01 varchar(1000));
commit;
insert into tfix1 values(-1,'tfix1');
insert into tfix2 values(-2,'tfix2');
insert into tfix3 values(-2,'tfix2');
commit;

set transaction read committed;
set term ^;
execute block as
declare n int = 200000;
declare k int;
begin
while (n>0) do begin
insert into tfix1(id,s01) values(:n, rpad('',1000,'x'));
insert into tfix2(id,s01) values(:n, rpad('',1000,'x'));
insert into tfix3(id,s01) values(:n, rpad('',1000,'x'));
n=n-1;
end
end^
set term ;^
-- here we must wait a few minutes until this EB is finished ...

session #⁠2
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠
session #⁠2

C:\1INSTALL\FIREBIRD\Data>isql localhost:C:\1INSTALL\FIREBIRD\Data\t1.fdb -n
Database: localhost:C:\1INSTALL\FIREBIRD\Data\t1.fdb
commit;
set transaction read committed;
set term ^;
execute block as
declare n int = 21000;
declare k int;
begin
while (n>0) do begin
in autonomous transaction do select 1 from rdb$database into :k;
n=n-1;
end
end^
set term ;^
commit;
set list on;
select * from tfix1 where id<0 for update with lock;
set list off;
/*
after 1..2 seconds we will get:
ID -1
S01 tfix1
*/

session #⁠3
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠
start trace with enabled log_sweep.

session #⁠1
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠
SQL> rollback;
SQL> show database; -- this action finishes with COMMIT
Database: localhost:C:\1INSTALL\FIREBIRD\Data\t1.fdb
Owner: SYSDBA
PAGE_SIZE 4096
Number of DB pages allocated = 11810
Sweep interval = 20000
Forced Writes are OFF
Transaction - oldest = 5
Transaction - oldest active = 21009
Transaction - oldest snapshot = 6
Transaction - Next = 21011
ODS = 11.2
Default Character set: NONE
SQL> select 1 from rdb$database;

CONSTANT

============
1

-- sweep does NOT start here (no info about it in trace window)

session #⁠2
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠
SQL> commit;
SQL> set list on;
SQL> select * from tfix1 where id<0 for update with lock; -- sweep starts only now

ID -1
S01 tfix1

SQL> set list off;

See firebird.log and trace in attach.