SQLite Transaction Handling

Introduction

In this lab, we will explore SQLite transaction handling, focusing on maintaining data integrity through the use of transactions. We will learn how to begin and commit transactions, ensuring that multiple related operations are treated as a single, atomic unit.

This lab will guide you through the process of starting a transaction using BEGIN TRANSACTION, making changes to the database, and then permanently saving those changes using the COMMIT statement. You'll also learn how to undo changes using ROLLBACK and use SAVEPOINT for more granular control. This hands-on experience will solidify your understanding of how transactions guarantee atomicity, consistency, isolation, and durability (ACID properties) in SQLite.

Skills Graph

Create a Database and Table

In this first step, we'll create a SQLite database and a table to store user data. This will provide the foundation for exploring transaction handling in subsequent steps.

First, open your terminal in the LabEx VM. Your default path is /home/labex/project.

Now, let's create a SQLite database named mydatabase.db. Run the following command to create the database file and open the SQLite command-line tool:

sqlite3 mydatabase.db

You will see a prompt indicating that you are now inside the SQLite shell:

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Next, create a table named users to store basic user information. This table will have three columns: id (a unique identifier), name, and balance. Enter the following SQL command at the sqlite> prompt and press Enter:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL
);

This command sets up the users table where:

• id is an integer that serves as the primary key for each user.
• name is a text field representing the user's name.
• balance is a real number representing the user's account balance.

Now, insert some initial data into the users table:

INSERT INTO users (name, balance) VALUES ('Alice', 100.0);
INSERT INTO users (name, balance) VALUES ('Bob', 200.0);

These commands add two users, Alice and Bob, with initial balances of 100.0 and 200.0, respectively.

To confirm that the data was added correctly, run this command to view all records in the table:

SELECT * FROM users;

Expected Output:

1|Alice|100.0
2|Bob|200.0

This output shows the id, name, and balance for each record. The SELECT * command retrieves all columns from the specified table.

Begin and Commit a Transaction

In this step, you'll learn how to start and commit a transaction. Transactions are essential for ensuring data integrity when performing multiple related operations.

To start a transaction, use the BEGIN TRANSACTION command:

BEGIN TRANSACTION;

This command tells SQLite to start tracking changes, but not to permanently apply them to the database until you explicitly commit the transaction.

Now, let's transfer 50.0 from Alice to Bob:

UPDATE users SET balance = balance - 50.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 50.0 WHERE name = 'Bob';

These commands update the balances of Alice and Bob within the transaction. Alice's balance is decreased by 50.0, and Bob's balance is increased by 50.0.

To save the changes, use the COMMIT command:

COMMIT;

This command commits the transaction, making the changes permanent.

Verify the changes by querying the table again:

SELECT * FROM users;

Expected Output:

1|Alice|50.0
2|Bob|250.0

This output confirms that the transaction was successfully committed, and the balances of Alice and Bob have been updated accordingly.

Rollback a Transaction

In this step, you'll learn how to use the ROLLBACK command to undo changes made during a transaction. This is essential for handling errors and ensuring data integrity.

First, begin a new transaction:

BEGIN TRANSACTION;

Now, let's attempt to transfer 100.0 from Alice to Bob, but this time, we'll simulate an error condition:

UPDATE users SET balance = balance - 100.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 100.0 WHERE name = 'Bob';

Since Alice only has 50.0, the first update will result in a negative balance. While SQLite doesn't enforce constraints by default, we'll simulate an error occurring after this operation.

To undo the changes, use the ROLLBACK command:

ROLLBACK;

This command rolls back the transaction, undoing any changes made since the BEGIN TRANSACTION statement.

Verify that the changes have been rolled back by querying the users table:

SELECT * FROM users;

Expected Output:

1|Alice|50.0
2|Bob|250.0

This output confirms that the transaction was successfully rolled back, and the balances of Alice and Bob remain unchanged.

Implement Savepoints

In this step, you'll learn how to use savepoints within transactions. Savepoints allow you to create intermediate points within a transaction to which you can rollback, without rolling back the entire transaction.

First, begin a new transaction:

BEGIN TRANSACTION;

Create a savepoint named savepoint1:

SAVEPOINT savepoint1;

Now, let's transfer 20.0 from Alice to Bob:

UPDATE users SET balance = balance - 20.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 20.0 WHERE name = 'Bob';

Create another savepoint named savepoint2:

SAVEPOINT savepoint2;

Let's add a new user named 'Charlie' with an initial balance of 300.0:

INSERT INTO users (name, balance) VALUES ('Charlie', 300.0);

Now, let's say we decide that adding Charlie was a mistake. We can rollback to savepoint1, which will undo the INSERT statement and the transfer of 20.0 between Alice and Bob:

ROLLBACK TO SAVEPOINT savepoint1;

Verify the changes after rollback to savepoint1:

SELECT * FROM users;

Expected Output:

1|Alice|50.0
2|Bob|250.0

You should see that Charlie is not in the table, and Alice and Bob's balances are back to their values before savepoint1.

Now, let's transfer 10.0 from Alice to Bob:

UPDATE users SET balance = balance - 10.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 10.0 WHERE name = 'Bob';

Finally, commit the transaction:

COMMIT;

Add a Constraint to Prevent Negative Balances

In this step, you'll add a constraint to the users table to prevent negative balances. This will help ensure data integrity by preventing invalid transactions.

Add the following constraint to the users table:

ALTER TABLE users ADD CONSTRAINT positive_balance CHECK (balance >= 0);

This command adds a CHECK constraint named positive_balance to the users table. This constraint ensures that the balance column must always be greater than or equal to 0.

Now, let's try to transfer 1000 from Alice to Bob, which will violate the constraint:

BEGIN TRANSACTION;
UPDATE users SET balance = balance - 1000 WHERE name = 'Alice';
UPDATE users SET balance = balance + 1000 WHERE name = 'Bob';

This time, the UPDATE statement will throw an error because it violates the positive_balance constraint. You'll see an error message like: Error: CHECK constraint failed: positive_balance.

Rollback the transaction:

ROLLBACK;

Verify that the changes have been rolled back:

SELECT * FROM users;

Expected Output:

1|Alice|40.0
2|Bob|260.0

This confirms that the ROLLBACK successfully reverted the database to its original state before the failed transaction.

Summary

In this lab, you've learned the fundamentals of transaction handling in SQLite. You've covered how to begin and commit transactions, rollback changes, implement savepoints for more granular control, and add constraints to ensure data integrity. These skills are crucial for building robust and reliable database applications.