[Firebird-devel] [FB-Tracker] Created: (CORE-3922) Sweep does not start if one of connects doing select * from ... where ... FOR UPDATE WITH LOCK without COMMIT or ROLLBACK

[Pavel Z. (JIRA) <tr...@fi...>]

Sweep does not start if one of connects doing select * from  ... where ... FOR UPDATE WITH LOCK without COMMIT or ROLLBACK
--------------------------------------------------------------------------------------------------------------------------

                 Key: CORE-3922
                 URL: http://tracker.firebirdsql.org/browse/CORE-3922
             Project: Firebird Core
          Issue Type: Bug
          Components: Engine
            Reporter: 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira