ES/EDS and ExtConnPool problem: EDS-connection may see old value of generator 
if it is established twise and interval is less than ExtConnPoolLifeTime (even 
when generator is RECREATED every time)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                 Key: CORE-6464
                 URL: http://tracker.firebirdsql.org/browse/CORE-6464
             Project: Firebird Core
          Issue Type: Bug
          Components: Engine
    Affects Versions: 4.0 Beta 2
            Reporter: Pavel Zotov
         Attachments: extpool-and-sequence.bat.7z

Steps to reproduce:

1) open firebird.conf and set ExtConnPoolLifeTime to value about 5...10 (I used 
5); restart FB 4.x.

2) unpack batch from attached .7z; open it in editor and adjust following 
variables:
=====
fbc // path to isql.exe from FB 4.x
dbnm // name of temporary database
usr // SYSDBA
psw // masterkey
=====

This batch will create temporary database and .sql which does:
=======
1) connect to this temp database;
2) run 'RECREATE SEQUENCE g_eds_test;'
3) creates procedure 'sp_increment_sequence'  (its name speaks for itself)
4) RECONNECT to database;
5) run procedure 'sp_increment_sequence' with input arg. = 123.
6) check that final value of sequence 'g_eds_test' is 123
=======

Then batch will parse firebird.conf (from !fbc!\ folder) and take value 
ExtConnPoolLifeTime  from there (it must be uncommented, of course).

Finally, it calls just created .sql  *three* times, with different interval 
between second and third: firs interval = (ExtConnPoolLifeTime + 1), second is  
(ExtConnPoolLifeTime - 1), i.e.:
=======
t0 --> run #1
t1 = return from sql (i.e. finish of run #1)

.... wait for (ExtConnPoolLifeTime + 1) seconds ....

t2 =  t1 + (ExtConnPoolLifeTime + 1) seconds --> run #2
t3 = return from sql (i.e. finish of run #2)

.... wait for (ExtConnPoolLifeTime - 1) seconds ....

t4 =  t3 + (ExtConnPoolLifeTime - 1) seconds --> run #3
=======
Built-in Windows utility 'timeout' is used for pausesbetween launches of isql.


Here is the content of SQL:
=======
set bail on;
set heading off;
set list on;
connect 'localhost:c:\temp\tmp4test.fdb' user SYSDBA password 'masterkey';
create or alter procedure sp_increment_sequence(a_inc int = null) as begin end;

recreate view v_check as
select cast('now' as timestamp) dts, coalesce( q'{YES, '}' || 
trim(g.rdb$generator_name) || q'{' exists.}', q'{NO, it doesn't}') as "Does 
sequence 'g_eds_test' exists ? =>"
from rdb$database r
left join rdb$generators g on upper(g.rdb$generator_name) = upper('g_eds_test');
commit;

select 'point-1' as msg, v.* from v_check v;
recreate sequence g_eds_test;
commit;
select 'point-2' as msg, v.* from v_check v;

select gen_id(g_eds_test, 0) as init_seq_value from rdb$database;
commit;

set term #;
create or alter procedure sp_increment_sequence(a_inc int = null) 
returns(gen_value_before_inc int, gen_value_after__inc int) as
    declare c int;
begin
    gen_value_before_inc = gen_id(g_eds_test,0);
    c = gen_id( g_eds_test, coalesce(a_inc,1) );
    gen_value_after__inc = gen_id(g_eds_test,0);
    suspend;
end
#
commit
#

--  ### RECONNECT ###
connect 'localhost:c:\temp\tmp4test.fdb' user SYSDBA password 'masterkey'
#

select gen_id(g_eds_test, 0) as seq_value_after_reconnect from rdb$database
#
commit
#

execute block returns(gen_value_before_inc int, gen_value_after__inc int) as
begin
  execute statement ( 'select gen_value_before_inc, gen_value_after__inc from 
sp_increment_sequence( ? )' ) ( 123 )
  on external 'localhost:' || rdb$get_context('SYSTEM','DB_NAME') as user 
'SYSDBA' password 'masterkey'
  -- role 'A' || replace(lpad('',30,uuid_to_char(gen_uuid())),'-','') 
--------------------------------------------- ::::: NB :::::
  into gen_value_before_inc, gen_value_after__inc
  ;
  suspend;
end
#
set term ;#

select iif( gen_id(g_eds_test, 0) > 0, 'Expected: ' || gen_id(g_eds_test,0), 
'UNEXPECTED >>> ' || gen_id(g_eds_test,0) || ' <<< ?' ) as final_seq_value from 
rdb$database;
commit;

drop procedure sp_increment_sequence;
drop sequence g_eds_test;
commit;

select 'point-3' as msg, v.* from v_check v;
commit;

set list off;
set heading off;
select '=== bye-bye from SQL ===' from rdb$database;
=======

PLEASE NOTE ON COMMENTED LINE:
"  -- role 'A' || replace(lpad('',30,uuid_to_char(gen_uuid())),'-','') 
--------------------------------------------- ::::: NB :::::"


Run this batch.

This is result of batch when it runs with  ExtConnPoolLifeTime  = 5:
################################
MSG                             point-1
DTS                             2021-01-07 10:30:04.5180
Does sequence 'g_eds_test' exists ? => NO, it doesn't

MSG                             point-2
DTS                             2021-01-07 10:30:04.5290
Does sequence 'g_eds_test' exists ? => YES, 'G_EDS_TEST' exists.

INIT_SEQ_VALUE                  0

SEQ_VALUE_AFTER_RECONNECT       0

GEN_VALUE_BEFORE_INC            0
GEN_VALUE_AFTER__INC            123

FINAL_SEQ_VALUE                 Expected: 123

MSG                             point-3
DTS                             2021-01-07 10:30:05.0920
Does sequence 'g_eds_test' exists ? => NO, it doesn't

=== bye-bye from SQL === 


Take delay for 6 s, i.e. ### GREATER ### than ECP lifetime. . .

Waiting for 6 seconds, press a key to continue ...5 4 3 2 1 0

Check result after "long delay":

MSG                             point-1
DTS                             2021-01-07 10:30:11.5640
Does sequence 'g_eds_test' exists ? => NO, it doesn't


MSG                             point-2
DTS                             2021-01-07 10:30:11.5790
Does sequence 'g_eds_test' exists ? => YES, 'G_EDS_TEST' exists.

INIT_SEQ_VALUE                  0

SEQ_VALUE_AFTER_RECONNECT       0

GEN_VALUE_BEFORE_INC            0
GEN_VALUE_AFTER__INC            123

FINAL_SEQ_VALUE                 Expected: 123

MSG                             point-3
DTS                             2021-01-07 10:30:12.1600
Does sequence 'g_eds_test' exists ? => NO, it doesn't

=== bye-bye from SQL === 


Take delay for 4 s, i.e. ### SHORTER ### than ECP lifetime. . .

Waiting for 4 seconds, press a key to continue ...3 2 1 0

Check result after "short delay":

MSG                             point-1
DTS                             2021-01-07 10:30:16.2370
Does sequence 'g_eds_test' exists ? => NO, it doesn't


MSG                             point-2
DTS                             2021-01-07 10:30:16.2560
Does sequence 'g_eds_test' exists ? => YES, 'G_EDS_TEST' exists.


INIT_SEQ_VALUE                  0

SEQ_VALUE_AFTER_RECONNECT       0

GEN_VALUE_BEFORE_INC            123
GEN_VALUE_AFTER__INC            246

FINAL_SEQ_VALUE                 UNEXPECTED >>> 0 <<< ?

MSG                             point-3
DTS                             2021-01-07 10:30:16.3380
Does sequence 'g_eds_test' exists ? => NO, it doesn't

=== bye-bye from SQL === 
################################

As you can see, sequence is recreated every time. 
But its *OLD* value (123) still visible to procedure 'sp_increment_sequence' 
when it is called via ES/EDS and - most important -  time from previous call is 
less then ExtConnPoolLifeTime.

If we uncomment line "-- role 'A' || 
replace(lpad('',30,uuid_to_char(gen_uuid())),'-','')" and repeat than result is 
expected:
=====
GEN_VALUE_BEFORE_INC            0
GEN_VALUE_AFTER__INC            123
FINAL_SEQ_VALUE                 Expected: 123
=====

(using ROLE clause with random value forces EDS mechanism to create totally new 
connection, AFAIK).


PS. WI-V4.0.0.2307 Firebird 4.0 Release Candidate 1


-- 
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

Reply via email to