2011年12月29日木曜日

デッドロックの相手を調べる方法

「メッセージ 1205、レベル 13、状態 51、行 2」
「トランザクション (プロセス ID 99) が、ロック 個のリソースで
他のプロセスとデッドロックして、このトランザクションがそのデッドロックの対象となりました。
トランザクションを再実行してください。」

このエラーが出た場合は、どう対処すればよいだろうか?
順を追って説明する。

■デットロックとは
デットロックとは、相手(別のSQL)と自分(自分が流したSQL)とで、
ロックをかけ合い、お互い相手を待つことである。

これは、どういう事かというと、処理が一切できなくなることを意味する。
データベースは、それを自動的に解除する仕組みを持っていて、心配はいらない。

しかし、片方のSQLがロールバックされてしまう。どちらがロールバックされるかだが
コストが小さい(戻す処理が少ない)方が優先されるようである。

上記のエラーが出た場合、デットロックが起きてしまったことは分かるのだが、
SQL Serverの初期設定では、デットロックの相手が分からない。

■デットロックの相手を調べる

ログを残すには、以下のコマンドを実行する。
---------------------------------------------------
DBCC TRACEON(1204, 3605, -1)
GO
---------------------------------------------------
このコマンドを実行した後に、デットロックが発生すると
ログファイルに記録されるようになる。


■デットロックの再現方法

デットロックは、タイミングによって起きたり起きなかったりする。
これでは、デットロックがどんな風に起きたのか、スッキリしないであろう。
ここでは、シンプルな条件で実際にデットロックを起こしてみる。

以下のスクリプトをテスト環境などで流してもらいたい。
まず、デットロックが起こせる環境を準備する。
---------------------------------------------------

--テーブルの作成
CREATE TABLE T_TEST
(
  ID INT NOT NULL,
  VALUE CHAR(2) NOT NULL
)
GO

--テストデータを入れる
INSERT INTO T_TEST VALUES
(
  1,
  '11'
)

INSERT INTO T_TEST VALUES
(
  2,
  '22'
)

-- 一意キーをセットする
ALTER TABLE T_TEST
ADD CONSTRAINT PK_T_TEST
PRIMARY KEY(ID)
GO

COMMIT

--データが2レコード表示される
SELECT * FROM T_TEST

---------------------------------------------------
ここからが、デットロックの再現方法である

SQL Server Management Studeioを起動する
また別に、SQL Server Management Studeioを起動する

初めに起動した方を「A」、後から起動した方を「B」と呼ぶとする。

まず、「A」で以下を実行
---------------------------------------------------
BEGIN TRANSACTION

UPDATE T_TEST SET VALUE = '11' WHERE ID = 1
---------------------------------------------------

次に、「B」で以下を実行
---------------------------------------------------
BEGIN TRANSACTION

UPDATE T_TEST SET VALUE = '22' WHERE ID = 2
---------------------------------------------------

さらに、「A」で以下を実行
---------------------------------------------------
UPDATE T_TEST SET VALUE = '22' WHERE ID = 2
---------------------------------------------------

最後に、「B」で以下を実行
---------------------------------------------------
UPDATE T_TEST SET VALUE = '11' WHERE ID = 1
---------------------------------------------------

ここで、例のデットロックエラーが発生したはずである。
もし出なかった場合は、「ROLLBACK」コマンドを複数回実行して
トランザクションが開始されていないことを確認後、再実行してほしい。


■デットロックをログで確認する

SQL Server Management Studeioを起動する
オブジェクトエクスプローラ内で、
「管理」→「SQL Serverログ」→「現在」をクリックする

画面の上にある「検索」ボタンを押下する
検索する文字列を「Deadlock」と入力して検索を実行する

検索でヒットしたレコードから上に眺めていくと
「Node:1」「Node:2」が見つかるはずである。これは先ほど
SQL Server Management Studeioで実行した「A」「B」のことである。

実際のログデータを抜き出してみた。
★の箇所に、デットロックしたSQLが記録されている
---------------------------------------------------
2011-12-XX 18:37:07.06 ResType:LockOwner Stype:'OR'Xdes:0x83FDB200 Mode: X SPID:74
2011-12-XX 18:37:07.06 Requested By:
★2011-12-XX 18:37:07.06 Input Buf: Language Event: UPDATE T_TEST SET VALUE = '22' WHERE ID = 2
2011-12-XX 18:37:07.06 SPID: 76 ECID: 0 Statement Type: UPDATE Line #: 2
2011-12-XX 18:37:07.06 Owner:0x8016E120 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:76
2011-12-XX 18:37:07.06 Grant List 3:
2011-12-XX 18:37:07.06 KEY: 5:72057594088062976 (010086470766) CleanCnt:2 Mode:X
2011-12-XX 18:37:07.06 Node:2
2011-12-XX 18:37:07.06 ResType:LockOwner Stype:'OR'Xdes:0x8549BA00 Mode: X SPID:76
2011-12-XX 18:37:07.06 Requested By:
★2011-12-XX 18:37:07.06 Input Buf: Language Event: UPDATE T_TEST SET VALUE = '11' WHERE ID = 1
2011-12-XX 18:37:07.06 SPID: 74 ECID: 0 Statement Type: UPDATE Line #: 2
2011-12-XX 18:37:07.06 Owner:0x8016FDE0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
2011-12-XX 18:37:07.06 Grant List 3:
2011-12-XX 18:37:07.06 KEY: 5:72057594088062976 (020068e8b274) CleanCnt:3 Mode:X
2011-12-XX 18:37:07.06 Node:1
---------------------------------------------------

■デットロック対策

これまでに実践したものを書き出してみる

・データへのアクセス順番をそろえる(Order byでソートしてアクセス)
・ロックの単位をなるべく小さくする(一意キー単位での更新)
・トランザクションはなるべく短くする(細かくCOMMITする)
・ロックに影響を受けにくくする「SET READ_COMMITTED_SNAPSHOT ON」
・UPDATEを実行する前に、あらかじめ対象データを排他ロックさせる
SELECT ID FROM T_TEST WITH ( ROWLOCK, UPDLOCK ) WHERE ID = 1 --行ロック+更新ロック
・デットロックは、起きてしまうものと楽観的にとらえ、起きたらやり直しさせる
ERROR_NUMBER() = 1205 でデットロックと判断できるため、ループして再実行
・複数端末で同時アクセスされないようアプリ自体で排他する

■最後に

デットロックは、時としてとんでもないトラブルの原因となる。
さらに厄介なのは、再現が難しく、簡単には修正できないことも多い。
処理量が増えてくると急に頻発し、担当者を長期間悩ませたりする。

こんなにもイヤな状況に陥らないためには、テーブル設計やコーディング段階で
「ここではデットロックは起きないだろうか?」と自問自答しながら、
SQLレベルで意識して開発していくことが極めて重要である。

2011年6月2日木曜日

IDENTITYが重複した値を返す

SQLServerでは、Oracleと違いCREATE SEQUENCEコマンドがない。
代替方法として、IDENTITYで連番が採番されるテーブルを用意しておき、
ストアド側で以下のように呼ぶ。

-- トランザクション
BEGIN TRANSACTION @TRAN;

-- セーブポイント
SAVE TRANSACTION SP;

-- 連番取得
INSERT INTO 採番テーブル名 DEFAULT VALUES;
SET @連番 = SCOPE_IDENTITY();

-- セーブポイントにロールバック
ROLLBACK TRANSACTION SP;

-- コミット
COMMIT TRANSACTION @TRAN;

上記のやり方で、シーケンス番号が取得できる。

が、しかしである。ある環境だと希に奇妙な問題が発生する。
シーケンス番号のはずが、重複した値が返るのだ・・・

これは、どういうことかというと2CPU(2ソケット)以上を
搭載しているサーバー機であると、ストアドのシーケンス
番号取得部分(SCOPE_IDENTITY)がパラレル実行され
瞬間的にCPUそれぞれで同じ番号を取得できてしまうらしい。
複数コアCPUの場合では、まだ発生を確認できていない。

この問題は、SQLServer 2008 Expressで確認しており
2005など下位バージョンでも発生する可能性がある。

【回避方法】
1CPUのみ利用する設定に変更することで回避できるようだ。
以下のようなコマンドを実行する。ただし、パフォーマンスが
少し落ちるため、実行する前に影響範囲を検討頂きたい。

■DB単位で設定する場合
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

■ストアド単位で設定する場合
OPTION (MAXDOP 1)

より詳しい解説については、以下のサイトでコメントがある。

■SCOPE_IDENTITY() sometimes returns incorrect value
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=328811

■You may receive incorrect values when using SCOPE_IDENTITY() and @@IDENTITY
http://support.microsoft.com/default.aspx?scid=kb;en-US;2019779

■SQL SERVER ? 2008 ? SCOPE_IDENTITY Bug with Multi Processor Parallel Plan and Solution
http://blog.sqlauthority.com/2009/03/24/sql-server-2008-scope_identity-bug-with-multi-processor-parallel-plan-and-solution/

発生時のインパクトが大きいだけに、なるべく早く根本的に解決してほしいものだ。

2011年5月22日日曜日

LoadLibrary 193 Error と 64bit環境で32bit DLLコンパイル方法

LoadLibraryでDLLをロードする際に「193」エラーコードが
返ってくる場合がある。

呼び出そうとしているDLLが確かに存在していおり
パスが通っているか、EXEがあるフォルダに置いたとしても
エラーが解決されない場合がある。
DLLの状態を確認するコマンドに「dumpbin /dependents hoge.dll」がある。

この原因は、そのDLLが64bit用にコンパイルされたもので
32bit環境で呼び出そうとした場合が考えられる。

であれば、DLLを32bit用に再コンパイルすれば解決する。

しかし、Windows7 64bit環境で、nmakeなどを使って32bit環境用に
コンパイルする際は、いくつか設定すべき項目がある。

今回は、64bitOSで32bit用のDLLを作成する手順である。

・Windows SDKのコマンドラインツール(CMD Shell)の設定
以下のコマンドを実行
SetEnv /x86 /xp
実行すると、ウインドウタイトルが「x86」に変わる

・メイクファイルの「LDFLAGS」の項目に以下を追加
-MACHINE:X86

参考までに、上記2点の統一が取れていないと以下のエラー表示
「fatal error LNK1112: module machine type 'X86' conflicts with target machine type 'x64'」

・そしてDLLの出力先ディレクトをx86用のパスに設定
これは、何を意味しているかというと、
64bit用は、「Program Files」配下に作成されるのをご存じだろう。
今回の出力先は、「Program Files」配下ではエラーになる。
例えばCドライブ直下にbinフォルダを作成し、そこを出力されるよう
メイクファイルを書き直す(例:DIR_BIN=C:\bin)

参考までに、ここが間違っていると以下のエラーが表示される
「fatal error C1905: Front end and back end not compatible (must target same processor).」

上記の手順を行った後、DLLを再コンパイルし、LoadLibraryを実行してみてほしい。
すんなり通ったのではないだろうか?

ちなみに、  LoadLibraryがもう少し分かりやすいエラーを返してほしいものである。

2011年4月27日水曜日

SQL Server 2008 Express インストール

最近は、データベースもコストダウンの対象になり
無償版を利用することが多くなった。

SQL Server 2008 R2 Expressは、データベースサイズの上限が
10Gbytesまで拡大されたため、活用できる範囲が多いと感じる。

以下の手順は、稼働中のWindows 2003 Serverへ導入した記録である。
ネットにつなげない環境であったため、事前にインストーラーを
準備してからセットアップを開始した。

■インストールが必要なもの
Windows インストーラ 4.5 (再起動が必要です)
Windows PowerShell 1.0
Microsoft .Net Framework 3.5 SP1
Microsoft .Net Framework 3.5 Langpack
SQL Server 2008 Express with Advanced Services
(Management Studioを含む)

※OSのバージョンによっては、インストール物が上記と
異なる場合があるため、事前に確認すること

※ネットにつなげない環境の場合
Frameworkをインストールする場合、コマンドプロンプトから
「dotnetfx35.exe /lang:enu」のオプションでスキップ可能

■インストール時の選択内容
「SQL Server 2008 Express with Advanced Services」
機能
・データベースエンジンサービス
・管理ツールー基本
・SQLクライアント接続SDK
インスタンス構成
・規定のインスタンス
サービスアカウント
・サーバのログイン名、パスワード入力
認証モード
・混合モード
・管理者の指定
administratorを選択

■データベースメモリ設定
「max server memory」を設定します
物理メモリより小さくし、いくらか空きメモリは残すこと

■データベースの作成
データベース名を入力

■モード設定
ALTER DATABASE データベース名
SET READ_COMMITTED_SNAPSHOT ON
これは、必要に応じて設定します

■ユーザー設定
USE データベース名
CREATE LOGIN ログイン名 WITH PASSWORD = 'パスワード文字列' , DEFAULT_DATABASE = データベース名
CREATE USER ユーザー名 FOR LOGIN ログイン名 WITH DEFAULT_SCHEMA = dbo
※ログイン名、パスワード文字列、ユーザー名に任意の名称をセット

■権限の設定
Management Studio→セキュリティ→ログイン→作成したユーザー名を選択
サーバーロールを選択
以下の項目にチェックを入れる
「serveradmin」
「sysadmin」

■ネットワーク設定
構成マネージャを起動
SQL Serverのネットワーク構成を選択
プロトコルを選択
TCP/IPを選択し、右クリックで「有効」選択
名前つきインスタンスの場合、「IPALL」のTCPポートを1433にセット
SQL Serverのサービスを再起動する
※Expressエディションは、名前付きインスタンスのため、上記設定が必要

■ポート設定
TCP アクセス用に Windows ファイアウォールのポートを開く
Windows ファイアウォール→例外→ポート追加
ポート番号:1433
TCPが選択されていること

以上を設定後、ネットワーク越しにDBに接続できるか確認すること
※データベースに個人情報などを入れる場合は、セキュリティ機器を設置したり
データを暗号化するなど十分な対策を講じること

2011年4月23日土曜日

SQLServerで独立トランザクション

SQLServerは、ログを取りたいのだが、最後にロールバックされると
すべて戻ってしまう。これでは、トラブルの原因を調べる際にとても不便だ。

オラクルでは、「PRAGMA AUTONOMOUS_TRANSACTION」句で
独立トランザクションが簡単に実現できる。

SQLServerでも、同じ事ができないか?
SQLServer2008だと可能である。手順は以下の通り

■ループバックを設定
USE MASTER
GO
EXEC sp_addlinkedserver @server = N'loopback',@srvproduct = N' ',@provider = N'SQLNCLI', @datasrc = @@SERVERNAME
GO
EXEC sp_serveroption loopback,N'remote proc transaction promotion','FALSE'
EXEC sp_serveroption loopback,N'RPC OUT','TRUE' -- Enable RPC to the given server.
GO

■使い方
EXECUTE [loopback].[DB名].[dbo].[独立して実行したいストアド名]

これで、最後に「ROLLBACK TRANSACTION 」が実行されたとしても
ロールバックされずに済む。

■欠点
パフォーマンスが良くない。
ただ、ループ内などで連続で呼んだりしなければ、実用に耐える。
気になる方は、実験してから利用することを勧める。

SQLServer リソースプールinternalのメモリ不足です

■原因について
「max server memory」を指定していない場合、
物理メモリをすべて食い尽くし、SQLServer自体で
利用するメモリが不足するためと考えられる。
SQLServerの仕様のようだが、本番で発生すると痛い。

■対処法
   「max server memory」を設定して下さい。
物理メモリより当然少なくし、OSが利用する分、
SQLServerが利用する分を差し引いた残りを設定する。
トラブル時で時間がないときは、とりあえずサーバ再起動が早道

■詳細
「max server memory」の既定値は2,147,483,647Mbytes
となっている。既定値のままだとどんどんメモリを食う。
消費したメモリは、どこに使われたかというと
「バッファキャッシュ」という所に割り当てられている。
これは、あらかじめディスクから読み込んだデータを
メモリ上に展開しておき、次回から高速に応答できるようにしている。

SQLServerのバッファキャッシュが解放される条件は、
内部的に空き容量(Free Pages)の状態かつ
1)コンピュータを再起動した場合
2)他のアプリケーションからのメモリ要求が発生した場合
だそうだ。

「DBCC DROPCLEANBUFFERS」というコマンドがあるが
これは、物理的にメモリを解放せず、メモリ使用量は減らない。

2011年4月14日木曜日

SQLServer2008 ログファイルサイズを縮小する

デフォルト自動拡張がONになっていて、ディスク容量を
食いつぶした場合に有効な方法

■バックアップを取る

--USBメモリなどにとりあえずバックアップ
USE MASTER
BACKUP LOG DB名 TO DISK = 'D:\backup.bak'

定期的にバックアップをバッチで取る場合は、
BACKUP LOG DB名 TO DISK = 'D:\backup.bak' WITH INIT
として、バックアップが1つだけ残るようにする

■論理名を取得

USE DB名
SELECT name FROM sys.database_files

■ログファイルを圧縮

DBCC SHRINKFILE (取得した論理名, サイズMB)

■自動拡張の上限をセット

対象のDBを選択してプロパティを表示
ファイルを選択し、自動拡張の欄で最大サイズを入力

■最後に
ディスクの空き容量を確認して、増えていればOK

2011年4月13日水曜日

Windows7 64bit + Oracle + Access2007 でリンクテーブル

■ODBCの設定をする
C:\Windows\SysWOW64\odbcad32.exe を起動する
※64bitOS特有の起動手順

ユーザーDSNを選択
Microsoft ODBC for Oracleを選択
データソース名は任意
ユーザー名は、DBのユーザー名
サーバーは、サーバ名:1521/DB名
※DB名はTNS名ではなくSIDを指定する
※1521はポート番号でデフォルトこの値で問題ない

■リンクテーブルを張る
外部データの取込→ODBCデータベースを選択
リンクテーブルを作成してソースデータにリンクするを選択
コンピュータデータソースのタブを選択
先ほど作成したデータソース名をクリック
パスワードを入力して、リンクしたいテーブルを選択する

☆ リンクテーブルで全角文字などが「?」のように文字化けした場合
SQLPLUSで以下を確認する
SQL> select parameter ,value from nls_database_parameters where parameter like  '%CHARACTERSET';
  
コマンドプロンプトから「regedit」起動
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEx の NLS_LANGを確認する

マイコンピュータのプロパティから
設定の変更→詳細設定→環境変数→ユーザー環境変数
NLS_LANGを新規登録し、値は先ほどの2カ所と一致している値をセットする
(例: JAPANESE_JAPAN.JA16SJISTILDE)

リンクテーブルを開きなおすと全角文字が正しく表示される

2011年4月4日月曜日

ブログ開始

今日からブログを開始しました
とりあえずメモ代わりに使おう