AT Modes

AT modes are used to control the transactional behaviour of a query. An “AT mode” can be specified for a SELECT query in all versions of Kognitio. Prior to Kognitio version 8.1.50 this could only be one of:

  • AT_NOW - run point in time from the start of the current transaction without getting locks. This is the default in version 8.1.50, 8.2 and above
  • AT_FULL_HISTORY - run from disk and see deleted rows
  • AT_CURRENT_TRANSACTION - get locks and run normally. This is the default in all versions prior to 8.1.50

These AT modes actually control several behaviours and from version 8.1.50 onwards Kognitio have added flags to achieve these behaviours individually. For version 8.1.50 onwards the ‘AT mode’ is a combination of comma separated values which can be chosen from the above and the following new modes:

  • AT_IGNORE_LOCKS - don’t obtain locks on user objects
  • AT_FORCE_DISK - get rows from disk, ignoring table/view images
  • AT_FORCE_RANDOM - treat ram image as random; thus if it is replicated you will get a copy of each row from each ramstore. wx_ram_proc() and wx_ram_addr() are useful here.
  • AT_INCLUDE_DELETED - include rows that were deleted prior to the current transaction (i.e. show deleted rows). The functions WX_CREATE_TNO() and WX_UPDATE_TNO() are useful when this AT mode is used
  • AT_INCLUDE_UNCOMMITTED - include rows that have been created since the start of the current transaction (i.e. show uncommitted rows). The function WX_CREATE_TNO() is useful here.

In Kognitio versions 8.1.50, 8.2 and above the default AT mode for SELECT queries with server-side autocommit (requires >= 8.1.50 ODBC driver) has been changed to AT NOW so that sessions just doing SELECT queries don’t get shared locks.

The default AT mode is controlled with the parameter default_at_now. On install this is set to 2 (AT NOW). Set default_at_now to 0 to get shared locks (the old default behaviour). Note subquery selects within updates (for example) will still get shared locks.

Usage:

[WITH ...]
SELECT ...
[FROM ...]
[WHERE ...]
[GROUP BY ...]
[HAVING ...]
[AT <at_mode>]

Examples:

Create a simple table with a single integer column, insert 0,1 and 2 into it. Then delete 0 and 2 and then re-insert 2:

CREATE TABLE my_table (c1 INT) REPLICATED;
INSERT INTO my_table VALUES BETWEEN 0 and 2;  -- Insert 0,1,2
DELETE FROM my_table WHERE c1 <> 1;           -- Delete 0,2
INSERT INTO my_table VALUES (2);              -- Insert 2 again

Now investigate different AT-modes behaviour. The default AT-mode is AT NOW and will return 2 rows:

SELECT WX_CREATE_TNO(),
       WX_UPDATE_TNO(),
       *
FROM my_table
ORDER BY 3,1,2;

-- 2 rows returned e.g:
-- (599, 2147483647, 1)
-- (601, 2147483647, 2)

The value 1 was inserted in transaction 599 and the value 2 was inserted in transaction 601. In the update information 2147483647 indicates that a row has never been updated . In this case the INSERT was first and only for these rowsNeither of these rows has been updated since they were inserted: only the second insert for the value 2 is shown.

Using AT-mode AT INCLUDE_DELETED will return 4 rows, the 2 rows still present (as above) plus the 2 rows that were deleted:

SELECT WX_CREATE_TNO(),
       WX_UPDATE_TNO(),
       *
FROM my_table
ORDER BY 3,1,2
AT INCLUDE_DELETED;

-- 4 rows returned,
-- (599, 600, 0)
-- (599, 2147483647, 1)
-- (599, 600, 2)
-- (601, 2147483647, 2)

The 2 DELETED rows were removed by transaction number 600 this is shown as an update.

Using AT-mode AT FORCE_RANDOM we see there are 4 ramstores on this system and we get 14 rows returned:

SELECT wx_ram_proc(),
       *
FROM my_table
AT FORCE_RANDOM;

-- 8 rows returned,
-- (0, 1)
-- (0, 2)
-- (1, 1)
-- (1, 2)
-- (2, 1)
-- (2, 2)
-- (3, 1)
-- (3, 2)

It is possible to see the difference in forcing access from disk by looking at the query plans via PICTURE function:

PICTURE SELECT *
        FROM my_table;

PICTURE SELECT *
        FROM my_table
        AT FORCE_DISK;