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
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
