Database Tutorial

Course Tutorial Site

Site Admin

Transaction Management

with 4 comments

This page shows you how transactions work. It also demonstrates how to use Transaction Control Language (TCL) commands (under the Database Engine folding tab). The demonstration highlights how to ensure primary to foreign key mapping works by placing logic on the database. The more frequently this type of logic is database-centric, the less likely you’ll introduce errors into your model. That’s especially true when you don’t enforce FOREIGN KEY constraints as database-level constraints.

A basic transaction occurs anytime you write DML statements to two or more tables. Transactions are broader than a single INSERT, UPDATE, or DELETE statement because they involve changes in multiple tables. You must guarantee that all DML statements work or all fail, which means that you shouldn’t write a change to one table unless you can guarantee writes to all tables.

Modern databases accomplish this task by applying the ACID transaction model. ACID stands for Atomic, Consistent, Isolated, and Durable. Atomic means everything or nothing happens. Consistent has various explanations in the literature (academe) and practice (business), but it can be summarized as all components yield the same result whether they occur sequentially or concurrently (in parallel). Isolation means that nobody but you can see any changes until all changes are made, which is typically done with a COMMIT; statement. Durable means the changes endure beyond your connection to the system.

Database engines work differently. Oracle is always in transactional mode, whereas MySQL only supports transactional models if you use the InnoDB or Falcon engines. In these examples, you use transactional engines. The PHP examples are based on your installation of the Zend Community Engine.

The preceding flow chart depict the logic of the program samples. It depicts that a transaction spans two or more independent DML statements. Through a transaction the set of DML statements becomes an atomic event that is ACID compliant.

Transaction Management

Oracle

These are the steps that demonstrate how to create a reusable framework for transaction management in Oracle. These steps are very straightforward when you have a command of PL/SQL, which is Oracle’s proprietary extension to the SQL language. It is extremely powerful and leads the industry in features that support database-centric development.

1. Sign on as the system user, and create a new user. Users in Oracle have their own schema or work area, and they don’t require a database like MySQL or SQL Server.

SQL> CREATE USER student IDENTIFIED BY student;
SQL> GRANT CONNECT, resource, CREATE any VIEW TO student;

2. Create a create_oracle_procedure.sql file with the following contents:

-- Conditionally drop the objects to make this rerunnable.
BEGIN
  FOR i IN (SELECT table_name
            FROM   user_tables
            WHERE  table_name IN ('A','B','TRANSACTION_ERROR_LOG')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name IN ('A_SEQ','B_SEQ','TEL_SEQ')) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/  
 
-- Create the tables and sequences.
CREATE TABLE a
( a_id   NUMBER CONSTRAINT a_pk PRIMARY KEY
, a_text VARCHAR2(12));
 
CREATE SEQUENCE a_seq;
 
CREATE TABLE b
( b_id   NUMBER CONSTRAINT b_pk PRIMARY KEY
, a_id   NUMBER 
, b_text CHAR(12) CONSTRAINT u_b UNIQUE
, CONSTRAINT fk_a FOREIGN KEY(a_id) REFERENCES a (a_id));
 
CREATE SEQUENCE b_seq;
 
-- Create the error handling table.
CREATE TABLE transaction_error_log
( error_log_id   NUMBER  CONSTRAINT tel_pk PRIMARY KEY
, table_name     VARCHAR2(12)
, table_id       NUMBER
, table_text     VARCHAR2(12));
 
CREATE SEQUENCE tel_seq;
 
-- Create a stored procedure.
CREATE OR REPLACE PROCEDURE double_insert
( input_a VARCHAR2, input_b VARCHAR2) IS
BEGIN
  /* Set savepoint. */
  SAVEPOINT starting_point;
 
  /* Insert a row into two tables. */
  INSERT INTO a VALUES (a_seq.NEXTVAL, input_a);
  INSERT INTO b VALUES (b_seq.NEXTVAL, a_seq.CURRVAL, input_b);
 
  /* This commits the writes to table A and B when there's no error. */
  COMMIT;
 
EXCEPTION
  /* This acts as an exception handling block. */
  WHEN OTHERS THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO starting_point;
 
    /* While in the same transaction context, this occurs after prior work is undone. */
    INSERT INTO transaction_error_log VALUES
    ( tel_seq.NEXTVAL, 'B', b_seq.CURRVAL, input_b);
 
    /* This commits the write to error handling table. */
    COMMIT;
 
    /* This returns control back to the execution block, and suppresses an error back to the user. */
    RETURN;
END;
/
 
-- Define a couple local session variables.
VARIABLE text1 VARCHAR2(12)
VARIABLE text2 VARCHAR2(12)
 
-- Assign values to session variables.
BEGIN
  :text1 := 'This is one.';
  :text2 := 'This is two.';
END;
/
 
-- Call the local procedure.
EXECUTE double_insert(:text1,:text2);
EXECUTE double_insert(:text1,:text2);
EXECUTE double_insert(:text1,:text2);
EXECUTE double_insert(:text1,'This is new');
 
-- Select the data set. 
SELECT * FROM a;
SELECT * FROM b;
SELECT * FROM transaction_error_log;

3. Quit the session as the system user. You can simply reconnect to the new schema like this:

SQL> CONNECT student/student@orcl

4. Run the file from the relative directory where you started the sqlplus executable.

SQL> @create_oracle_procedure.sql

You see the following query results at the end of the script:

      A_ID A_TEXT
---------- ------------
         1 This IS one.
         4 This IS one.
 
 
      B_ID       A_ID B_TEXT
---------- ---------- ------------
         1          1 This IS two.
         4          4 This IS NEW
 
 
ERROR_LOG_ID TABLE_NAME     TABLE_ID TABLE_TEXT
------------ ------------ ---------- ------------
           1 B                     2 This IS two.
           2 B                     3 This IS two.

A quick note to those new to automatic numbering in Oracle. You can find more on SQL Automated Numbering for Oracle, MySQL, and SQL Server here.

This demonstrates how to perform transactions and leverage TCL in the Oracle database.

MySQL

These are the steps that demonstrate how to create a reusable framework for transaction management in MySQL. It’s important that you note that error handling here works inside the BEGIN block, which differs from the Oracle model with the EXCEPTION block.

1. Sign on as the root user, if you need more on that go here. Create a database – storeddb:

CREATE DATABASE storeddb;

2. Create a user and grant them permissions:

mysql> CREATE USER 'student' IDENTIFIED BY 'student';
mysql> GRANT ALL ON storeddb.* TO 'student'@'localhost' IDENTIFIED BY 'student';

3. Create a create_mysql_procedure.sql file with the following contents:

-- Select the database.
USE storeddb;
 
-- Conditionally drop the objects to make this rerunnable.
DROP TABLE IF EXISTS a;
DROP TABLE IF EXISTS b;
DROP TABLE IF EXISTS transaction_error_log;
DROP PROCEDURE IF EXISTS double_insert;
 
-- Create the tables.
CREATE TABLE a
( a_id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, a_text CHAR(12));
 
CREATE TABLE b
( b_id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, a_id   INT UNSIGNED 
, b_text CHAR(12) UNIQUE KEY);
 
-- Create the tables.
CREATE TABLE transaction_error_log
( error_log_id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, TABLE_NAME     CHAR(12)
, table_id       INT UNSIGNED
, table_text     CHAR(12));
 
-- Reset the execution delimiter to create a stored program.
-- ----------------------------------------------------------------------
--  Somebody told me that this line seemed unnecessary to a few folks following 
--  the example. They skipped it, and encounter a host of meaningless errors.
-- 
--  Those errors are triggered because the semicolon, \g, or \G are execute
--  instructions to the MySQL engine. The semicolon inside a stored procedure 
--  or function serves a different purpose. Inside the stored program unit the 
--  semicolon is a statement and block terminator. You must change the MySQL
--  DELIMITER to something other than a semicolon before you try to create a
--  stored programming unit. You must then change it back if you plan to use the
--  semicolon in the rest of your program. Also, you MUST have a white space 
--  between the DELIMITER keyword and the symbol (either a $$ or ; in the sample
--  programs that are provided). 
-- ----------------------------------------------------------------------
DELIMITER $$
 
-- Create a stored procedure, the MODIFIES SQL DATA phrase is optional in MySQL 5.1+.
CREATE PROCEDURE double_insert
( input_a CHAR(12), input_b CHAR(12)) MODIFIES SQL DATA
BEGIN
  /* Declare a locally scoped variable. */
  DECLARE duplicate_key INT DEFAULT 0;
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
 
  /* Start transaction context. */
  START TRANSACTION;
 
  /* Set savepoint. */  
  SAVEPOINT both_or_none;
 
  /* Insert a row into two tables. */
  INSERT INTO a VALUES (NULL, input_a);
  INSERT INTO b VALUES (NULL, last_insert_id(), input_b);
 
  /* This acts as an exception handling block. */  
  IF duplicate_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT both_or_none;
 
    /* While in the same transaction context, this occurs after prior work is undone. */
    INSERT INTO transaction_error_log VALUES (NULL, 'B', last_insert_id(), input_b);
 
  END IF;
 
  /* This commits the writes to table A and B when the duplicate key constraint isn't violated. */
  COMMIT;
 
END;
$$
 
-- Reset the delimiter to the default.
-- ----------------------------------------------------------------------
--  Check the earlier note because this is a critical line in the sample.
-- ----------------------------------------------------------------------
DELIMITER ;
 
-- Declare a couple local session variables.
SET @text1 = 'This is one.';
SET @text2 = 'This is two.';
 
-- Call the local procedure.
CALL double_insert(@text1,@text2);
CALL double_insert(@text1,@text2);
CALL double_insert(@text1,@text2);
CALL double_insert(@text1,'New Text');
 
-- Query table contents. 
SELECT * FROM a;
SELECT * FROM b;
SELECT * FROM transaction_error_log;

4. Quit the session as the root user.

mysql> QUIT;

5. Sign on as the student user.

C:\> mysql -ustudent -pstudent

6. As the student user, source the file. You have two ways to do that. One leverage csh/Tcsh shell syntax and the other uses Bourne, Korn, or BASH shell syntax.

6(a). The csh/Tcsh syntax:

mysql> SOURCE create_mysql_procedure.sql

6(b). The Bourne, Korn, or BASH syntax:

mysql> \. create_mysql_procedure.sql

When you source it, you should see the following. Don’t worry if you see the three warnings because when you rerun the script they won’t be there. There telling you that the tables didn’t exist to be dropped the first time.

+------+--------------+
| a_id | a_text       |
+------+--------------+
|    1 | This IS one. |
|    4 | This IS one. |
+------+--------------+
2 ROWS IN SET (0.00 sec)
 
+------+------+--------------+
| b_id | a_id | b_text       |
+------+------+--------------+
|    1 |    1 | This IS two. |
|    4 |    4 | NEW Text     |
+------+------+--------------+
2 ROWS IN SET (0.00 sec)
 
+--------------+------------+----------+--------------+
| error_log_id | TABLE_NAME | table_id | table_text   |
+--------------+------------+----------+--------------+
|            1 | B          |        2 | This IS two. |
|            2 | B          |        3 | This IS two. |
+--------------+------------+----------+--------------+
2 ROWS IN SET (0.00 sec)

A quick note to those new to automatic numbering in MySQL. You use a null when you don’t want to write an override signature for the INSERT statement. You can find more on SQL Automated Numbering for Oracle, MySQL, and SQL Server here.

This demonstrates how to perform transactions and leverage TCL in the MySQL database.

SQL Server

Under development …

Written by michaelmclaughlin

January 26th, 2010 at 1:39 am

Posted in

4 Responses to 'Transaction Management'

Subscribe to comments with RSS or TrackBack to 'Transaction Management'.

  1. I believe the last line in the second paragraph should read:
    which means that you *shouldn’t* write a change to one table unless you can guarantee writes to all tables.

    Terrance M

    25 Mar 10 at 9:56 pm

  2. Excellent catch! It’s corrected. Thank you.

    michaelmclaughlin

    25 Mar 10 at 10:08 pm

  3. In the second paragraph of the page, last sentence should it say:
    “You must guarantee that all DML statements work or all fail, which means that you… shouldn’t … write a change to one table unless you can guarantee writes to all tables.”

    The change is indicated by use of ellipses.

    Jared M

    25 Mar 10 at 10:50 pm

  4. […] mirrored post on technical blog […]

Leave a Reply