FirebirdSQL logo
 System TablesSecurity tables 

Using MON$ATTACHMENTS to Kill a Connection

Monitoring tables are read-only.However, the server has a built-in mechanism for deleting (and only deleting) records in the MON$ATTACHMENTS table, which makes it possible to close a connection to the database.

Note
Notes
  • All the current activity in the connection being deleted is immediately stopped and all active transactions are rolled back

  • The closed connection will return an error with the isc_att_shutdown code to the application

  • Subsequent attempts to use this connection (i.e., use its handle in API calls) will return errors

  • Termination of system connections (MON$SYSTEM_FLAG = 1) is not possible.The server will skip system connections in a DELETE FROM MON$ATTACHMENTS.

Closing all connections except for your own (current):
DELETE FROM MON$ATTACHMENTS
WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION

MON$TABLE_STATS

MON$TABLE_STATS reports table-level statistics.

Column Name Data Type Description

MON$STAT_ID

INTEGER

Statistics identifier

MON$STAT_GROUP

SMALLINT

Statistics group:

0 - database
1 - connection
2 - transaction
3 - statement
4 - call

MON$TABLE_NAME

CHAR(63)

Name of the table

MON$RECORD_STAT_ID

INTEGER

Link to MON$RECORD_STATS

Getting statistics at the record level for each table for the current connection
SELECT
  t.mon$table_name,
  r.mon$record_inserts,
  r.mon$record_updates,
  r.mon$record_deletes,
  r.mon$record_backouts,
  r.mon$record_purges,
  r.mon$record_expunges,
  ------------------------
  r.mon$record_seq_reads,
  r.mon$record_idx_reads,
  r.mon$record_rpt_reads,
  r.mon$backversion_reads,
  r.mon$fragment_reads,
  ------------------------
  r.mon$record_locks,
  r.mon$record_waits,
  r.mon$record_conflicts,
  ------------------------
  a.mon$stat_id
FROM mon$record_stats r
JOIN mon$table_stats t ON r.mon$stat_id = t.mon$record_stat_id
JOIN mon$attachments a ON t.mon$stat_id = a.mon$stat_id
WHERE a.mon$attachment_id = CURRENT_CONNECTION

MON$TRANSACTIONS

MON$TRANSACTIONS reports started transactions.

Column Name Data Type Description

MON$TRANSACTION_ID

BIGINT

Transaction identifier (number)

MON$ATTACHMENT_ID

BIGINT

Connection identifier

MON$STATE

SMALLINT

Transaction state:

0 - idle
1 - active

MON$TIMESTAMP

TIMESTAMP WITH TIME ZONE

The date and time when the transaction was started

MON$TOP_TRANSACTION

BIGINT

Top-level transaction identifier (number)

MON$OLDEST_TRANSACTION

BIGINT

Transaction ID of the oldest [interesting] transaction (OIT)

MON$OLDEST_ACTIVE

BIGINT

Transaction ID of the oldest active transaction (OAT)

MON$ISOLATION_MODE

SMALLINT

Isolation mode (level):

0 - consistency (snapshot table stability)
1 - concurrency (snapshot)
2 - read committed record version
3 - read committed no record version
4 - read committed read consistency

MON$LOCK_TIMEOUT

SMALLINT

Lock timeout:

-1 - wait forever
0 - no waiting
1 or greater - lock timeout in seconds

MON$READ_ONLY

SMALLINT

Flag indicating whether the transaction is read-only (value 1) or read-write (value 0)

MON$AUTO_COMMIT

SMALLINT

Flag indicating whether automatic commit is used for the transaction (value 1) or not (value 0)

MON$AUTO_UNDO

SMALLINT

Flag indicating whether the logging mechanism automatic undo is used for the transaction (value 1) or not (value 0)

MON$STAT_ID

INTEGER

Statistics identifier

Getting all connections that started Read Write transactions with isolation level above Read Commited
SELECT DISTINCT a. *
FROM mon$attachments a
JOIN mon$transactions t ON a.mon$attachment_id = t.mon$attachment_id
WHERE NOT (t.mon$read_only = 1 AND t.mon$isolation_mode >= 2)

MON$COMPILED_STATEMENTS

Virtual table listing compiled statements.

Column Name Data Type Description

MON$COMPILED_STATEMENT_ID

BIGINT

Compiled statement id

MON$SQL_TEXT

BLOB TEXT

Statement text

MON$EXPLAINED_PLAN

BLOB TEXT

Explained query plan

MON$OBJECT_NAME

CHAR(63)

PSQL object name

MON$OBJECT_TYPE

SMALLINT

PSQL object type:

2 - trigger
5 - stored procedure
15 - stored function

MON$PACKAGE_NAME

CHAR(63)

PSQL object package name

MON$STAT_ID

INTEGER

Statistics identifier