5

In SQL Server 2014, In Memory OLTP, What happens, if a transaction is rolled back and newly created version of row is no more required. Is it duty of garbage collector to remove even such type of rows or such garbage will be collected run time on transaction rollback ?

7

My understanding from the SIGMOD paper Hekaton: SQL Server’s Memory-Optimized OLTP Engine is that it gets handled the same as other types of garbage.

A couple of relevant sections are

6.2.3 Transaction Rollback

Transactions can be rolled back at user request or due to failures in commit processing. Rollback is achieved by invalidating all versions created by the transaction and clearing the end-timestamp field of all versions deleted by the transaction. If there are any other transactions dependent on the outcome of the rolled-back transaction, they are so notified. Again the write-set of the transaction is used to perform this operation very efficiently.

Note that it says invalidated not garbage collected.

Also see the Garbage Collection section (emphasis mine)

8.1.1 GC Correctness

First, care must be taken to identify which versions might be garbage. Potential garbage versions may be created by one of two processes. First, a version becomes garbage if a) it was deleted (via explicit DELETE or through an UPDATE operation) by a committed transaction and b) the version cannot be read or otherwise acted upon by any transaction in the system. A second, and less common way for versions to become garbage is if they were created by a transaction that subsequently rolls back. The first and most important property of the GC is that it correctly determines which versions are actually garbage. The visibility of a version is determined by its begin and end timestamps. Any version whose end timestamp is less than the current oldest active transaction in the system is not visible to any transaction and can be safely discarded. A GC thread periodically scans the global transaction map to determine the begin timestamp of the oldest active transaction in the system. When the GC process is notified that it should begin collection, transactions committed or aborted since the last GC cycle are ordered by their end timestamps.

But in practice it does seem the garbage from rollbacks is dispensed with quicker than for commits so perhaps the aborting thread does clean up its own garbage at least some of the time.

To see this I created the following proc (in a memory optimised database)

CREATE PROC GetStats
AS
SELECT *
FROM   sys.dm_db_xtp_table_memory_stats
WHERE  object_id = object_id('dbo.Test')

SELECT name AS 'index_name',
       s.*
FROM   sys.dm_db_xtp_hash_index_stats s
       JOIN sys.indexes i
         ON s.object_id = i.object_id
            AND s.index_id = i.index_id
WHERE  i.object_id = object_id('dbo.Test')

SELECT name AS 'index_name',
       s.index_id,
       scans_started,
       rows_returned,
       rows_expired,
       rows_expired_removed
FROM   sys.dm_db_xtp_index_stats s
       JOIN sys.indexes i
         ON s.object_id = i.object_id
            AND s.index_id = i.index_id
WHERE  object_id('dbo.Test') = s.object_id;

And then ran the following, experimenting with toggling the ROLLBACK and COMMIT

CREATE TABLE [dbo].[Test]
  (
     [Id]     [INT] NOT NULL CONSTRAINT PK PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 8),
     [String] CHAR(900) COLLATE Latin1_General_100_BIN2 NOT NULL INDEX IX NONCLUSTERED HASH WITH (BUCKET_COUNT = 8)
  ) WITH ( MEMORY_OPTIMIZED = ON )

INSERT INTO [dbo].[Test]
VALUES
    (1,REPLICATE('A',900)),
    (2,REPLICATE('A',900)),
    (3,REPLICATE('A',900)),
    (4,REPLICATE('A',900)),
    (5,REPLICATE('A',900)),
    (6,REPLICATE('A',900)),
    (7,REPLICATE('A',900)),
    (8,REPLICATE('A',900))

/*Get initial position*/
EXEC GetStats

BEGIN TRAN
    UPDATE [dbo].[Test]
    SET    [String] = REPLICATE('B', 900)

/*Mid Transaction*/
EXEC GetStats

COMMIT    

/*Force a scan of both indexes*/
SELECT COUNT(*)
FROM   dbo.Test WITH (INDEX=IX)
EXCEPT
SELECT COUNT(*)
FROM   dbo.Test WITH (INDEX = PK)

EXEC GetStats

DROP TABLE [dbo].[Test]

I consistently got results summarized as follows.

The effect of the UPDATE was the same for both committed and rolled back transactions on memory_allocated_for_table_kb (Grew from 7KB to 15KB)

Bucket stats

The initial bucket stats for both tests was the same with

+------------+--------------------+--------------------+------------------+------------------+
| index_name | total_bucket_count | empty_bucket_count | avg_chain_length | max_chain_length |
+------------+--------------------+--------------------+------------------+------------------+
| IX         |                  8 |                  7 |                8 |                8 |
| PK         |                  8 |                  7 |                8 |                8 |
+------------+--------------------+--------------------+------------------+------------------+

Mid transaction (before rollback or commit) the bucket counts are

+------------+--------------------+--------------------+------------------+------------------+
| index_name | total_bucket_count | empty_bucket_count | avg_chain_length | max_chain_length |
+------------+--------------------+--------------------+------------------+------------------+
| IX         |                  8 |                  6 |                8 |                8 |
| PK         |                  8 |                  7 |               16 |               16 |
+------------+--------------------+--------------------+------------------+------------------+

Following the rollback they instantly revert to the initial count but after the commit they remain as in the second table showing that garbage is now present.

The single bucket that apparently all the PK values hash to now has 16 linked rows rather than the previous 8.

And now the hash index on the other column has two buckets in use with 8 linked rows in each (for both the "before" and "after" string values of AAA... and BBB...)

Rows Expired

After Commit

+------------+----------+---------------+---------------+--------------+----------------------+
| index_name | index_id | scans_started | rows_returned | rows_expired | rows_expired_removed |
+------------+----------+---------------+---------------+--------------+----------------------+
| IX         |        2 |             1 |             8 |            8 |                    0 |
| PK         |        3 |            10 |            16 |            8 |                    0 |
+------------+----------+---------------+---------------+--------------+----------------------+

After Rollback

+------------+----------+---------------+---------------+--------------+----------------------+
| index_name | index_id | scans_started | rows_returned | rows_expired | rows_expired_removed |
+------------+----------+---------------+---------------+--------------+----------------------+
| IX         |        2 |             1 |             8 |            8 |                    8 |
| PK         |        3 |            10 |            16 |            8 |                    8 |
+------------+----------+---------------+---------------+--------------+----------------------+

The rows_expired is 8 in both cases but rows_expired_removed is 0 following the transaction commit whereas all of them were removed after the rollback.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.