On 03/12/16 12:50, Hamish Moffatt hamish@...
[firebird-support] wrote:

>
> I'm also seeing idle transactions in MON$TRANSACTIONS sometimes. That
> is presumably the cause of my big issue, which is that I am reading
> back old data from the db.

OK, here's where I'm confused. The last statement executed by each
connection seems to stay listed, idle, in MON$STATEMENTS.

For each statement I execute, the database driver is doing

- isc_start_transaction
- isc_allocate_statement
- isc_prepare_statement
- isc_dsql_execute
- isc_commit_transaction
- isc_dsql_free_statement with DSQL_drop

Now I've enabled the fbtracemgr and what I see is that the FREE
STATEMENT does not happen until the next transaction is started. The
free_statement call causes a CLOSE_CURSOR event in the trace but the
FREE_STATEMENT does not come until the next transaction starts.

I've attached from two executes of "SELECT 1 FROM RDB$DATABASE". You can
hopefully see from the timestamps that the FREE_STATEMENT never starts
until just before the next START_TRANSACTION.

Anyway, is this all as expected? Maybe it's not even important.

What I am seeing is that a thread is getting old data from a SELECT,
suggesting that there's an active transaction/statement somewhere
keeping it pinned to old data. I should not have any such lingering
transactions/statements though.



thanks,


Hamish



----------

2016-12-03T16:30:40.9030 (12132:0279A674) TRACE_INIT
SESSION_4


2016-12-03T16:30:40.9040 (12132:0279A674) START_TRANSACTION
C:\DEV\WEBAPP_DEV\PROJECTS\SERVER\WEBAPP-SERVER\RISING5.FDB (ATT_886, SYSDBA:NONE, UTF8, TCPv4:127.0.0.1)
C:\dev\webapp_dev\projects\server\webapp-server\debug\webapp_server.exe:23260
(TRA_72718, CONCURRENCY | WAIT | READ_WRITE)

2016-12-03T16:30:51.8920 (12132:0279A674) PREPARE_STATEMENT
C:\DEV\WEBAPP_DEV\PROJECTS\SERVER\WEBAPP-SERVER\RISING5.FDB (ATT_886, SYSDBA:NONE, UTF8, TCPv4:127.0.0.1)
C:\dev\webapp_dev\projects\server\webapp-server\debug\webapp_server.exe:23260
(TRA_72718, CONCURRENCY | WAIT | READ_WRITE)

Statement 1130:
-------------------------------------------------------------------------------
SELECT 1 FROM RDB$DATABASE
0 ms

2016-12-03T16:30:51.8930 (12132:0279A674) EXECUTE_STATEMENT_START
C:\DEV\WEBAPP_DEV\PROJECTS\SERVER\WEBAPP-SERVER\RISING5.FDB (ATT_886, SYSDBA:NONE, UTF8, TCPv4:127.0.0.1)
C:\dev\webapp_dev\projects\server\webapp-server\debug\webapp_server.exe:23260
(TRA_72718, CONCURRENCY | WAIT | READ_WRITE)

Statement 1130:
-------------------------------------------------------------------------------
SELECT 1 FROM RDB$DATABASE

2016-12-03T16:30:51.8940 (12132:0279A674) COMMIT_TRANSACTION
C:\DEV\WEBAPP_DEV\PROJECTS\SERVER\WEBAPP-SERVER\RISING5.FDB (ATT_886, SYSDBA:NONE, UTF8, TCPv4:127.0.0.1)
C:\dev\webapp_dev\projects\server\webapp-server\debug\webapp_server.exe:23260
(TRA_72718, CONCURRENCY | WAIT | READ_WRITE)
1 ms, 1 write(s), 1 fetch(es), 1 mark(s)

2016-12-03T16:30:51.8940 (12132:0279A674) CLOSE_CURSOR
C:\DEV\WEBAPP_DEV\PROJECTS\SERVER\WEBAPP-SERVER\RISING5.FDB (ATT_886, SYSDBA:NONE, UTF8, TCPv4:127.0.0.1)
C:\dev\webapp_dev\projects\server\webapp-server\debug\webapp_server.exe:23260

Statement 1130:
-------------------------------------------------------------------------------
SELECT 1 FROM RDB$DATABASE

2016-12-03T16:32:01.7590 (12132:0279A674) FREE_STATEMENT
C:\DEV\WEBAPP_DEV\PROJECTS\SERVER\WEBAPP-SERVER\RISING5.FDB (ATT_886, SYSDBA:NONE, UTF8, TCPv4:127.0.0.1)
C:\dev\webapp_dev\projects\server\webapp-server\debug\webapp_server.exe:23260

Statement 1130:
-------------------------------------------------------------------------------
SELECT 1 FROM RDB$DATABASE

2016-12-03T16:32:01.7600 (12132:0279A674) START_TRANSACTION
C:\DEV\WEBAPP_DEV\PROJECTS\SERVER\WEBAPP-SERVER\RISING5.FDB (ATT_886, SYSDBA:NONE, UTF8, TCPv4:127.0.0.1)
C:\dev\webapp_dev\projects\server\webapp-server\debug\webapp_server.exe:23260
(TRA_72719, CONCURRENCY | WAIT | READ_WRITE)

2016-12-03T16:32:16.7560 (12132:0279A674) PREPARE_STATEMENT
C:\DEV\WEBAPP_DEV\PROJECTS\SERVER\WEBAPP-SERVER\RISING5.FDB (ATT_886, SYSDBA:NONE, UTF8, TCPv4:127.0.0.1)
C:\dev\webapp_dev\projects\server\webapp-server\debug\webapp_server.exe:23260
(TRA_72719, CONCURRENCY | WAIT | READ_WRITE)

Statement 1131:
-------------------------------------------------------------------------------
SELECT 1 FROM RDB$DATABASE
0 ms

2016-12-03T16:32:16.7560 (12132:0279A674) EXECUTE_STATEMENT_START
C:\DEV\WEBAPP_DEV\PROJECTS\SERVER\WEBAPP-SERVER\RISING5.FDB (ATT_886, SYSDBA:NONE, UTF8, TCPv4:127.0.0.1)
C:\dev\webapp_dev\projects\server\webapp-server\debug\webapp_server.exe:23260
(TRA_72719, CONCURRENCY | WAIT | READ_WRITE)

Statement 1131:
-------------------------------------------------------------------------------
SELECT 1 FROM RDB$DATABASE

2016-12-03T16:32:16.7580 (12132:0279A674) COMMIT_TRANSACTION
C:\DEV\WEBAPP_DEV\PROJECTS\SERVER\WEBAPP-SERVER\RISING5.FDB (ATT_886, SYSDBA:NONE, UTF8, TCPv4:127.0.0.1)
C:\dev\webapp_dev\projects\server\webapp-server\debug\webapp_server.exe:23260
(TRA_72719, CONCURRENCY | WAIT | READ_WRITE)
1 ms, 1 write(s), 1 fetch(es), 1 mark(s)

2016-12-03T16:32:16.7580 (12132:0279A674) CLOSE_CURSOR
C:\DEV\WEBAPP_DEV\PROJECTS\SERVER\WEBAPP-SERVER\RISING5.FDB (ATT_886, SYSDBA:NONE, UTF8, TCPv4:127.0.0.1)
C:\dev\webapp_dev\projects\server\webapp-server\debug\webapp_server.exe:23260

Statement 1131:
-------------------------------------------------------------------------------
SELECT 1 FROM RDB$DATABASE

2016-12-03T16:32:34.7380 (12132:0279A674) FREE_STATEMENT
C:\DEV\WEBAPP_DEV\PROJECTS\SERVER\WEBAPP-SERVER\RISING5.FDB (ATT_886, SYSDBA:NONE, UTF8, TCPv4:127.0.0.1)
C:\dev\webapp_dev\projects\server\webapp-server\debug\webapp_server.exe:23260

Statement 1131:
-------------------------------------------------------------------------------
SELECT 1 FROM RDB$DATABASE

2016-12-03T16:32:34.7390 (12132:0279A674) START_TRANSACTION
C:\DEV\WEBAPP_DEV\PROJECTS\SERVER\WEBAPP-SERVER\RISING5.FDB (ATT_886, SYSDBA:NONE, UTF8, TCPv4:127.0.0.1)
C:\dev\webapp_dev\projects\server\webapp-server\debug\webapp_server.exe:23260
(TRA_72720, CONCURRENCY | WAIT | READ_WRITE)

[Non-text portions of this message have been removed]