UPDATE 2: What does UncommitOp include?

[Breck_Carter]

Update 2: The test code in Update 1 originally contained a "no operation" update operation when "UPDATE t SET data = 1" updated the first row, causing the UncommitOp value to return 1019 instead of 1020.

That has been changed to "UPDATE t SET data = 999" to make it clear the word should be "equal" rather than "roughly equal"

---

Update 1: Does the connection-level UncommitOp property equal the number of uncommitted row-level INSERT, UPDATE and DELETE operations?

Does UncommitOp include any other operations?

The following test shows that UncommitOp returns 1020 after four SQL statements insert, update (twice) and delete all 255 rows in a table.

CREATE TABLE t ( pkey INTEGER PRIMARY KEY, data INTEGER );

INSERT t SELECT row_num, row_num FROM RowGenerator;
UPDATE t SET data = 999;
UPDATE t SET data = 2;
DELETE FROM t;

SELECT @@VERSION, 
       CONNECTION_PROPERTY ( 'UncommitOp' ) AS UncommitOp, 
       CONNECTION_PROPERTY ( 'TransactionStartTime' ) AS TransactionStartTime;

@@VERSION,UncommitOp,TransactionStartTime
'17.0.4.2100','1020','2016-08-21 06:04:19.657'

---

Orginal question...

Exactly what does the connection-level UncommitOp property include?

The Help says "the number of uncommitted operations".

1 - Does that mean it counts the outstanding INSERT, UPDATE and DELETE SQL statements?

Testing seems to indicate "no" (see below).

2 - Does it count the affected rows? (which may be different from the number of statements)

Testing seems to indicate "yes".

3 - Does it count SELECT statements that obtain schema locks which are cleared by a subsequent COMMIT statements?

Testing seems to indicate "no".

4 - Does it count anything else? ( that's a "no coffee yet" question 🙂

5 - Is UncommitOp related to the connection-level TransactionStartTime property? AFAIK TransactionStartTime is not set for a SELECT that grabs a schema lock, so by definition a SELECT does not "start a transaction".

CREATE TABLE t ( pkey INTEGER PRIMARY KEY, data INTEGER );
INSERT t VALUES ( 1, 1 ), ( 2, 2 );
SELECT @@VERSION, 
       CONNECTION_PROPERTY ( 'UncommitOp' ) AS UncommitOp, 
       CONNECTION_PROPERTY ( 'TransactionStartTime' ) AS TransactionStartTime, 
       * 
  FROM sa_locks();
@@VERSION,UncommitOp,TransactionStartTime,conn_name,conn_id,user_id,table_type,creator,table_name,index_id,lock_class,lock_duration,lock_type,row_identifier
'17.0.4.2053','2','2016-08-18 04:23:44.383','ddd17-2',1,'dba','BASE','dba','t',,'Row','Transaction','Write',39321600
'17.0.4.2053','2','2016-08-18 04:23:44.383','ddd17-2',1,'dba','BASE','dba','t',,'Row','Transaction','Write',39321601
'17.0.4.2053','2','2016-08-18 04:23:44.383','ddd17-2',1,'dba','BASE','dba','t',,'Position','Transaction','Insert',
'17.0.4.2053','2','2016-08-18 04:23:44.383','ddd17-2',1,'dba','BASE','dba','t',,'Schema','Transaction','Shared',
'17.0.4.2053','2','2016-08-18 04:23:44.383','ddd17-2',1,'dba','BASE','dba','t',,'Table','Transaction','Intent',
'17.0.4.2053','2','2016-08-18 04:23:44.383','ddd17-2',1,'dba','BASE','dba','t',0,'Position','Transaction','Insert',
COMMIT;
SELECT * FROM t;
SELECT @@VERSION, 
       CONNECTION_PROPERTY ( 'UncommitOp' ) AS UncommitOp, 
       CONNECTION_PROPERTY ( 'TransactionStartTime' ) AS TransactionStartTime, 
       * 
  FROM sa_locks();
@@VERSION,UncommitOp,TransactionStartTime,conn_name,conn_id,user_id,table_type,creator,table_name,index_id,lock_class,lock_duration,lock_type,row_identifier
'17.0.4.2053','0','','ddd17-2',1,'dba','BASE','dba','t',,'Schema','Transaction','Shared',

[former_SQLA_member1694878]

I don't know the complete answer. However, based on the terminology I'd guess the connection property is referring to the number of uncommitted entries (a.k.a operations) in the transaction log. Something like a schema lock wouldn't write a "LogOp" to the transaction log file.

[Breck_Carter]

...where the definition of "uncommitted entries (a.k.a. operations)" is "rows", not "SQL statements"?

Which is what testing seems to indicate.

[VolkerBarth]

255 times 4 equals 1020, hence the word "roughly".

I would assume (but cannot test that currently) that the first UPDATE is a no-op for the row with pkey 1 as it remains unchanged - so only 1020 - 1 rows are modified, as counted:)

(It might be different if the table had a BEFORE UPDATE trigger.)

[Breck_Carter]

Well done, Volker! ...now I can make edit number 4,743,951 to the Foxhound 4 Help, to remove the word "roughly" 🙂 ...apparently the Help is becoming The Book.

[Breck_Carter]

Until I hear otherwise, here's what's going in the Foxhound 4 Help...

Performance Tip: The count of Uncommitted operations is equal to the number of uncommitted row-level inserts, updates and deletes.

The count of Uncommitted operations may be larger than the number of INSERT, UPDATE and DELETE statements because one statement can affect many rows.

If a single row is inserted, then updated twice and finally deleted, for example, that counts as four Uncommitted operations.

If a single row matches the WHERE clause of an UPDATE statement but all of the new column values in the SET clause are the same as the current values in the row, that row is not updated and the operation is not not counted as an Uncommitted operation.

A table-level schema lock obtained by a SELECT statement is not counted as an Uncommitted operation even though the lock is not cleared until a subsequent commit or rollback (and may thus be regarded as "uncommitted").

[Breck_Carter]

> It might be different if the table had a BEFORE UPDATE trigger.

The BEFORE UPDATE trigger doesn't affect the no-op nature of that row operation... if my book is to be believed, that is :)...

If the WHERE clause matches a row, but all the new column values are the same as the current values, the UPDATE proceeds as follows: Any BEFORE UPDATE triggers associated with this table are fired, a row lock is obtained but no update is performed, nothing is recorded in the transaction log, no AFTER UPDATE triggers are fired, and the SQLSTATE special literal is set to '00000'.

[VolkerBarth]

Yes, that's basically my information, too (and I certainly own your book twice), but it offers a side-effect:

I just wanted to hint at the fact that because a BEFORE UPDATE trigger (if existing) is fired for such rows, too, that trigger itself might alter the row's contents, so it becomes a modified row and therefore counts as a "real operation"... - So even if the SET clause does not alter the row's values, it can still be altered by a trigger and will then be counted.

I frequently use such kind of triggers.

The same is true for the special DEFAULT TIMESTAMP and LAST USER defaults.

Enough nitpicking for today:)

[Former Member · Accepted solution]

This counter is basically just the 'size' of your connection's current rollback log.

At any point this will be dominated by inserts, updates and deletes (or more specifically their counter-parts) since queries don't count and DDL will autocommit (a reset on the rollback log size). There may not always be an exact 1-for-1 correlation as there may be some bookkeeping extras needed in the rollback log that are not required in the transaction log.

As for your point #2, updates that make no actual changes are No-Ops and there is nothing to rollback for those so they don't contribute.

In addition, locking and the TransactionStartTime are part of the (meta-)transaction management layer and do not contribute any entries into the rollback log.

(late but hopefully some information of worth)

[Breck_Carter · Accepted solution]

1 Your mention of the transaction log confuses me, since the transaction log is so completely different from the rollback log; e.g., it doesn't get deleted, it contains DDL, it contains masses of its own "bookkeeping extras", etcetera. Perhaps "are not required in the transaction log" should just be omitted 🙂

[Breck_Carter · Accepted solution]

2 So UncommitOp is the pretty much the same as RollbackLogPages, except that it's measured in liters instead of gallons?

[Breck_Carter · Accepted solution]

3 Bookkeeping aside, is there nothing else counted in UncommitOp, other than inserts updates deletes?

Feel free to scream "NO! NOTHING ELSE!" ...it'll clear up my case of The Doubts 🙂

[Former Member · Accepted solution]

Your confusion aside, and to help in avoiding additional confusion on this end, what is your real concern here?

The contents of the rollback log form a transactionally scoped entity and can have additional entries related to things related to savepoints, nested transactions (T/SQL) and possibly some things related temporary objects, materialized views, etc.; none of which will exist (nor will have counter parts) in the transaction log but may need to be rolled back or act as the 'fences' for partial rollbacks. This is the book-keeping-on-the-side kinds of things mentioned.

Of course there are things in the transaction log that are not required for the rollback log and we may add or change those at any time; without such detailed documentation. The same is necessarily going to be true for rollback log entries.

[VolkerBarth · Accepted solution]

and can have additional entries related to things related to savepoints,...

And these entries get counted in the "UncommitOp" property, too? - I'm quite sure that question is Breck's real concern w.r.t. his comments 2 and 3 and is still unanswered IMVHO:)

[Breck_Carter · Accepted solution]

Volker is correct. My real concern has nothing to do with the transaction log... forget I mentioned it.

My real concern is the question "What does UncommitOp tell me about the work being performed by this connection?"

It does seem your definition of "bookkeeping" differs from the commonly-held opinion that "bookkeeping" is synonymous with "unimportant" (or evil 🙂

I very much appreciate your work... it greatly expands on the Help topic.