LOCK_HEADER BLOCK
        Version: 146, Creation timestamp: 2021-07-03 19:37:07
        Active owner:      0, Length: 26214400, Used: 11458184
        Enqs: 2251485914, Converts: 9947181, Rejects: 158444555, Blocks: 9267903
        Deadlock scans:  35238, Deadlocks:      2, Scan interval:  10
        Acquires: 4532677690, Acquire blocks: 76914749, Spin count:   0
        Mutex wait: 1.7%
        Hash slots: 30011, Hash lengths (min/avg/max):    0/   0/   6
        Remove node:      0, Insert queue:      0, Insert prior:      0
        Owners (368):   forward: 253144, backward: 2563376
        Free owners (328):      forward: 11065808, backward: 11105184
        Free locks (1553):      forward: 253672, backward: 1478904
        Free requests (39402):  forward: 4485968, backward: 3450704

props.setProperty("TRANSACTION_READ_COMMITTED", "isc_tpb_read_committed,isc_tpb_rec_version,isc_tpb_wait");

Database header page information:
        Flags                   0
        Generation              47045267
        System Change Number    0
        Page size               16384
        ODS version             12.0
        Oldest transaction      46994210
        Oldest active           46994211
        Oldest snapshot         46994211
        Next transaction        46994212
        Sequence number         0
        Next attachment ID      1398472
        Implementation          HW=AMD/Intel/x64 little-endian OS=Linux CC=gcc
        Shadow count            0
        Page buffers            1048576
        Next header page        0
        Database dialect        3
        Creation date           Jul 19, 2021 11:56:07
        Attributes              force write

    Variable header data:
        Sweep interval:         0
        *END*

CREATE TABLE tbl (
    ID                bigint NOT NULL,
    ID_LOG    		  bigint NOT NULL,
    ID_SBSCR  		  bigint NOT NULL,
    STATE             int DEFAULT 0 NOT NULL,
    IS_SEND           int DEFAULT 0,
    DT_SEND           timestamp
);



/******************************************************************************/
/***                              Primary keys                              ***/
/******************************************************************************/

ALTER TABLE tbl ADD CONSTRAINT PK_tbl PRIMARY KEY (ID);


/******************************************************************************/
/***                              Foreign keys                              ***/
/******************************************************************************/

ALTER TABLE tbl ADD CONSTRAINT FK_tbl_LOG FOREIGN KEY (ID_LOG) REFERENCES tbl_log (ID);
ALTER TABLE tbl ADD CONSTRAINT FK_tbl_SBSCR FOREIGN KEY (ID_SBSCR) REFERENCES tbl_subscriber (ID);


/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE INDEX idx1 ON tbl (ID_SBSCR, STATE);
CREATE INDEX idx2 ON tbl (ID_SBSCR, IS_SEND, STATE);

create or alter procedure LOG_GET (
    ID_LOG_LATEST bigint,
    ID_SUBSCR bigint)
returns (
    id bigint,
    log_unit blob)
as
declare variable MAX_COUNT_LOG_SEND bigint = 100;
declare variable CNT_SND integer = 1;
begin
  update tbl_subscriber
  set ID_LOG_LATEST = :ID_LOG_LATEST
  where ID = :ID_SUBSCR;

  update tbl
  set STATE = 1
  where ID_LOG + 0 <= :ID_LOG_LATEST and
        ID_SBSCR = :ID_SUBSCR and
        IS_SEND = 1 and
        STATE = 0;

  select param
  from cfg
  where name = 'MAX_COUNT_LOG_SEND'
  into :MAX_COUNT_LOG_SEND;

  for select id_log,
             log_unit 
      from tbl T2
      left join tbl_log T1 on T1.ID = T2.ID_LOG
      where T2.ID_SBSCR = :ID_SUBSCR and
            T2.STATE = 0
      order by T2.ID_LOG + 0 asc
      into :id, :log_unit
  do
  begin
    if (:CNT_SND <= :MAX_COUNT_LOG_SEND) then
    begin
      update tbl
      set IS_SEND = 1,
          DT_SEND = localtimestamp
      where ID_LOG = :ID and
            ID_SBSCR = :ID_SUBSCR;

      CNT_SND = :CNT_SND + 1;
      suspend;
    end
    else
    begin
      break;
    end
  end
end