Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
-
None
Description
It would be very useful to be able to make a connection share the transaction snapshot of another connection that is already open and has started a consistent read transaction.
This would not allow them to share the set of modifications (ie. inserts/updates/deletes not yet committed) - just the same query snapshot. It could even be read-only.
If the transaction isolation levels are repeatable read, and no changes have been made, it means you can get perfectly consistent queries across the connections.
The motivation is that then we can do parallel SQL backup dumps, parallel database resyncs, and parallel reporting queries. I would use all three personally.
PostgreSQL 9.3 has introduced this feature using the function pg_export_snapshot to get the revision number and SET TRANSACTION SNAPSHOT <snapshot ID> to use it in another session (personally I think it would be more natural to pass it in the BEGIN/START TRANSACTION statement). They use it for parallel pg_dump which is good, but I would also use it in my apps as it is very hard to make full use of a modern database server with only one client thread and some big client jobs still take hours in a single thread.
From what I understand of how InnoDB works, I think this is doable? The engine status shows lines like this:
Trx read view will not see trx with id >= 1CE54868, sees < 1CE54864
So we would need to be able to copy those values across to another connection/transaction.
Thoughts?
Attachments
Issue Links
- relates to
-
-
- Open
-
-
MDEV-14090 XA START must be WITH CONSISTENT SNAPSHOT
-
- Open
-
-
-
- Open
-
-
-
- Open
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Workflow | defaullt [ 28903 ] | MariaDB v2 [ 42712 ] |
I discussed this with Jeremy Cole some time ago.
He reckons it's entirely possible for two connections to share a transaction (not even necessarily read-only) and internally InnoDB would be able to cope with it, and has appropriate infra to make it happen without too much hacking.
It would be very beneficial for a number of applications, including (for instance) parallel mysqldump.
Some relevant work has been done in MySQL 5.7: http://dev.mysql.com/worklog/task/?id=7193
From that WL it seems they intend to support full XA, which is far more powerful than what I was suggesting here (which is only sharing the read snapshot). But either would be great for me!
And yeah, indeed the first application in postgresql was for parallel dump (in pg_dump).
| Workflow | MariaDB v2 [ 42712 ] | MariaDB v3 [ 64313 ] |
| Link | This issue relates to MDEV-16610 [ MDEV-16610 ] |
arjen, I do not think that it would be feasible to make multiple threads share the same transaction object inside InnoDB. It would require more frequent use of trx_t::mutex and could cause a performance regression for the basic case when each thread is executing only a single transaction.
In InnoDB, cloning any active read view should be straightforward. MDEV-14090 has been filed for a somewhat related bug that currently XA START does not even create a read view; for InnoDB, it will happen lazily when the first index record is about to be accessed.
Fixing MDEV-16610 would require an even broader change: creating a read view that corresponds to any time in the recent past, or cloning the read view of another server. A special mechanism would be needed to prevent the purge of transaction history from ‘devouring’ recent read views prematurely. Normally, the purge_sys.view may advance as soon as the oldest active read view in the local server is disposed of. When using distributed read views across multiple servers, we should take other servers into account. Not every transaction would access every server.
| Link | This issue relates to MDEV-14090 [ MDEV-14090 ] |
| Workflow | MariaDB v3 [ 64313 ] | MariaDB v4 [ 130151 ] |
| Link | This issue relates to MDEV-33446 [ MDEV-33446 ] |
This is part of the XA standard, but we don't support it yet. The syntax already exists, XA START, but the implementation in the storage engines is missing. Neither InnoDB nor XtraDB support it, as far as I know.