Transactions

This lecture introduces the concept of database transactions in theory and out in the wild. We overview different isolation levels and the method of two-phase locking for supporting conflict-serializable schedules.

The reading material from the textbooks includes

• Chapter 20 and 21.1

• Chapter 17, 18.1, 18.2

You can find the slides of the lecture here Download here.

Live Lecture video (2021):

If you are curious about writing transactions in SQL these are the basic commands:

• BEGIN starts a transaction.
• COMMIT commits a transaction.
• ROLLBACK rolls back a transaction.
• SET autocommit = OFF tells the database to obey the commands given to start and commit/rollback transactions. If autocommit is instead set to ON, every single SQL statement will be executed in it's own transaction, which is commited/rolled back by the database server as soon as an SQL statement has been processed. By default, autocommit is set to ON in MySQL, and to OFF in PostgreSQL.