【　MySQL の InnoDB によるトランザクション処理　】

1. トランザクション対応の表作成
2. トランザクション処理
3. プログラミング上の注意
4. 新しいバージョンへの移行

概要 
  • MySQL では、表作成時に無指定の場合テーブル形式は、MyISAM
    になります
     
  • 既存表の形式は、SHOW TABLE STATUS で知る事ができます。また、
    SHOW CREATE TABLE 表名によって表形式のオプション指定方法
    を知る事もできます
     
  • トランザクションを利用する場合は、テーブル形式は InnoDB を
    使用します。以下にテーブル作成の CREATE 構文のサンプルを示し
    ます
    •  



    

    CREATE TABLE 商品マスタ (
  コード varchar(4),
  商品名 varchar(50),
  単価 int,
  PRIMARY KEY(コード)
) TYPE=InnoDB



    


  • このテーブルを SHOW CREATE TABLE で表定義出力すると以下のようになります
    • 

    CREATE TABLE `商品マスタ` (
  `コード` varchar(4) NOT NULL default '',
  `商品名` varchar(50) default NULL,
  `単価` int(11) default NULL,
  PRIMARY KEY  (`コード`)
) TYPE=InnoDB



    


  • MyISAM 形式の既存表を InnoDB 形式に変更するには、以下の構文を使用します
    • 

    ALTER TABLE 商品マスタ TYPE=InnoDB



    


  • 以下は、InnoDB 関係のデフォルトのシステム変数の値です
    ( 4.0.16-nt )
    • 

    


    
 
変数名
値

    
1

    innodb_additional_mem_pool_size
    		

    1048576
    

    
2

    innodb_buffer_pool_size
    

    8388608
    

    
3

    innodb_data_file_path
    

    ibdata1:10M:autoextend
    

    
4

    innodb_data_home_dir
    

     
    

    
5

    innodb_file_io_threads
    

    4
    

    
6

    innodb_force_recovery
    

    0
    

    
7

    innodb_thread_concurrency
    

    8
    

    
8

    innodb_flush_log_at_trx_commit
    

    1
    

    
9

    innodb_fast_shutdown
    

    ON
    

    
10

    innodb_flush_method
    

     
    

    
11

    innodb_lock_wait_timeout
    

    50
    

    
12

    innodb_log_arch_dir
    

    .\
    

    
13

    innodb_log_archive
    

    OFF
    

    
14

    innodb_log_buffer_size
    

    1048576
    

    
15

    innodb_log_file_size
    

    5242880
    

    
16

    innodb_log_files_in_group
    

    2
    

    
17

    innodb_log_group_home_dir
    

    .\
    

    
18

    innodb_mirrored_log_groups
    

    1
    

    
19

    innodb_max_dirty_pages_pct
    

    90


    

    

    

    


    
概要

    

  • ある期間の複数のテーブルへの更新の整合性を保つ為のロック方法
    であり、更新に対するキャンセル機能を持ったものです
     
  • トランザクションは明示的にコマンドを発行する事によって作成され
    ます。MySQL では、START TRANSACTION か BEGIN または 
    BEGIN WORK を使用します
     
  • トランザクションの終了は、COMMIT または ROLLBACK を実行する
    か、セッションの終了時に暗黙的に行なわれます
     
  • 更新をデータベースに反映するには、COMMIT を発行します。セッシ
    ョンの終了は ROLLBACK と同等ですが、必ず明示的に ROLLBACK
    を指定するのがプログラミングとして正しい使用法です
     
  • 他からの更新は、先にロックされていた場合は innodb_lock_wait_timeout
    の値を最大値としてその時間待たされます。その時間内にロックしたセッ
    ションが COMMIT または ROLLBACK を実行するとロックは解除され
    ます
     
  • 通常、SELECT 文はロックの影響を受けません、しかし、ロックされる
    前の情報しか返す事はありません
    •  



    


  • 以下に PHP における更新のサンプルを示します

    • 

    require_once( "db.php" );
 
$SQL	= new DB();
 
$SQL->Execute( "BEGIN" );
 
$Query	= "UPDATE 商品マスタ SET 単価 = 1234 where コード = '0001'";
$ret = $SQL->Execute( $Query );
if ( !$ret ) {
	$SQL->Execute( "ROLLBACK" );
}
else {
	$SQL->Execute( "COMMIT" );
}
 
$SQL->Close();



    

    


    
AUTOCOMMIT

    

  • AUTOCOMMIT は、セッションの変数でありデフォルトで 1 です。これ
    は、トランザクションの開始と終了の間以外は全て即更新されると言
    う事です
    •  



    


  • 以下の二つのソースコードは違った結果を表示します

    • 

    require_once( "db.php" );
 
$SQL	= new DB();
 
$SQL->Execute( "set autocommit = 1" );
 
$SQL->Execute( "BEGIN" );
 
$Query	= "UPDATE 商品マスタ SET 単価 = 1234 where コード = '0001'";
$SQL->Execute( $Query );
$SQL->Execute( "COMMIT" );
 
$Query = "SELECT * FROM 商品マスタ where コード = '0001'";
$ret = $SQL->Query( $Query );
$column = $SQL->Fetch( $ret );
print_r($column);
 
# 自動コミットの為、以下の UPDATE 文は即実行される
# ROLLBACK する為には、明示的に BEGIN を実行する必要がある
 
$Query	= "UPDATE 商品マスタ SET 単価 = 9999 where コード = '0001'";
$SQL->Execute( $Query );
$SQL->Execute( "ROLLBACK" );
 
$Query = "SELECT * FROM 商品マスタ where コード = '0001'";
$ret = $SQL->Query( $Query );
$column = $SQL->Fetch( $ret );
print_r($column);
 
$SQL->Close();
 



    

    require_once( "db.php" );
 
$SQL	= new DB();
 
$SQL->Execute( "set autocommit = 0" );
 
$SQL->Execute( "BEGIN" );
 
$Query	= "UPDATE 商品マスタ SET 単価 = 1234 where コード = '0001'";
$SQL->Execute( $Query );
$SQL->Execute( "COMMIT" );
 
$Query = "SELECT * FROM 商品マスタ where コード = '0001'";
$ret = $SQL->Query( $Query );
$column = $SQL->Fetch( $ret );
print_r($column);
 
# 非自動コミットの為、前の COMMIT が新たなトランザクションの開始位置となる
 
$Query	= "UPDATE 商品マスタ SET 単価 = 9999 where コード = '0001'";
$SQL->Execute( $Query );
$SQL->Execute( "ROLLBACK" );
 
$Query = "SELECT * FROM 商品マスタ where コード = '0001'";
$ret = $SQL->Query( $Query );
$column = $SQL->Fetch( $ret );
print_r($column);
 
$SQL->Close();

    

    

    

    


    
ガイドライン

    

  • AUTOCOMMIT の値をどうするかは、システムで統一します。しかし、
    本来のトランザクション処理は AUTOCOMMIT = 0 で行なうもので
    す。バッチ処理の場合は、AUTOCOMMIT = 1 でもかまいませんが、
    通常そのような場合はある一定の更新件数毎に COMMIT を発行し
    ます
     
  • ロックに対するタイムアウト時はエラーが返されます。そのような場合
    は、ロールバックしてエラー表示を伴なう画面表示を行なうのが通常
    です
     
  • トランザクションはセッション内で整合性が保たれます。他の接続を
    使用したり、一旦接続を解除するような事は避けなければなりません
    そういう意味でも、データベースへのアクセス関数等は意味を良く知
    った上で使用しなければなりません
     
  • ロックされている間に取得した SELECT 文での情報は古い情報であ
    る事に注意して下さい。テーブルの列に対する加算や減算に SELECT
    文で得た情報を使用してはいけません。必ず UPDATE 文内で記述し
    て下さい
     
    具体的なテストは、PHP の sleep 関数で行なう事ができます
    •  



    

    

    

    


    
エクスポートとインポート

    

  • MySQL のバージョンアップは非常に多いので、最新のバージョンでの
    テストが必要になります。その際に要求されるのがデータベースのエク
    スポートとインポートです
     
  • 最も簡単で解りやすいエクスポート方法は、以下のようになります
     
    mysqldump --all-database > ファイル名
     
  • インポートは以下の入力でログインしてから source ファイル名 を
    実行します
     
    mysql -u root -p --default-character-set=sjis -s
     
  • -s はサイレントモードです。結果の表示が抑制されます
  • ※ MySQL の sjis 設定は、winmysqladmin.exe で行います
    •  



    


  • 以下は、4.0.16 をアンインストールして 4.0.17 をインストールし、最初のサービス起動直後のログです
    ( C:\mysql\data\マシン名.err です )
    • 

    InnoDB: The first specified data file .\ibdata1 did not exist:
InnoDB: a new database to be created!
031230 18:13:00  InnoDB: Setting file .\ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
031230 18:13:02  InnoDB: Log file .\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
031230 18:13:03  InnoDB: Log file .\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
031230 18:13:14  InnoDB: Started



    


  • 以下は、mysql で、インポートした時のメッセージです
    ( MySQL と エラーメッセージの sjis 化処理は済ませてあります )
    • 

    C:\mysql\bin>mysql -u root -p --default-character-set=sjis -s
Enter password:
mysql> source all.sql
ERROR 1050: Table 'columns_priv' は既にあります
ERROR 1050: Table 'db' は既にあります
ERROR 1062: '%-test%-' は key 1 において重複しています
ERROR 1050: Table 'func' は既にあります
ERROR 1050: Table 'host' は既にあります
ERROR 1050: Table 'tables_priv' は既にあります
ERROR 1050: Table 'user' は既にあります
ERROR 1062: 'localhost-root' は key 1 において重複しています
ERROR 1062: '%-root' は key 1 において重複しています
ERROR 1062: 'localhost-' は key 1 において重複しています
ERROR 1062: '%-' は key 1 において重複しています