ExtConnPoolLifeTime acts as countdown for activity in MOST RECENT database (of
several) rather then separate for each of used databases
---------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-6182
URL: http://tracker.firebirdsql.org/browse/CORE-6182
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 4.0 Beta 1
Reporter: Pavel Zotov
According to %FB4_HOME%\doc\sql.extensions\README.external_connections_pool,
parameter ExtConnPoolLifeTime is "idle connection lifetime, seconds".
It seems that if 'main' database performs PSQL which uses EDS to several (>=2)
different databases then value of ExtConnPoolLifeTime is applied to the LAST
moment when activity was, regardless to some concrete database.
Connection(s) to some DB that was handled before any others will exist until
*all* activity will gone, rather than to *this* ("earliest") DB.
Steps to reproduce:
1) prepare firebird.conf:
========
Servermode = Super # or SuperClassic
ExtConnPoolSize = 10
ExtConnPoolLifeTime = 10
========
2) restart FB if it is running as service;
3) run following script (change path and names of temp databases if needed):
=======
set bail on;
shell del c:\temp\tmp4eds_01.fdb 2>nul;
shell del c:\temp\tmp4eds_02.fdb 2>nul;
shell del c:\temp\tmp4eds_03.fdb 2>nul;
shell del c:\temp\tmp4eds_04.fdb 2>nul;
shell del c:\temp\tmpmain.fdb 2>nul;
create database 'localhost:c:\temp\tmp4eds_01.fdb' user SYSDBA password
'masterkey';
commit;
create database 'localhost:c:\temp\tmp4eds_02.fdb' user SYSDBA password
'masterkey';
commit;
create database 'localhost:c:\temp\tmp4eds_03.fdb' user SYSDBA password
'masterkey';
commit;
create database 'localhost:c:\temp\tmp4eds_04.fdb' user SYSDBA password
'masterkey';
commit;
create database 'localhost:c:\temp\tmpmain.fdb' user SYSDBA password
'masterkey';
commit;
create table tdelay(id int primary key);
commit;
create sequence g;
commit;
set term ^;
create procedure sp_delay returns( dts1 timestamp, dts2 timestamp, elap_ms
int ) as
declare v_id int;
begin
v_id = gen_id(g,1);
dts1 = cast('now' as timestamp);
insert into tdelay(id) values( :v_id );
in autonomous transaction do
begin
insert into tdelay(id) values( :v_id );
when any do
begin
end
end
delete from tdelay where id = :v_id;
dts2 = cast('now' as timestamp);
elap_ms = datediff( millisecond from dts1 to dts2);
suspend;
end
^
create procedure sp_do_eds ( a_target_db varchar(128) ) returns( source_dts
timestamp, source_db varchar(128), target_db varchar(128), target_dts timestamp
) as
begin
source_dts = cast('now' as timestamp);
for
execute statement
('select cast(? as varchar(128)) as connect_from_db,
mon$database_name as connect_to_db, cast(''now'' as timestamp) from
mon$database')
( rdb$get_context('SYSTEM', 'DB_NAME') )
on external
'localhost:' || a_target_db
as
user 'sysdba'
password' masterkey'
into source_db, target_db, target_dts
do
suspend;
end
^
set term ;^
commit;
-----------------------------------------------------------
set list on;
set transaction lock timeout 8;
select * from sp_do_eds( 'c:\temp\tmp4eds_01.fdb' );
select p.* from sp_delay p;
commit;
set transaction lock timeout 8;
select * from sp_do_eds( 'c:\temp\tmp4eds_02.fdb' );
select p.* from sp_delay p;
commit;
set transaction lock timeout 8;
select * from sp_do_eds( 'c:\temp\tmp4eds_03.fdb' );
select p.* from sp_delay p;
commit;
set transaction lock timeout 8;
select * from sp_do_eds( 'c:\temp\tmp4eds_04.fdb' );
select p.* from sp_delay p;
commit;
set heading off;
set bail off;
drop database; ---------- tmpmain
-------------------------------------------
rollback;
connect 'localhost:c:\temp\tmp4eds_01.fdb';
select 'Point before DROP ' || rdb$get_context('SYSTEM', 'DB_NAME') || ': '
|| cast('now' as timestamp) from rdb$database;
set echo on;
drop database;
set echo off;
-------------------------------------------
rollback;
connect 'localhost:c:\temp\tmp4eds_02.fdb';
select 'Point before DROP ' || rdb$get_context('SYSTEM', 'DB_NAME') || ': '
|| cast('now' as timestamp) from rdb$database;
set echo on;
drop database;
set echo off;
-------------------------------------------
rollback;
connect 'localhost:c:\temp\tmp4eds_03.fdb';
select 'Point before DROP ' || rdb$get_context('SYSTEM', 'DB_NAME') || ': '
|| cast('now' as timestamp) from rdb$database;
set echo on;
drop database;
set echo off;
-------------------------------------------
rollback;
connect 'localhost:c:\temp\tmp4eds_04.fdb';
select 'Point before DROP ' || rdb$get_context('SYSTEM', 'DB_NAME') || ': '
|| cast('now' as timestamp) from rdb$database;
set echo on;
drop database;
set echo off;
=======
Console will be filled with messages
=======
SOURCE_DTS 2019-11-03 14:28:41.4960
SOURCE_DB C:\TEMP\TMPMAIN.FDB
TARGET_DB C:\TEMP\TMP4EDS_01.FDB
TARGET_DTS 2019-11-03 14:28:41.9100
-------------------------------------------------------- [ 1 ]
. . .
SOURCE_DTS 2019-11-03 14:29:06.5020
SOURCE_DB C:\TEMP\TMPMAIN.FDB
TARGET_DB C:\TEMP\TMP4EDS_04.FDB
TARGET_DTS 2019-11-03 14:29:06.7940
=======
-- and after this:
=======
CONCATENATION Point before DROP C:\TEMP\TMP4EDS_01.FDB:
2019-11-03 14:29:14.8240 --------------------- [ 2 ]
drop database;
Statement failed, SQLSTATE = 40001
lock time-out on wait transaction
-object C:\TEMP\TMP4EDS_01.FDB is in use
=======
So, we can see here that:
1) last ctivity in TMP4EDS_01.FDB was at "2019-11-03 14:28:41.9100" (see line
marked as "[ 1 ]" )
2) attempt to drop this DB was at 2019-11-03 14:29:14.8240 ( line "[ 2 ]" )
datediff() between these values is ~33 seconds. This is much more than 10
seconds which was assigned to ExtConnPoolLifeTime.
--
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
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
