Changes between Initial Version and Version 1 of rfc54_dataset_transactions

Timestamp:

Feb 23, 2015, 9:18:46 AM (9 years ago)

Author:

Even Rouault

Comment:

Initial version of RFC 54

rfc54_dataset_transactions

@@ +1 @@
+= RFC 54: Dataset transactions =
+
+Authors: Even Rouault [[BR]]
+Contact: even dot rouault at spatialys.com [[BR]]
+Status: Development
+
+== Summary ==
+
+This RFC introduces an API to offer a transaction mechanism at dataset level and
+uses it in the PostgreSQL, SQLite and GPKG drivers. It also reworks
+significantly how transactions are handled in the PostgreSQL driver.
+It also introduces a generic mechanism to implement an emulation of transactions
+for datasources that would not natively support it, and uses it in the FileGDB
+driver.
+
+== Rationale ==
+
+The current abstraction offers a transaction API at the layer level. However,
+this is generally misleading since, when it is implemented in DBMS with
+BEGIN/COMMIT/ROLLBACK sql statements (PostgreSQL, SQLite, GPKG, PGDump, MSSQLSpatial),
+the semantics is really a transaction at database level that spans over all
+layers/tables. So even if calling StartTransaction() on a layer, it also extends
+on the changes done on other layers. In a very few drivers
+StartTransaction()/CommitTransaction() is sometimes used as a mechanism to do
+bulk insertion. This is for example the case of WFS, CartoDB, GFT, GME. For some
+of them, it could rather be at dataset level too since potentially multiple layer
+modifications could be stacked together.
+
+Furthermode some use cases require updating several layers consistently, hence
+the need for a real database level transaction abstraction.
+
+The current situation of various drivers is the following (some of the below
+observations resulting from the analysis are kept mainly for the benefit of developers
+that would need to work in the drivers) :
+
+=== PostgreSQL ===
+
+A few facts about cursors used to run GetNextFeature() requests:
+
+  * Cursors are needed for retrieval of huge amount of data without being
+    memory bound.
+  * Cursors need transactions to run.
+  * Default cursors (WITHOUT HOLD) cannot be used outside of the transaction that
+    created tem
+  * You cannot modify the structure of a table while the transaction (yes, the
+    transaction, not the cursor) is still active and if you do that on another
+    connection, it hangs until the other connection commits or rollbacks)
+  * Within a transaction, deleted/modified rows are only visible if they are done
+    before declaring the cursor.
+  * Cursors WITH HOLD: may be used outside of transaction but cause a copy of
+    the table to be done --> bad for performance
+
+Current flaws are :
+  * one cannot do interleaved layer reading (beyond the
+    first 500 features fetched, can be easily seen with OGR_PG_CURSOR_PAGE=1) due to
+    the underlying implicit transaction created to read layer A being closed when the
+    reading of layer B starts.
+  * GetFeature() flushes the current transaction and starts a new one to do a
+    cursor SELECT. Which is unnecessary since we retrieve only one record
+  * SetAttributeFilter() issues a ResetReading() which currently
+    FlushSoftTransaction() the ongoing transaction. Can be annoying in a scenario
+    with long update where you need transactional guarantee
+
+What works :
+  * Transaction support at the layer level forwarded to datasource.
+  * Interleaved writing works (even with copy mode)
+
+=== SQLite/GPKG ===
+
+  * Mechanisms used to read table content (sqlite3_prepare() / sqlite3_step())
+    do not need transactions.
+  * Step sees structure modifications (e.g. column addition) if run after
+    prepared statement but before first step.
+  * Step sees row modifications/additions as soon as they occur.
+  * Transaction support at the layer level forwarded to datasource.
+
+=== MySQL ===
+
+  * Cannot do interleaved layer reading (reading in one layer resets the other
+    reading) because of the use of mysql_use_result() that can work with one single
+    request at a time. mysql_store_result() would be a solution but requires
+    ingesting the whole result set into memory, which is inpractical for big layers.
+  * step does not set row changes once the query has started (if done through another
+    connection, because if done through ExecuteSQL() the long transaction is interrupted)
+  * No transaction support
+
+=== OCI ===
+
+  * Interleaved layer reading works
+  * Changes done after SELECT seem not to be seen.
+  * No transaction support
+
+=== FileGDB ===
+
+  * Interleaved layer reading works
+  * Changes done after SELECT seem not to be seen.
+  * No transaction support
+
+
+== Proposed changes ==
+
+=== GDALDataset changes ===
+
+The following methods are added to GDALDataset (and usable by OGRDataSource
+which inherits from GDALDataset).
+
+{{{
+/************************************************************************/
+/*                           StartTransaction()                         */
+/************************************************************************/
+
+/**
+ \brief For datasources which support transactions, StartTransaction creates a transaction.
+
+ If starting the transaction fails, will return 
+ OGRERR_FAILURE. Datasources which do not support transactions will 
+ always return OGRERR_UNSUPPORTED_OPERATION.
+
+ Nested transactions are not supported.
+ 
+ All changes done after the start of the transaction are definitely applied in the
+ datasource if CommitTransaction() is called. They may be cancelled by calling
+ RollbackTransaction() instead.
+ 
+ At the time of writing, transactions only apply on vector layers.
+ 
+ Datasets that support transactions will advertize the ODsCTransactions capability.
+ Use of transactions at dataset level is generally prefered to transactions at
+ layer level, whose scope is rarely limited to the layer from which it was started.
+ 
+ In case StartTransaction() fails, neither CommitTransaction() or RollbackTransaction()
+ should be called.
+ 
+ If an error occurs after a successfull StartTransaction(), the whole
+ transaction may or may not be implicitely cancelled, depending on drivers. (e.g.
+ the PG driver will cancel it, SQLite/GPKG not). In any case, in the event of an
+ error, an explicit call to RollbackTransaction() should be done to keep things balanced.
+ 
+ By default, when bForce is set to FALSE, only "efficient" transactions will be
+ attempted. Some drivers may offer an emulation of transactions, but sometimes
+ with significant overhead, in which case the user must explicitely allow for such
+ an emulation by setting bForce to TRUE. Drivers that offer emulated transactions
+ should advertize the ODsCEmulatedTransactions capability (and not ODsCTransactions).
+ 
+ This function is the same as the C function GDALDatasetStartTransaction().
+
+ @param bForce can be set to TRUE if an emulation, possibly slow, of a transaction
+               mechanism is acceptable.
+
+ @return OGRERR_NONE on success.
+ @since GDAL 2.0
+*/
+OGRErr GDALDataset::StartTransaction(CPL_UNUSED int bForce);
+
+
+/************************************************************************/
+/*                           CommitTransaction()                        */
+/************************************************************************/
+
+/**
+ \brief For datasources which support transactions, CommitTransaction commits a transaction.
+
+ If no transaction is active, or the commit fails, will return 
+ OGRERR_FAILURE. Datasources which do not support transactions will 
+ always return OGRERR_UNSUPPORTED_OPERATION. 
+ 
+ Depending on drivers, this may or may not abort layer sequential readings that
+ are active.
+
+ This function is the same as the C function GDALDatasetCommitTransaction().
+
+ @return OGRERR_NONE on success.
+ @since GDAL 2.0
+*/
+OGRErr GDALDataset::CommitTransaction();
+
+/************************************************************************/
+/*                           RollbackTransaction()                      */
+/************************************************************************/
+
+/**
+ \brief For datasources which support transactions, RollbackTransaction will roll
+ back a datasource to its state before the start of the current transaction. 
+
+ If no transaction is active, or the rollback fails, will return  
+ OGRERR_FAILURE. Datasources which do not support transactions will
+ always return OGRERR_UNSUPPORTED_OPERATION. 
+
+ This function is the same as the C function GDALDatasetRollbackTransaction().
+
+ @return OGRERR_NONE on success.
+ @since GDAL 2.0
+*/
+OGRErr GDALDataset::RollbackTransaction();
+}}}
+
+Note: in the GDALDataset class itself, those methods have an empty implementation
+that returns OGRERR_UNSUPPORTED_OPERATION.
+
+Those 3 methods are mapped at the C level as :
+{{{
+OGRErr CPL_DLL GDALDatasetStartTransaction(GDALDatasetH hDS, int bForce);
+OGRErr CPL_DLL GDALDatasetCommitTransaction(GDALDatasetH hDS);
+OGRErr CPL_DLL GDALDatasetRollbackTransaction(GDALDatasetH hDS);
+}}}
+
+Two news dataset capabilities are added :
+  * ODsCTransactions: True if this datasource supports (efficient) transactions.
+  * ODsCEmulatedTransactions: True if this datasource supports transactions through emulation.
+
+=== Emulated transactions ===
+
+A new function OGRCreateEmulatedTransactionDataSourceWrapper() is added for used
+by drivers that do not natively support transactions but want an emulation of
+them. It could potentially be adopted by any datasource whose data is supportted
+by files/directories.
+
+{{{
+/** Returns a new datasource object that adds transactional behaviour to an existing datasource.
+ * 
+ * The provided poTransactionBehaviour object should implement driver-specific
+ * behaviour for transactions.
+ *
+ * The generic mechanisms offered by the wrapper class do not cover concurrent
+ * updates (though different datasource connections) to the same datasource files.
+ *
+ * There are restrictions on what can be accomplished. For example it is not
+ * allowed to have a unreleased layer returned by ExecuteSQL() before calling
+ * StartTransaction(), CommitTransaction() or RollbackTransaction().
+ *
+ * Layer structural changes are not allowed after StartTransaction() if the
+ * layer definition object has been returned previously with GetLayerDefn().
+ *
+ * @param poBaseDataSource the datasource to which to add transactional behaviour.
+ * @param poTransactionBehaviour an implementation of the IOGRTransactionBehaviour interface.
+ * @param bTakeOwnershipDataSource whether the returned object should own the
+ *                                 passed poBaseDataSource (and thus destroy it
+ *                                 when it is destroyed itself).
+ * @param bTakeOwnershipTransactionBehaviour whether the returned object should own
+ *                                           the passed poTransactionBehaviour
+ *                                           (and thus destroy it when
+ *                                           it is destroyed itself).
+ * @return a new datasource handle
+ * @since GDAL 2.0
+ */
+OGRDataSource CPL_DLL* OGRCreateEmulatedTransactionDataSourceWrapper(
+                                OGRDataSource* poBaseDataSource,
+                                IOGRTransactionBehaviour* poTransactionBehaviour,
+                                int bTakeOwnershipDataSource,
+                                int bTakeOwnershipTransactionBehaviour);
+}}}
+
+The definition of the IOGRTransactionBehaviour interface is the following:
+{{{
+/** IOGRTransactionBehaviour is an interface that a driver must implement
+ *  to provide emulation of transactions.
+ *
+ * @since GDAL 2.0
+ */
+class CPL_DLL IOGRTransactionBehaviour
+{
+    public:
+
+        /** Start a transaction.
+        *
+        * The implementation may update the poDSInOut reference by closing
+        * and reopening the datasource (or assigning it to NULL in case of error).
+        * In which case bOutHasReopenedDS must be set to TRUE.
+        *
+        * The implementation can for example backup the existing files/directories
+        * that compose the current datasource.
+        *
+        * @param poDSInOut datasource handle that may be modified
+        * @param bOutHasReopenedDS output boolean to indicate if datasource has been closed
+        * @return OGRERR_NONE in case of success
+        */
+       virtual OGRErr StartTransaction(OGRDataSource*& poDSInOut,
+                                       int& bOutHasReopenedDS) = 0;
+
+        /** Commit a transaction.
+        *
+        * The implementation may update the poDSInOut reference by closing
+        * and reopening the datasource (or assigning it to NULL in case of error).
+        * In which case bOutHasReopenedDS must be set to TRUE.
+        *
+        * The implementation can for example remove the backup it may have done
+        * at StartTransaction() time.
+        *
+        * @param poDSInOut datasource handle that may be modified
+        * @param bOutHasReopenedDS output boolean to indicate if datasource has been closed
+        * @return OGRERR_NONE in case of success
+        */
+       virtual OGRErr CommitTransaction(OGRDataSource*& poDSInOut,
+                                        int& bOutHasReopenedDS) = 0;
+
+        /** Rollback a transaction.
+        *
+        * The implementation may update the poDSInOut reference by closing
+        * and reopening the datasource (or assigning it to NULL in case of error).
+        * In which case bOutHasReopenedDS must be set to TRUE.
+        *
+        * The implementation can for example restore the backup it may have done
+        * at StartTransaction() time.
+        *
+        * @param poDSInOut datasource handle that may be modified
+        * @param bOutHasReopenedDS output boolean to indicate if datasource has been closed
+        * @return OGRERR_NONE in case of success
+        */
+       virtual OGRErr RollbackTransaction(OGRDataSource*& poDSInOut,
+                                          int& bOutHasReopenedDS) = 0;
+
+        /** Ask a layer to re-"adopt" its previous feature definition object.
+        *
+        * The aim is that the user doesn't see visible object changes.
+        *
+        * This method is called if StartTransaction(), CommitTransaction() or
+        * RollbackTransaction() has closed and reopened a datasource.
+        *
+        * The implementation os the method should drop the reference to feature
+        * definition object it may have currently, assign the poFeatureDefn as
+        * its current definition object, and take a new reference on it.
+        *
+        * The passed poFeatureDefn object is supposed to be the "same" as
+        * the one the layer has currently (as verified with IsSame()) since
+        * the emulated datasource wrapper prevents layer structural modifications
+        * in cases where the user has already called GetLayerDefn()
+        */
+        virtual void   ReadoptOldFeatureDefn(OGRDataSource* poDS,
+                                            OGRLayer* poLayer,
+                                            OGRFeatureDefn* poFeatureDefn) = 0;    
+};
+}}}
+
+=== OPGRLayer changes ===
+
+At the OGRLayer level, the documentation of GetNextFeature() receives the
+following additional information to clarify its semantics :
+
+{{{
+Features returned by GetNextFeature() may or may not be affected by concurrent
+modifications depending on drivers. A guaranteed way of seing modifications in
+effect is to call ResetReading() on layers where GetNextFeature() has been called,
+before reading again. Structural changes in layers (field addition, deletion, ...)
+when a read is in progress may or may not be possible depending on drivers.
+If a transaction is committed/aborted, the current sequential reading may or may
+not be valid after that operation and a call to ResetReading() might be needed.
+}}}
+
+=== PG driver changes ===
+
+Dataset level transactions have been implemented, and use of implicitely
+created transactions reworked.
+
+Interleaved layer reading is now possible.
+
+GetFeature() has been modified to run without a cursor or a transaction, and
+all other calls to transactions have been checked/modified to not reset
+accidentally a transaction initiated by the user.
+
+Below the new behaviour as described in the updated drv_pg_advanced.html help
+page :
+
+{{{
+Efficient sequential reading in PostgreSQL requires to be done within a transaction
+(technically this is a CURSOR WITHOUT HOLD).
+So the PG driver will implicitely open such a transaction if none is currently
+opened as soon as a feature is retrieved. This transaction will be released if
+ResetReading() is called (provided that no other layer is still being read).
+
+If within such an implicit transaction, an explicit dataset level StartTransaction()
+is issued, the PG driver will use a SAVEPOINT to emulate properly the transaction
+behaviour while making the active cursor on the read layer still opened.
+
+If an explicit transaction is opened with dataset level StartTransaction()
+before reading a layer, this transaction will be used for the cursor that iterates
+over the layer. When explicitly committing or rolling back the transaction, the
+cursor will become invalid, and ResetReading() should be issued again to restart
+reading from the beginning.
+
+With the above rules, the below examples show the SQL instructions that are
+run when using the OGR API in different scenarios.
+
+
+lyr1->GetNextFeature()             BEGIN (implict)
+                                   DECLARE cur1 CURSOR FOR SELECT * FROM lyr1
+                                   FETCH 1 IN cur1
+
+lyr2->GetNextFeature()             DECLARE cur2 CURSOR  FOR SELECT * FROM lyr2
+                                   FETCH 1 IN cur2
+
+lyr1->GetNextFeature()             FETCH 1 IN cur1
+
+lyr2->GetNextFeature()             FETCH 1 IN cur2
+
+lyr1->CreateFeature(f)             INSERT INTO cur1 ...
+
+lyr1->ResetReading()               CLOSE cur1
+
+lyr2->ResetReading()               CLOSE cur2
+                                   COMMIT (implicit)
+
+
+
+ds->StartTransaction()             BEGIN
+
+lyr1->GetNextFeature()             DECLARE cur1 CURSOR FOR SELECT * FROM lyr1
+                                   FETCH 1 IN cur1
+
+lyr2->GetNextFeature()             DECLARE cur2 CURSOR FOR SELECT * FROM lyr2
+                                   FETCH 1 IN cur2
+
+lyr1->CreateFeature(f)             INSERT INTO cur1 ...
+
+lyr1->ResetReading()               CLOSE cur1
+
+lyr2->ResetReading()               CLOSE cur2
+
+ds->CommitTransaction()            COMMIT
+
+
+
+ds->StartTransaction()             BEGIN
+
+lyr1->GetNextFeature()             DECLARE cur1 CURSOR FOR SELECT * FROM lyr1
+                                   FETCH 1 IN cur1
+
+lyr1->CreateFeature(f)             INSERT INTO cur1 ...
+
+ds->CommitTransaction()            CLOSE cur1 (implicit)
+                                   COMMIT
+
+lyr1->GetNextFeature()             FETCH 1 IN cur1      ==> Error since the cursor was closed with the commit. Explicit ResetReading() required before
+
+
+
+lyr1->GetNextFeature()             BEGIN (implicit)
+                                   DECLARE cur1 CURSOR FOR SELECT * FROM lyr1
+                                   FETCH 1 IN cur1
+
+ds->StartTransaction()             SAVEPOINT savepoint
+
+lyr1->CreateFeature(f)             INSERT INTO cur1 ...
+
+ds->CommitTransaction()            RELEASE SAVEPOINT savepoint
+
+lyr1->ResetReading()               CLOSE cur1
+                                   COMMIT (implicit)
+
+
+Note: in reality, the PG drivers fetches 500 features at once. The FETCH 1
+is for clarity of the explanation.
+}}}
+
+It is recommended to do operations within explicit transactions for ease of mind
+(some troubles fixing ogr_pg.py, but which does admitedly weird things like
+reopening connections, which does not fly very well with 'implicit' transactions)
+
+=== GPKG and SQLite driver changes ===
+
+Dataset level transactions have been implemented. A few fixes made here and there
+to avoid reseting accidentally a transaction initiated by the user.
+
+=== FileGDB driver changes ===
+
+The FileGDB driver uses the above described emulation to offer a transaction
+mechanism. This works by backing up the
+current state of a geodatabase when StartTransaction(force=TRUE) is called.
+If the transaction is committed, the backup copy is destroyed. If the transaction
+is rolled back, the backup copy is restored. So this might be costly when operating
+on huge geodatabases.
+Note that this emulation has an unspecified behaviour in case of concurrent updates
+(with different connections in the same or another process).
+
+== SWIG bindings (Python / Java / C# / Perl) changes ==
+
+The following additions have been done :
+  * Dataset.StartTransaction(int force=FALSE)
+  * Dataset.CommitTransaction()
+  * Dataset.RollbackTransaction()
+  * ogr.ODsCTransactions constant
+  * ogr.ODsCEmulatedTransactions constant
+
+== Utilities ==
+
+ogr2ogr now uses dataset transactions (instead of layer transactions) if
+ODsCTransactions is advertized.
+
+== Documentation ==
+
+New/modified API are documented. MIGRATION_GUIDE.TXT updated with mention
+to below compatibility issues.
+
+== Test Suite ==
+
+The test suite is extended to test
+ * updated drivers: PG, GPKG, SQLite, FileGDB
+ * use of database transactions by ogr2ogr
+
+== Compatibility Issues ==
+
+As described above, subtle behaviour changes can be observed with the PG driver,
+related to implicit transactions that were flushed before and are no longer now,
+but this should hopefully be restricted to non-typical use cases. So some cases that "worked" before might
+no longer work, but the new behaviour should hopefully be more understandable.
+
+The PG and SQLite drivers could accept apparently nested calls to StartTransaction()
+(at the layer level). This is no longer possible since they are now redirected
+to dataset transactions, that explicitely do not support it.
+
+== Out of scope ==
+
+The following drivers that implement BEGIN/COMMIT/ROLLBACK could be later
+enhanced to support dataset transactions: OCI, MySQL, MSSQLSpatial.
+
+GFT, CartoDB, WFS could also benefit for dataset transactions.
+
+VRT currently supports layer transactions (if the underlying dataset support it,
+and excluding union layers). If dataset transaction were to be implemented, should
+it consist in forwarding dataset transaction to source dataset(s) ? Implementation
+might be complicated in case the same dataset is used by multiple sources,
+but more fundamentally one cannot guarantee ACID on multiple datasets.
+
+== Related tickets ==
+
+A proposed revision on how transactions are implemented in the PG driver was
+proposed a long time ago (https://trac.osgeo.org/gdal/ticket/1265) to solve
+some of the above issues. The patch no longer applies but it is expected that
+the changes done for this RFC cover the issues that the ticket wanted to address.
+
+== Implementation ==
+
+Implementation will be done by Even Rouault ([http://spatialys.com Spatialys]),
+and sponsored by [http://www.linz.govt.nz/ LINZ (Land Information New Zealand)].
+
+The proposed implementation lies in the "rfc54_dataset_transactions" branch of the
+https://github.com/rouault/gdal2/tree/rfc54_dataset_transactions repository.
+
+The list of changes: https://github.com/rouault/gdal2/compare/rfc54_dataset_transactions
+
+== Voting history ==
+
+TBD