MySQL – Behavior of Transactions on Multiple Connections

mariadbMySQLtransaction

I have a MariaDB database with InnoDB tables. I have two connections to the same database. One connection is using a transaction around a large chunk of work. The other connection is tasked with updating a table in the same database (but outside the scope of the transaction's work) to track the progress of the transaction.

All of this is happening in the same script, but again, with separate connections: one for the actual job being performed (that includes a transaction), and another for keeping meta info on the progress of the job.

I expected that this would work such that the job tracking record would update as the job progressed – 10%, 20%, 50%, etc because the transaction work is being done on a separate connection and impacting other tables. However, in my testing, the job tracking record doesn't get updated until the transaction for the job is committed.

As far as I can tell, it doesn't deadlock because the "progress monitoring" connection isn't using a transaction at all. The "worker" connection wraps its queries in a transaction, but the other connection doesn't.

I check progress by just looking at a 'percent complete' field I fill in as the work progresses. Order of operations is:

Connection 1: start transaction
Connection 1: add and update rows
Connection 2: update progress
Connection 1: add and update more rows.

Then, in another HTTP connection I'm polling and outputting the progress value, but it never updates until the transaction on connection 1 is committed.

Is there any way to get the behavior I want, outside of moving the job-tracking table into an entirely different database?

Best Answer

What you see is the correct behaviour for the default isolation setting (REPEATABLE READ) and even for lower (READ COMMITTED) setting. More details can be found in MariaDB and MySQL docs:

which basically sums up to:

What one transaction is writing, other transactions should not be able to read. Not before the one transaction commits.

When a transaction is writing something to the database, there is no guarantee that the write will persist. The transaction may get an error and all of the writes it has done be rolled back. So, what you observe

However, in my testing, the job tracking record doesn't get updated until the transaction for the job is committed.

is correct from that point of view. There is nothing to be seen from the outside, progress is 0, until something is committed. That's what the Isolation property of transactions is all about.

I see 2 solutions to the issue:

A. use the "worker", transaction 1 for progress report, as well. Who's better to know how much its work has progressed, than the worker itself?
B. In the 2nd, "progress report" connection, use the lowest isolation setting (READ UNCOMMITTED):
```
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
```
Now, the transactions from this connection will be able to do "dirty reads", i.e. see uncommitted writes from the 1st transaction and will report correctly the (uncommitted) progress.

Related Solutions

Sql-server – connection pooling, transactions, nested transaction and rollback

There is no such thing as nested transactions in any released version of SQL Server. You can say BEGIN TRANSACTION as many times as you want, but a ROLLBACK affects all of them (never mind what @@TRANCOUNT says - it only reflects nesting level).

From the documentation:

It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

A demonstration:

CREATE TABLE dbo.foo(a INT);

BEGIN TRANSACTION;
  INSERT dbo.foo(a) SELECT 1;
  BEGIN TRANSACTION;
    INSERT dbo.foo(a) SELECT 2;
    BEGIN TRANSACTION;
    ROLLBACK TRANSACTION;

SELECT tc = @@TRANCOUNT;

SELECT a FROM dbo.foo;

DROP TABLE dbo.foo;

Results:

tc    
----
0

1 row(s) affected.

a
----

0 row(s) affected.

So, there is no way to only roll back part of a "nested" transaction - it's all or nothing. If you want previous parts of this transaction to commit even if later parts fail, then stop nesting, and commit the first transaction before starting the next one.

SQL Server – Does RCSI Use Less or Same Space as SNAPSHOT?

Read committed snapshot isolation (statement-level snapshot isolation) potentially uses less storage than snapshot isolation (transaction-level snapshot isolation) because usually the row versions aren't required for as long a period of time after they've been generated.

For transaction-level snapshot isolation, the versions have to stick around for the entire duration of a transaction, which could be quite a long time (or quite a long time, as seen here). Of course, statements can run for a long time, too, but it's usually the case that a transaction will take longer.

To emphasise the point: A transaction running under snapshot isolation is guaranteed to see committed data as it existed at the start of the transaction. This means row versions have to be maintained until the whole transaction completes. Where the same transaction is running under RCSI, row versions can potentially be released after each statement. Overall, snapshot isolation tends to result in a larger version store size than RCSI on the same workload.

The same versions are generated with either or both settings enabled. I believe Kimberly was referring to storage impact in the sense of write rate, not storage used, so the two statements you mention aren't contradictory; just a little unclear or unspecific.

Best Answer

Related Solutions

Sql-server – connection pooling, transactions, nested transaction and rollback

SQL Server – Does RCSI Use Less or Same Space as SNAPSHOT?

Related Question