Transactions

zyBooks Sections 7.1 and 7.5

Transactions: Two viewpoints

• User of an RDBMS
• Managing a sequence of operations that must succeed or fail as one
• Example: Transferring money from a savings account to a checking account
• The balance of the savings account is lowered
• The balance of the checking account is raised
• Both UPDATES must succeed
• Developer of an RDBMS
• The RDBMS must allow and manage concurrent use of the database
• Two users may both be trying to buy the last item in stock, simultaneously

​

Simple transaction control in SQL

• START TRANSACTION
• SAVEPOINT svpt_name
• ROLLBACK [TO svpt_name]
• COMMIT
• SET AUTOCOMMIT = 0 (OFF) | 1 (ON)

Example

• Imagine a GUI lets you enter items with one-to-many relationships:
• A comment with multiple images
• A fighter mission with multiple targets, multiple refueling stops, multiple support missions, etc.
• User may add/edit/delete sub-items
• User may cancel at any time

​

Window opens

START TRANSACTION

INSERT … (empty item, only key)

OK button

​

UPDATE … (fields)

COMMIT

​

Saves this item’s fields as well as all the 1-many related items that were added

Cancel button

ROLLBACK

Deletes this item as well as all the 1-many related items that were added

Window opens

SAVEPOINT add-image

INSERT … (empty item, only key, fk)

OK button

UPDATE … (image caption field)

Cancel button

ROLLBACK to add-image

ACID properties of a transaction

• Atomic - succeeds or fails as a single unit
• Consistent - all data rules and constraints must be valid upon a commit - a transaction that tries to commit inconsistent data is rolled back
• Isolated - without interference from other concurrent transactions; as though it is being executed alone
• Durable - changes made are saved permanently upon a commit, even if the system fails or crashes

RDBMS Subsystem support

Table 6.1.1

Concurrency system support for isolation

• The concurrency system prevents conflicts like:
• Dirty read - a transaction reads data updated by an uncommitted transaction
• Non-repeatable read - a transaction repeatedly reads data that is changing
• Phantom read - a transaction is reading a series of rows while another adds or deletes one of the rows

Transaction isolation levels

• Can be set by administrators and programmers
• SERIALIZABLE - guaranteed isolation.
• REPEATABLE READ - transaction reads committed data only. Read data cannot be changed (by other transactions). Allows phantom reads.
• READ COMMITTED - transaction reads committed data only. Read data can be changed (by other transactions). Allows non-repeatable and phantom reads.
• READ UNCOMMITTED - can read uncommitted data.
• Performance tradeoffs

Concurrency

• An RDBMS must provide concurrency mechanisms such as row- and table-level locking, and shared and exclusive locks
• Must be able to handle issues such as deadlock with prevention, avoidance or detection
• Locking in postgres

Transactions in psycopg

• Opening a connection or cursor (manually) does not start a transaction
• Changes will not be written to the database without an explicit commit
• Using a connection context:
• with psycopg.connect() as conn:
• Using a connection context:
• with conn.transaction():
• Transaction management in psycopg

Example transaction

• In the profs project, a madiSTEM workshop must have a leader – there is no such thing as a workshop without a person attached to it.
• The relationship between person and workshop is M-N
• A person must exist before a workshop can be created, so the order of creation is:
• person
• workshop
• person_workshop

Example transaction (MySQL)

set autocommit=0;

START transaction;

INSERT INTO workshop (state, title, description,

capacity, max_repeat, event_year)

VALUES ('Proposed', 'Cool Science Stuff',

'Amazing activities', 12, 3, 2024);

INSERT INTO person_workshop (person_email, workshop_id, role)

VALUES ('petersva@jmu.edu', LAST_INSERT_ID(), 'Lead');

COMMIT;

-- or ROLLBACK

autocommit is on by default in Workbench

Get the most recent autoincrement value

Example transaction (Postgres)

BEGIN;

INSERT INTO workshop (state, title, description,

capacity, max_repeat, event_year)

VALUES ('Proposed', 'Cool Science Stuff', 'Amazing activities',

12, 3, 2024);

INSERT INTO person_workshop (person_email, workshop_id, role)

VALUES ('petershz@jmu.edu', lastval(), 'Lead');

COMMIT;

-- or ROLLBACK

​

autocommit is off by default in PgAdmin, BEGIN is more common than START

Get the most recent autoincrement value