Database Transaction Tests
Achieving Atomic, Consistent, Isolated & Durable Results
According to Wikipedia:
'In computer science, ACID (atomicity, consistency, isolation, durability) is a set of properties of database transactions intended to guarantee data validity despite errors, power failures, and other mishaps. In the context of databases, a sequence of database operations that satisfies the ACID properties (which can be perceived as a single logical operation on the data) is called a transaction. For example, a transfer of funds from one bank account to another, even involving multiple changes such as debiting one account and crediting another, is a single transaction (a.k.a atomicity).'
We selected third-party software capable of importing all the rows in a Worksheet as a single transaction and rolling back the transaction if an error occurs.
We simulated the failure of the Database as we attempted to import the AE Example CRF as follows:
We deleted the AE Example CRF Worksheet from the Database using the "Delete CRF" menu option.
We opened SQL Server Management Studio (SSMS) and displayed the Example CRF database table, and confirmed that the AE Example CRF was not in the database table.
We manually added code to the database repository responsible for importing the AE Example CRF into the database. The added code tests to see if it is trying to insert the 11th row. and if so, the code deletes a line of code that causes the transaction to fail. It should then roll back the ten rows previously inserted into the Database.
To confirm this, we switched to SSMS and browsed the list of Example CRFs again. As expected, the AE Example CRF was not there.
Consistency is a very general term, which demands that the data must meet all database validation rules. The App confirms that each column in each row in the Worksheet passes validation checks before attempting to insert the Worksheet into the database. Therefore, the database does not contain any database validation rules because none are needed. ???
According to Wikipedia, to achieve isolation, we assume two transactions execute at the same time, each attempting to modify the same data. One of the two must wait until the other completes in order to maintain isolation.
As a reminder, the application does not provide for an update of the CDASHIG database, it only imports data requiring a creation, or deletion, of all rows in the Example CRF or Domain. The transaction, or unit of work, includes all the rows in the example CRF or Domain. Once the delete or insert unit of work starts a user cannot change it.
In database systems, durability is one of the ACID properties (Atomicity, Consistent, Isolated, and Durable) that guarantees that transactions that have been committed will survive permanently. For example, if you import an Example CRF and the system reports that the CRF was successfully imported, the CRF will still appear in the database even if the system crashes.
Many Database Management Systems (DBMSs) implement durability by writing transactions into a transaction log that can be reprocessed to recreate the system state right before any later failure. A transaction is deemed committed only after it is entered in the log.
Most modern DBMSs provide the capability to provide durable results (including the two that our app currently supports)
We recommend that you contact your IT Department and ask them to configure your DBMS server so the results will be durable.