Use transactions
All actions in the FairCom JSON DB API are performed within a transaction. When an action is run, the server automatically creates a transaction, commits it when successful or rolls it back if it fails.
When multiple actions are performed within a transaction, they are all rolled back or committed together in an all or none fashion.
Applications determine when to roll back or commit multi-action transactions. A roll back might be necessary when an action fails or when an external event invalidates the transaction, such as when a business rule is violated or when the application encounters an exception. Sometimes an application might decide to commit a transaction even if one or more actions fail.
In FairCom SQL, all statements automatically run in a multi-statement transaction. When you run a COMMIT or ROLLBACK statement, the current multi-statement transaction ends and another one is automatically created.
In FairCom’s CTDB and ISAM APIs, you can choose when to run functions in a transaction and when not to.
Create transactions
In the JSON DB I, use the "createTransaction" action to create and run a multi-action transaction.
In the CTDB API, use ctdbBegin() to create a multi-action transaction, use ctdbCommit() to commit, and use ctdbAbort() to abort.
Automatic transaction isolation
Transaction isolation means controlling the visibility of the actions performed within a transaction. It prevents queries from seeing data changes made before a transaction is completed. When transactions are running, the data being changed within the transaction is isolated from queries on that data. This allows queries to run at any point in time with accurate results for analytics, and reporting.
Without transaction isolation, queries can return incorrect results. Take an example where a query attempts to read two records. The first record is a withdrawal of $10,000 from one account and the second record is a deposit of that $10,000 into another account. After the query reads the first record, another process removes both records because the bank denied the transfer. The query looks for the next record and since it no longer exists, the query ends. The query incorrectly reports a withdrawal of $10,000.
Transactions ensure each query sees a snapshot of the data. For example, a multi-action transaction inserts a record that withdraws $10,000 from one account and inserts a second record that deposits $10,000 into another account. During the transaction, after the withdrawal record is inserted, a query reads all withdrawal and deposit records. The query does not see the withdrawal record because it is not yet committed. It is isolated from queries until the transaction completes. After the transaction is completed, another query reads all withdrawal and deposit records. This second query now sees both the withdrawal and deposit records because they have both been committed as part of the transaction. Both queries return the correct results as of the point in time they ran.
JSON DB cursors with transactions
In the JSON DB API, a cursor is created from a "getRecords..." action which may or may not be part of a multi-action transaction. Thus, a cursor may or may not be part of a multi-action transaction.
When a cursor is part of a multi-action transaction, it is automatically closed when the transaction commits or rolls back. An application may also close a cursor before a transaction ends.
When a cursor is part of a multi-action transaction, it returns uncommitted records inserted and updated within the transaction. It does not return uncommitted records being processed by other transactions.
A cursor created outside of a multi-action transaction runs in its own automatically created transaction. It cannot be added to another multi-action transaction. It is unaffected by data changes that occur in other multi-action transactions. In other words, it cannot see the uncommitted records inserted and updated in other transactions. This behavior is consistent with SQL.
For a cursor to be part of a multi-action transaction, create a transaction using a "createTransaction" action, which returns a "transactionId". To read records inside a multi-action transaction, add the "transactionId" to a "getRecords..." action. This creates a cursor within the transaction allowing the cursor to return uncommitted records inserted and updated within the transaction.
Note
NOTE: the "getRecordsFromCursor" action does not create a cursor. Instead, it returns records from a previously created cursor. Thus, you cannot add a "transactionId" to the "getRecordsFromCursor" action.