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