70.821
Transactions in SQLite

---

Objectives

Upon completion of this lesson, you will be able to:

• create transactions in SQLite
• explain when to use transactions

---

Introduction

In this lesson, you will learn about how to specify transactions and define data integrity under concurrent access in SQLite.

A transaction is a sequence of database modifications and queries that occur as a single sequence. The database meets all integrity constraints before and after the transaction.

A transaction must be explicitly started and, if all operations were successful, committed. If an operation within a transaction is not successful, then the transaction must be aborted or rolled back. A rollback restores the database to the state prior to the start of the transaction and all updates are reversed.

For more information on transactions and concurrency, see Lesson 60.701 Transaction Processing in Relational Databases.

To follow along, go to Files & Resources and download the R Notebook and copy (or better yet, type) the R and SQL code chunks into an R Notebook of your own – and experiment along the way.

SQLite and ACID

SQLite is a transactional database; it ensures that all modifications (i.e., inserts, updates, and deletes) and queries exhibit the ACID properties, i.e., they are atomic, consistent, isolated, and durable.

Adhering to the ACID properties implies that in SQLite all modifications leave the database in a consistent state even if a transaction is interrupted by a program crash, operation system dump, or power failure to the computer.

ACID Properties

Atomic: A transaction must be atomic, i.e., a sequence of modification operations cannot be broken down into smaller ones. When a a transaction is committed, either all modifications are applied or none are applied, i.e., no partial updates.

Consistent: a transaction must ensure to change the database from one valid state to another. When a transaction starts and executes a statement to modify data, the database becomes inconsistent. However, when the transaction is committed or rolled back, it is important that the transaction must keep the database consistent.

Isolated: a pending transaction performed by a session must be isolated from other sessions. When a session starts a transaction and executes the INSERT or UPDATE statement to change the data, these changes are only visible to the current session, not others. On the other hand, the changes committed by other sessions after the transaction started should not be visible to the current session.

Durable: if a transaction is successfully committed, the changes must be permanent in the database regardless of the condition such as power failure or program crash. On the contrary, if the program crashes before the transaction is committed, the change should not persist.

Transactions in SQLite

Begin Transaction

By default, SQLite operates in auto-commit mode. This means that for each command that modifies the database (i.e., INSERT, UPDATE, DELETE), SQLite starts a transaction, processes the command, and commits the transaction automatically.

To start a transaction explicitly, issue the BEGIN TRANSACTION command. As SQLite does not support nested transactions, a new transaction cannot be started until the current transaction is committed or rolled back.

begin transaction;

After starting a transaction, the transaction is active until it is explicitly committed or rolled back. Any changes made to the database are only visible to the application that initiated the transaction and are not visible to anyone else. Any other client connected to the database will see that data in the state prior to the start of the transaction (the atomic property). Any SELECT queries by other clients will no see any updates done within the transaction until the transaction is committed.

Commit Transaction

To commit a transaction, i.e., to make permanent any changes done after the start of the transaction, requires an explicit COMMIT or COMMIT TRANSACTION statement.

commit transaction;

Rollback Transaction

If an error occurred during the transaction or, for some reason, the changes should not be made permanent, then all changes can undone using a ROLLBACK or ROLLBACK TRANSACTION statement. If a rollback is perform rather than a commit, then the state of the database is returned to the state just prior to the start of the transaction.

rollback transaction;

Nested Transactions

Nested transactions are a concept in database management systems (DBMS) where a transaction contains one or more sub-transactions. These sub-transactions are referred to as “nested transactions”. The main transaction, also known as the outer or parent transaction, is responsible for managing the nested transactions. This hierarchy allows for better control and isolation of operations, as each nested transaction can be individually committed or rolled back without affecting the other transactions.

In a nested transaction model, if a nested transaction commits successfully, its changes are not immediately made permanent in the database. Instead, the changes are only visible to its parent transaction and are considered temporary. The parent transaction must also commit successfully for the changes made by its nested transactions to become permanent. If any nested transaction or the parent transaction fails, the whole transaction hierarchy can be rolled back, undoing all changes.

Nested transactions are useful in situations where a complex operation requires multiple, related sub-operations that depend on each other, and you want to ensure data consistency and integrity. If one of the sub-operations fails, you may need to undo the changes made by the other sub-operations, which can be achieved by rolling back the nested transaction.

There are read and there are write transactions. In a read transaction you read a consistent state of the database if when others are manipulating the data you are reading – as your application will see the state of the database at the start of the transaction. This can be critical for analytics or calculations where the underlying data must be consistent. So, you could use a nested read transactions within an outer transaction that might make modifications to the database.

You do that because you don’t have to look everything – remember that transactions requires shadow tables or non-concurrency which reduces database server performance.

It can help with making transactions more granular which can increase concurrency and performance of the database server. Always remember that your application is often not the only one accessing the database… think of an airline reservation system where thousands or hundreds of thousands of flight bookings occur every minute and so you have lots of concurrent transactions. Nesting can help isolate smaller parts of the database and increase overall concurrency.

A rollback or commit implicitly closes the transaction and a new transaction can be started. As mentioned before, SQLite does not support nested transactions, i.e., transactions within a transaction. Nested transactions can be useful when some change within a transaction should be made permanent while others should not be, all while not allowing other clients to see any partial updates. Databases like Oracle and Microsoft SQL Server do support nested transactions.

Read Transactions

While it is not common to place SELECT statements within a transaction, it can sometimes be useful when we want to guarantee that we read all tables from a defined state – this can be especially useful when generating reports.

Classic Example

The classic example to motivate transactions is a transfer of money from one account to another in a banking system. One account is debited and then another account is credited. No other client should see one account being debited before the other is credited as that would lead to inconsistency. Furthermore, most banking application have an accounting transactions log called the General Journal where all debits and credits are recorded. Let’s look at the code for doing this, starting with a definition of the tables. We will need a table for accounts and a table for the journal.

CREATE TABLE IF NOT EXISTS accounts ( 
    account_no INTEGER NOT NULL, 
    balance DECIMAL NOT NULL DEFAULT 0,
    PRIMARY KEY(account_no),
  CHECK(balance >= 0)
);

CREATE TABLE IF NOT EXISTS journal (
    change_no INTEGER PRIMARY KEY AUTOINCREMENT,
    account_no INTEGER NOT NULL, 
    flag TEXT NOT NULL, 
    amount DECIMAL NOT NULL, 
    change_date DATE NOT NULL,
    FOREIGN KEY (account_no) 
     REFERENCES accounts(account_no)
);

Next, we will insert sample data into the accounts table that we can use for a “transfer”. After this the database will contain two accounts, one with a balance of \(1100\) and another with a balance of \(2200\), each having an account number.

INSERT INTO accounts 
     (account_no, balance)
     VALUES (117860, 1100),
            (299871, 2200);

Now, the “transfer”: a transfer of \(100\) from account 117860 to account 299871 with an entry in the journal. This requires four data changes to the database: an update of the balance in the two accounts and insertions of two new rows into the journal. This must be done within a transaction because in a concurrent access scenario, our client might update the first balance, then the database might process a read from the database for another client that is also connected to the database. That client would now see that the balance is less but the other account is not yet updated: an inconsistency.

BEGIN TRANSACTION;

UPDATE accounts
   SET balance = balance - 100
 WHERE account_no = 117860;

UPDATE accounts
   SET balance = balance + 100
 WHERE account_no = 299871;

INSERT INTO journal 
     (account_no,flag,amount,change_date) 
     VALUES (117860, 'D', 100, date('now'));

INSERT INTO journal
     (account_no,flag,amount,change_date) 
     VALUES (299871, 'C', 100, date('now'));

COMMIT;

If any part of the update cannot be done, then we would issue a ROLLBACK rather than a COMMIT.

Let’s see what the tables contain after the transfer transaction.

NA

Table 1: 2 records

account_no	balance
117860	1000
299871	2300

NA

Table 2: 2 records

change_no	account_no	flag	amount	change_date
1	117860	D	100	2024-02-14
2	299871	C	100	2024-02-14

Experiment with this code and set up a concurrent situation and then run the code with an without the transaction logic.

Transactions in Applications

Of course, in an application, we would need to use conditional statements to determine when and whether to commit or rollback. In R, the above transaction code would be best placed within a function, perhaps as shown below. The function doTransfer takes source and a destination account numbers plus an amount to transfer and then executes the SQL statements within a transaction and commits if successful or rolls back if any of them failed. The function also takes the database connection object as a parameter.

The function dbSendStatement() is used to send update, delete, and insert statements plus other database commands such as for dropping tables, creating tables or indexes, and, of course, transaction logic. It is a synchronous method that returns once the command has been executed. As an alternative, we can call dbExecute() which calls dbSendStatement() and returns the number of rows affected; if that number is less than one then the command was not successful.

doTransfer <- function (dbcon, acctNoSrc, acctNoDest, amount)
{
  txnFailed = FALSE
  
  if (acctNoSrc == acctNoDest)
  {
    # can't transfer to same account
    return (FALSE)
  }
  
  dbExecute(dbcon, "BEGIN TRANSACTION")

  # debit source account
  sql <- "UPDATE accounts
             SET balance = balance - ?
           WHERE account_no = ?"
  ps <- dbSendStatement(dbcon, sql,
                        params = list(amount, acctNoSrc))
  if (dbGetRowsAffected(ps) < 1)
    txnFailed = TRUE
  dbClearResult(ps)

  # credit destination account
  sql <- "UPDATE accounts
             SET balance = balance + ?
           WHERE account_no = ?"
  ps <- dbSendStatement(dbcon, sql,
                        params = list(amount, acctNoDest))
  if (dbGetRowsAffected(ps) < 1)
    txnFailed = TRUE
  dbClearResult(ps)
  
  # add accounting transaction to journal
  sql <- "INSERT INTO journal 
           (account_no,flag,amount,change_date) 
          VALUES (?, 'D', ?, date('now'))"
  ps <- dbSendStatement(dbcon, sql,
                        params = list(acctNoSrc, amount))
  if (dbGetRowsAffected(ps) < 1)
    txnFailed = TRUE
  dbClearResult(ps)
  
  sql <- "INSERT INTO journal
           (account_no,flag,amount,change_date) 
          VALUES (?, 'C', ?, date('now'))"
  ps <- dbSendStatement(dbcon, sql,
                        params = list(acctNoDest, amount))
  if (dbGetRowsAffected(ps) < 1)
    txnFailed = TRUE
  dbClearResult(ps)


  # commit transaction if no failure, otherwise rollback
  if (txnFailed == TRUE)
    dbExecute(dbcon, "ROLLBACK TRANSACTION")
  else
    dbExecute(dbcon, "COMMIT TRANSACTION")

  # return status; TRUE if successful; FALSE if failed
  return (!txnFailed)
}

To test the function, we will call it with two account numbers and an amount.

srcAccount <- '117860'
destAccount <- '299871'
amount <- 99

status <- doTransfer(dbcon, srcAccount, destAccount, amount)

if (status == TRUE) {
  cat('Transfer successful')
} else {
  cat('Transfer failed')
}

Transfer successful

To verify that the database was correctly updated, we should inspect all the tables that were affected, but for brevity we will only look at the table containing the journal entries.

Table 3: 4 records

change_no	account_no	flag	amount	change_date
1	117860	D	100	2024-02-14
2	299871	C	100	2024-02-14
3	117860	D	99	2024-02-14
4	299871	C	99	2024-02-14

Now that we know that it commits correctly, let’s transfer to an account that does not exist; then the transaction should fail and no log entry should have been created.

srcAccount <- '117860'
destAccount <- '999999'
amount <- 200

status <- doTransfer(dbcon, srcAccount, destAccount, amount)

if (status == TRUE) {
  cat('Transfer successful')
} else {
  cat('Transfer failed')
}

Transfer failed

And, indeed, the journal doesn’t contain the entry.

Table 4: 4 records

change_no	account_no	flag	amount	change_date
1	117860	D	100	2024-02-14
2	299871	C	100	2024-02-14
3	117860	D	99	2024-02-14
4	299871	C	99	2024-02-14

Conclusion

Transactions are necessary to ensure data integrity when a database is modified concurrently by multiple users or applications. This lesson explained how to create transactions in SQLite and perform commit and rollback functions.

When databases can be accessed concurrently, all updates that consist of more than one modification must be done as a transaction, otherwise data integrity issues will arise.

---

Files & Resources

All Files for Lesson 70.821

---

See Also

• 60.701 Transaction Processing in Relational Databases

• 60.706 Concurrency Control in Relational Databases

References

SQLite Transactions

Errata

None collected yet. Let us know.