IDbTransaction.Commit returns before transaction is complete

user_693367 0
Viewed 580

I have a server which (rarely) dynamically creates database rows to accommodate the data model the user configures. During startup the server may have to create about a thousand rows as well as multiple inserts into existing tables.

When all this is done, it commits the transaction and sends out notifications about the new data model to anyone who might be listening. The issue is, that transaction.Commit() appears to return before the database has actually finished making the changes, so if a client makes a request to the server after it has sent out the notifications, the client may get an empty result. My assumption was that waiting for transaction.Commit() would ensure that the transaction was all done and committed.

The reason why the client gets an empty result is that when not doing DDL operations the database is using snapshot isolation, so clearly the snapshot is taken before the DDL operation has completed (but well after transaction.commit has returned)

The order of operations is:

  1. Start transaction
  2. Do thousands of operations in database (DDL)
  3. Commit transaction
  4. Send notifications about changes
  5. Client requests data (Snapshot isolation)
  6. No data (state before transaction) is returned to client.

Why does transaction.Commit() finish before the transaction has finished committing? How can I make the server wait for the transaction to be completely finished before proceeding to send out the notifications?

Edit: Clarity.

ddl ado.net sql-server c#
2 Answers

This is the natural behaviour of 'Snapshot Isolation'. Check this link: https://www.sqlshack.com/snapshot-isolation-in-sql-server/

"When another session reads the same data, the committed version of the data as of the time the reading transaction began is returned."

You have to kill the session in order to other transcation can read it. Or change it to 'Read Committed'.

user_145625350

As we figured out in comments, one possible reason for this is pending outer transaction. As stated in documentation:

When used in nested transactions, commits of the inner transactions don't free resources or make their modifications permanent. The data modifications are made permanent and resources freed only when the outer transaction is committed. Each COMMIT TRANSACTION issued when @@TRANCOUNT is greater than one simply decrements @@TRANCOUNT by 1. When @@TRANCOUNT is finally decremented to 0, the entire outer transaction is committed. Because transaction_name is ignored by the Database Engine, issuing a COMMIT TRANSACTION referencing the name of an outer transaction when there are outstanding inner transactions only decrements @@TRANCOUNT by 1.

So despite Commit() is executed successfully, if outer transaction is present - no changes will be persisted to database until that outer transaction commits too.

user_53117350
Related