Transactions in MBS Xojo SQL Plugin
Please set transaction mode and isolation level after connecting to the database before running commands.
Transactions can be completed by either being committed or being rolled back. When a transaction is committed, the changes made in that transaction are made permanent. When a transaction is rolled back, the affected rows are returned to the state they were in before the transaction was started.
Transactions can be in one of two modes: auto-commit mode or manual-commit mode.
In auto-commit mode, every database operation is a transaction that is committed when performed. In databases without transaction support, auto-commit mode is the only supported mode. In such databases, statements are committed when they are executed and there is no way to roll them back; they are therefore always in auto-commit mode.
In manual-commit mode, applications must explicitly complete transactions by calling Commit to commit them or Rollback to roll them back. This is the normal transaction mode for most relational databases.
Note: The default transaction settings are DBMS-defined. e.g. SQLite is in auto-commit mode by default.
You can set auto-commit mode compulsory by setting AutoCommit property with parameter value kAutoCommitOn. To set manual-commit mode you should set AutoCommit property with parameter value kAutoCommitOff. If you didn't set transaction mode explicitly then the library uses the default DBMS transaction settings. See DBMS documentation to get information concerning the default transaction mode.
If you disconnected from server without commit, then the result of transaction depends on underlying DBMS. It can either being committed or being rolled back. See DBMS documentation.
Calling Commit or Rollback in auto-commit is safe (it has no effect). If you are working in manual-commit mode and call Commit method then current transaction is committed and new one is started. If you are working in manual-commit mode and call Rollback method then current transaction is rolled back and new one is started.
Transaction Isolation Level
Transaction isolation refers to the degree of interaction between multiple concurrent transactions. SQL-92 defines four isolation levels, all of which are supported by our plugin:
- Read uncommitted (the lowest level where transactions are isolated just enough to ensure that physically corrupt data is not read)
- Read committed
- Repeatable read
- Serializable (the highest level, where transactions are completely isolated from one another)
Note: If you change isolation level it causes implicit commit for this connection.
If you didn't set transaction isolation level explicitly then the library uses the default DBMS transaction settings. See DBMS documentation to get information concerning the default transaction isolation level.
Here is an example in Xojo for use with SQLDatabaseMBS class:
db.AutoCommit = db.kAutoCommitOn
db.RaiseExceptions = True
Dim r1 As RecordSet = db.SQLSelect("SELECT COUNT(*) FROM test_tbl")
Dim c1 As Integer = r1.IdxField(1).IntegerValue
System.DebugLog "Count before: "+Str(c1)
// Insert value
db.SQLExecute "Insert into test_tbl(fid, fvarchar20) values (1, 'Some string (1)')"
// try rollback
Dim r2 As RecordSet = db.SQLSelect("SELECT COUNT(*) FROM test_tbl")
Dim c2 As Integer = r2.IdxField(1).IntegerValue
System.DebugLog "Count after: "+Str(c2)
If you set db.AutoCommit to kAutoCommitOn, the insert is committed right away and the rollback does nothing. So count increases in this example. Now when you use kAutoCommitOff, the rollback is done and count stays the same.
Please note that you may not call BeginTransaction method in Xojo. This would execute the "BEGIN TRANSACTION" internally, which conflicts the auto commit as the plugin may start a transaction and that may cause an error for starting a transaction within a transaction.