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 '220.127.116.110','1020','2016-08-21 06:04:19.657'
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();
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)
answered 22 Aug '16, 16:42
Nick Elson S...