MySQL 5 and Transcation Processing

 

Objectives:  

Transaction Processing: 

Many times a change to the database requires several SQL-Statements, for example transferring money from a savings account to a checking account, requires an UPDATE to the savings account to decrement, and UPDATE to the checking account to increment it, and an INSERT INTO a journal.  This is called a transaction.  If any of the SQL-Statements fail, the database may wind up with incorrect data.  For example if the UPDATE to the savings account succeeds, but the UPDATE to the checking account does not, then your out $500, or whatever the amount is.  To protect against this problem most databases support transaction processing, this works by holding the SQL-Statements in a buffer area, until a COMMIT statement is issued.   A commit statement simply tells the database that it is ok to write the buffer items to disk.  If an error occurs you can undo the entire transaction with the ROLLBACK command. 

In MySQL, the InnoDB engine is required for transaction processing.  MyISAM does NOT support Transcation processing.

 

A transaction is:

        Logical Unit of Work.

        May Consist of Many SQL Statements.

        Commits and Rollbacks.

        Savepoints.

        Can be used to provide data consistency.

 

ACID Transaction Properties (Atomicity, Consistency, Isolation, Durability):

Transaction Isolation Levels (Phantom Reads, Non-repeatable Reads, Dirty Reads):

First step is to make sure auto-commit is turned off with: SET autocommit=0;

SET TRANSACTION:  Indicates the beginning of a transaction and sets key features.

            SET TRANSACTION;

            SET TRANSACTION READONLY;

            -- Transaction Level consistency
           
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

            -- Statement Level consistency
           
SET TRANSACTION ISOLATION LEVEL COMMITTED;

 

COMMIT:  Ends current transaction by saving database changes and starts new transaction.

 

ROLLBACK:  Ends current transaction by discarding database changes.

            ROLLBACK;

            ROLLBACK TO SAVEPOINT <save-point-name>;

 

SAVEPOINT:  Defines "midpoint" for the transaction to allow partial rollbacks.

SAVEPOINT <save-point-name>;

 

Example(s):  

Method One:

CREATE PROCEDURE transfer_funds (
	from_account INT, to_account INT, amount NUMBER(10,2))
BEGIN
	SET AUTOCOMMIT=0;
	
	UPDATE account_balance
		SET balance = balance - amount
	WHERE account_id = from_account;
	
	UPDATE account_balance
		SET balance = balance + amount
	WHERE account_id = to_account;
	
	INSERT INTO journal (from_acount_id, to_account_id, transfer_amount)
		VALUES (from_account, to_account, amount);
		
	COMMIT;
END;


CREATE PROCEDURE transfer_funds (
	from_account INT, to_account INT, amount NUMBER(10,2))
BEGIN
	START TRANSACTION;

	UPDATE account_balance
		SET balance = balance - amount
	WHERE account_id = from_account;
	
	UPDATE account_balance
		SET balance = balance + amount
	WHERE account_id = to_account;
	
	INSERT INTO journal (from_acount_id, to_account_id, transfer_amount)
		VALUES (from_account, to_account, amount);
		
	COMMIT;
END;


Method Two:  Using a SAVE POINT:

CREATE PROCEDURE savepoint_example(in_department_name VARCHAR(30),
                                   in_location VARCHAR(30),
                                   in_address1 VARCHAR(30),
                                   in_address2 VARCHAR(30),
                                   in_zipcode           VARCHAR(10),
                                   in_manager_id INT) 
BEGIN
	DECLARE sp_location_exists INT DEFAULT 0;
	DECLARE duplicate_dept  INT DEFAULT 0;
	
	
	START TRANSACTION;  
	-- Statement denotes the start of the transaction.  
	-- We can place this statement after our declarations, since they do not participate in any way in the transaction.
	
	
	-- Does the location exist? 
	SELECT COUNT(*)
	  INTO location_exists
	  FROM locations
	 WHERE location=in_location;
	-- In this SQL statement we check to see if a matching location exists.
	
	IF location_exists=0 THEN
		-- If the location does exists, we insert an audit log record 
		INSERT INTO AUDIT_LOG (audit_message)
		               VALUES (CONCAT('Creating new location',in_location));

		-- and then create the location.		
		INSERT INTO locations (location,address1,address2,zipcode)
		 VALUES (in_location,in_address1,in_address2,in_zipcode);
	ELSE
		-- If the location already exisits, we update it with thenew detail.	
		UPDATE locations set address1=in_address1,
				    address2=in_address2,
				    zipcode=in_zipcode
		 WHERE location=in_location;
		                  
	END IF;
	
	-- First save point that we gotten this far successfully.
	SAVEPOINT savepoint_location_exists;
	
	BEGIN
		-- Define an error handler that will fire in the event of a duplicate key error.
		-- If the handler is invoked, it will issue a rollback to our savepoint and then 
		-- 		set the duplicate_dept variiable so that we can detect that the rollback has occurred.
	     DECLARE DUPLICATE_KEY CONDITION FOR 1062;
		DECLARE CONTINUE HANDLER FOR DUPLICATE_KEY /*Duplicate key value*/
		        BEGIN
		        	SET duplicate_dept=1;
		        	ROLLBACK TO SAVEPOINT savepoint_location_exists;
		        END;
		
		INSERT INTO AUDIT_LOG (audit_message)
		       VALUES (CONCAT('Creating new department',in_department_name));
		        
		INSERT INTO DEPARTMENTS (department_name,location,manager_id)
		                 VALUES (in_department_name,in_location, in_manager_id);
		-- Insert an audit record and then insert a new department.
		-- If the department already exists with this name,
		--  the handler will fire, setting the duplicate_dept variable and rolling back to the savepoint.
		--  This partial rollback will undo the audit log entry for the new department, 
		--  but will preserve the inserts or update executed to ensure that the location existed.
		
		
		IF duplicate_dept=1 THEN
			-- Check here to see if the handler set the duplicate_dept flag.
			-- If so then update the departments table with the new information.	
			UPDATE departments
			   SET location=in_location,
				     manager_id=in_manager_id
			 WHERE department_name=in_department_name;
		END IF;
		                 
	END;
	
	COMMIT;
	
END;

   Alternate to SAVEPOINT Implementation:

CREATE PROCEDURE nosavepoint_example(in_department_name VARCHAR(30),
                                   in_location VARCHAR(30),
                                   in_address1 VARCHAR(30),
                                   in_address2 VARCHAR(30),
                                   in_zipcode  VARCHAR(10),
                                   in_manager_id INT)
BEGIN
	DECLARE location_exists    INT DEFAULT 0;
	DECLARE department_exists  INT DEFAULT 0;
		
	START TRANSACTION;
	
	-- Does the location exist?
	SELECT COUNT(*)
	  INTO location_exists
	  FROM locations
	 WHERE location=in_location;
	 
	IF location_exists=0 THEN
	
		INSERT INTO AUDIT_LOG (audit_message)
		               VALUES (CONCAT('Creating new location',in_location));
									
		INSERT INTO locations (location,address1,address2,zipcode)
		 VALUES (in_location,in_address1,in_address2,in_zipcode);
	ELSE
	
		UPDATE locations set address1=in_address1,
				    address2=in_address2,
				    zipcode=in_zipcode
		 WHERE location=in_location;
		                  
	END IF;
	
	-- Does the department exists?
	SELECT COUNT(*)
	  INTO department_exists
		FROM departments
	 WHERE department_name=in_department_name;
	
	IF department_exists=1 THEN 
	
		 UPDATE departments
			  SET location=in_location,
			     manager_id=in_manager_id
			WHERE department_name=in_department_name;
			
	ELSE
		
		INSERT INTO AUDIT_LOG (audit_message)
		       VALUES (CONCAT('Creating new department',in_department_name));
		        
		INSERT INTO DEPARTMENTS (department_name,location,manager_id)
		       VALUES (in_department_name,in_location, in_manager_id);
		                 
	END IF;
		                 	
	COMMIT;
	
END;

METHOD THREE - Nested Transactions with SAVE POINT.:

 

CREATE PROCEDURE nested_tfer_funds(
  in_from_acct INTEGER,
  in_to_acct   INTEGER,
  in_tfer_amount DECIMAL(8,2))
BEGIN

  DECLARE txn_error INTEGER DEFAULT 0 ;

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
    SET txn_error=1;
  END;

  SAVEPOINT savepoint_tfer;

  UPDATE account_balance
     SET balance=balance-in_tfer_amount
   WHERE account_id=in_from_acct;

  IF txn_error THEN
    ROLLBACK TO savepoint_tfer;
    SELECT 'Transfer aborted ';
  ELSE
    UPDATE account_balance
       SET balance=balance+in_tfer_amount
     WHERE account_id=in_to_acct;

     IF txn_error THEN
        ROLLBACK TO savepoint_tfer;
        SELECT 'Transfer aborted ';
     END IF;
  END IF;

END;

DEADLOCKS:

 

CREATE PROCEDURE tfer_funds2
       (from_account int, to_account int,
        tfer_amount numeric(10,2), OUT out_status INT,
        OUT out_message VARCHAR(30))
BEGIN

	DECLARE deadlock INT DEFAULT 0;
	DECLARE attempts INT DEFAULT 0;

	-- Setup a label loop to try the transaction three times.
	tfer_loop:WHILE (attempts<3) DO
		BEGIN
			-- Prepare the block for the execution of the transaction.
			-- Defeine an EXIT handler and associate it with the deadlock error.
			-- When a deadlock occurs, the handler will set a variable indicating faluure, issue a ROLLBACK, 
			--	and then terminate the block, while remaining within the loop. 
			DECLARE deadlock_detected CONDITION FOR 1213;
			DECLARE EXIT HANDLER FOR deadlock_detected 
				BEGIN
					ROLLBACK;
					SET deadlock=1;
				END;
			SET deadlock=0;
			
			START TRANSACTION;
				
			UPDATE account_balance
			   SET balance=balance-tfer_amount
			 WHERE account_id=from_account;
	 		
			UPDATE account_balance
		 	  SET balance=balance+tfer_amount
			 WHERE account_id=to_account;
			 
			COMMIT;
			
		END;
		IF deadlock=0 THEN  -- No deadlock occurred! bye, bye...
			LEAVE tfer_loop;
		ELSE
			SET attempts=attempts+1; -- deadlock occurred, try it again.
		END IF;
	END WHILE tfer_loop;
	
	IF deadlock=1 THEN  -- All three attempts resulted in a deadlock, notify the user.
		SET out_status=-1;
		SET out_message="Failed with deadlock for 3 attempts";
	ELSE  -- We finally succeeded on (attempts)...
		SET out_status=0;
		SET out_message=CONCAT("OK (",attempts," deadlocks)");
	END IF;
		
END;

Locking Rows in order to avoid deadlock condition in the first place:

 

CREATE PROCEDURE tfer_funds3
       (from_account int, to_account int,tfer_amount numeric(10,2))
BEGIN
	DECLARE local_account_id INT;
	DECLARE lock_cursor CURSOR FOR 
		SELECT account_id
		  FROM account_balance
		 WHERE account_id IN (from_account,to_account)
		 ORDER BY account_id
		   FOR UPDATE;  -- The FOR UPDATE part will handle the locking for us.
		   
	START TRANSACTION;
	
	OPEN lock_cursor;
	FETCH lock_cursor INTO local_account_id;
	
	UPDATE account_balance
	   SET balance=balance-tfer_amount
	 WHERE account_id=from_account;
	 
	UPDATE account_balance
	   SET balance=balance+tfer_amount
	 WHERE account_id=to_account;
	 
	CLOSE lock_cursor;
	 
	COMMIT;
END;  

LOCKING STRATEGIES:

A deadlock is the most severe result of locking.  It occurs when two programs are competing for a lock on the same row in a table.  In this situation, MySQL will wait a total of 50 seconds (default) to get the lock.  If unable to get the lock, MySQL will raise ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.  MySQL will also rollback the transaction.  The wait timeout can be adjusted with the innodb_lock_wait_timeout parameter in the configuration file.

First Example without any locking strategies:

CREATE PROCEDURE tfer_funds4
       (from_account int, to_account int,tfer_amount numeric(10,2),
        OUT status int, OUT message VARCHAR(30))
BEGIN
	DECLARE from_account_balance NUMERIC(10,2);
	
	SELECT balance
	  INTO from_account_balance
	  FROM account_balance
	 WHERE account_id=from_account;
	 
	IF from_account_balance >= tfer_amount THEN  
		   
		START TRANSACTION;
	
		UPDATE account_balance
		   SET balance=balance-tfer_amount
		 WHERE account_id=from_account;
	 
		UPDATE account_balance
		   SET balance=balance+tfer_amount
		 WHERE account_id=to_account;
		COMMIT;
		
		SET status=0;
		SET message='OK';
	ELSE
		SET status=-1;
		SET message='Insufficient funds';
	END IF;
END;

Pessimistic locking strategy:

Assume that concurrent updates are likely to occur and write programs to prevent them from happening. 

To do this you must lock rows as soon as they are read.  This is the safest and least flexible. 

It requires other programs to wait until you are done with the row/table.

CREATE PROCEDURE tfer_funds5
       (from_account int, to_account int,tfer_amount numeric(10,2),
        OUT status int, OUT message VARCHAR(30))
BEGIN
	DECLARE from_account_balance NUMERIC(10,2);
	
	START TRANSACTION;
	
	SELECT balance
	  INTO from_account_balance
	  FROM account_balance
	 WHERE account_id=from_account
	   FOR UPDATE;  -- Lock as soon as they are read.
	 
	IF from_account_balance>=tfer_amount THEN  
	
		UPDATE account_balance
		   SET balance=balance-tfer_amount
		 WHERE account_id=from_account;
	 
		UPDATE account_balance
		   SET balance=balance+tfer_amount
		 WHERE account_id=to_account;
		COMMIT;
		
		SET status=0;
		SET message='OK';
	ELSE
		ROLLBACK;
		SET status=-1;
		SET message='Insufficient funds';
	END IF;
END; 

Optimistic locking strategy:

Assume that it is unlikely that anyone will update a row between the time we view it and the time we update it. 

Since we cannot be sure that this assumption is true, we must then, at the last possible moment, make sure that the row has not been updated.

If the row has been updated, the transaction cannot be trusted and will have to be aborted.


CREATE PROCEDURE tfer_funds6
       (from_account INT, to_account INT, tfer_amount NUMERIC(10,2),
        OUT status INT, OUT message VARCHAR(30) )
    
BEGIN

  DECLARE from_account_balance    NUMERIC(8,2);
  DECLARE from_account_balance2   NUMERIC(8,2);
  DECLARE from_account_timestamp1 TIMESTAMP;
  DECLARE from_account_timestamp2 TIMESTAMP;

  SELECT account_timestamp,balance
    INTO from_account_timestamp1,from_account_balance
    FROM account_balance
   WHERE account_id=from_account;

  IF (from_account_balance>=tfer_amount) THEN

    -- Here we perform some long running validation that
    -- might take a few minutes */
    CALL long_running_validation(from_account);

    START TRANSACTION;

    -- Make sure the account row has not been updated since
    --         our initial check
    SELECT account_timestamp, balance
      INTO from_account_timestamp2,from_account_balance2
      FROM account_balance
     WHERE account_id=from_account
       FOR UPDATE;

     IF (from_account_timestamp1 <> from_account_timestamp2 OR
         from_account_balance    <> from_account_balance2)  THEN
       ROLLBACK;
       SET status=-1;
       SET message=CONCAT("Transaction cancelled due to concurrent update",
                          " of account"  ,from_account);
    ELSE
       UPDATE account_balance
          SET balance=balance-tfer_amount
        WHERE account_id=from_account;

       UPDATE account_balance
          SET balance=balance+tfer_amount
        WHERE account_id=to_account;

       COMMIT;

       SET status=0;
       SET message="OK";
    END IF;

  ELSE
    ROLLBACK;
    SET status=-1;
    SET message="Insufficient funds";
  END IF;
END$$

The choice between the two strategies is based on a trade-off between concurrency and robustness: 

Pessimistic locking is less likely to require transaction retries or faulures, while optimistic locking minimizes the duration of lucks, thus improving concurrency and transaction throughput.

Usually, we choose optimistic locking only if the duration of the locks or the number of rows locked by the pessimistic solution would be unacceptable.

Transaction Design Guidelines:

A well-designed transaction should have the following properties: