Unit 10 Transaction

1
Unit 10 Transaction
2
Transactions

• A transaction is a logical unit of work
• It begins with BEGIN TRANSACTION
• It ends with COMMIT or ROLLBACK
• The transaction manager is sometimes known as the
  TP Monitor (transaction processing monitor)
• Atomicity The manager guarantees that if any
  part of the transaction fails, the entire
  transaction will be rolled back, and the database
  set to its state before BEGIN

3
A Sample Transaction
4
Transaction Manager

• COMMIT signifies transaction has completed
  successfully
• ROLLBACK signifies transaction has encountered an
  error
• Implicit ROLLBACK if the transaction manager
  does not receive an explicit COMMIT, it will
  default to ROLLBACK
• After COMMIT or ROLLBACK, a message is returned
  to the user

5
Recovery Log

• A recovery log or journal keeps the before and
  after state for each transaction
• An active (online) log is kept for immediate
  recovery of recent activity
• An archive log is kept offline for more extensive
  recovery requirements
• Atomicity must be at the statement level (set
  processing)
• Transactions must not be nested

6
COMMIT Point

• Database updates are kept in buffers, and written
  to disk after COMMIT
• The log must be written before COMMIT
  (write-ahead log rule) so the database can
  recover if the system crashes after COMMIT but
  before disk writing is complete
• On COMMIT (defaults to) all database positioning
  is lost and tuple locks released
• This is the COMMIT point, or synchpoint
• ROLLBACK will return the database to the previous
  COMMIT point

7
The ACID Properties

• Atomicity Transactions are atomic
• Correctness (f/k/a Consistency) Transactions
  transform a correct state of the database into
  another correct state
• Isolation Transactions are isolated from one
  another
• Durability Once a transaction commits, its
  updates persist

8
MySQL Transactions
9
Transaction Syntax

• The START TRANSACTION or BEGIN statement begin a
  new transaction
• COMMIT commits the current transaction, making
  its changes permanent
• ROLLBACK rolls back the current transaction,
  canceling its changes

10
AutoCommit

• The SET AUTOCOMMIT statement disables or enables
  the default auto-commit mode for the current
  connection.
• SET AUTOCOMMIT0 1
• By default MySQL runs with auto-commit mode
  enabled
• As soon as you execute a statement that updates
  (modifies) a table, MySQL stores the update on
  disk
• If you are using a transaction-safe storage
  engine (like InnoDB, BDB or NDB Cluster), you can
  disable autocommit mode with
• SET AUTOCOMMIT0
• After disabling autocommit mode, you must use
  COMMIT to store your changes to disk or ROLLBACK
  if you want to ignore the changes you have made
  since the beginning of your transaction

11
Statements That Cannot Be Rolled Back

• Some statements cannot be rolled back
• Data definition language (DDL) create or drop
  databases create, drop, or alter tables or
  stored routines
• You should design your transactions not to
  include such statements
• If you issue a statement early in a transaction
  that cannot be rolled back, and then another
  statement later fails, the full effect of the
  transaction cannot be rolled back in such cases
  by issuing a ROLLBACK statement

12
Statements That Cause an Implicit Commit

• Each of the following statements implicitly end a
  transaction, as if you had done a COMMIT before
  executing the statement
• ALTER FUNCTION, ALTER PROCEDURE, ALTER TABLE,
  BEGIN, CREATE DATABASE, CREATE FUNCTION, CREATE
  INDEX, CREATE PROCEDURE, CREATE TABLE, DROP
  DATABASE, DROP FUNCTION, DROP INDEX, DROP
  PROCEDURE, DROP TABLE, LOAD MASTER DATA, LOCK
  TABLES, RENAME TABLE, SET AUTOCOMMIT1, START
  TRANSACTION, TRUNCATE TABLE, UNLOCK TABLES
• UNLOCK TABLES commits a transaction only if any
  tables currently are locked.

13
Statements That Cause an Implicit Commit (Cont.)

• CREATE TABLE, CREATE DATABASE DROP DATABASE, and
  TRUNCATE TABLE (MySQL 5.0.8)
• ALTER FUNCTION, ALTER PROCEDURE, CREATE FUNCTION,
  CREATE PROCEDURE, DROP FUNCTION, and DROP
  PROCEDURE (MySQL 5.0.13)
• The CREATE TABLE statement in InnoDB is processed
  as a single transaction.
• This means that a ROLLBACK from the user does not
  undo CREATE TABLE statements the user made during
  that transaction.

14
MySQL Transaction Example

• shellgt mysql test
• Welcome to the MySQL monitor. Commands end with
  or \g.
• Your MySQL connection id is 5 to server version
  3.23.50-log
• Type 'help' or '\h' for help. Type '\c' to clear
  the buffer.
• mysqlgt CREATE TABLE CUSTOMER (A INT, B CHAR (20),
  INDEX (A))
• -gt ENGINEInnoDB
• Query OK, 0 rows affected (0.00 sec)
• mysqlgt BEGIN
• Query OK, 0 rows affected (0.00 sec)
• mysqlgt INSERT INTO CUSTOMER VALUES (10,
  'Heikki')
• Query OK, 1 row affected (0.00 sec)
• mysqlgt COMMIT
• Query OK, 0 rows affected (0.00 sec)
• mysqlgt SET AUTOCOMMIT0

• Query OK, 0 rows affected (0.00 sec)
• mysqlgt INSERT INTO CUSTOMER VALUES (15, 'John')
• Query OK, 1 row affected (0.00 sec)
• mysqlgt ROLLBACK
• Query OK, 0 rows affected (0.00 sec)
• The InnoDB Storage Engine
• 827
• mysqlgt SELECT FROM CUSTOMER
• --------------
• A B
• --------------
• 10 Heikki
• --------------
• 1 row in set (0.00 sec)
• mysqlgt