メモ:コネクションプールを使ってる時の SqlConnection を Close した時の内部動作

GitHub 上でコード追っかけたのでメモ
※実行してトレースしたわけでは無いので間違ってるかも

生きてる SqlTransaction があっても、Rollback 呼んでそう。

SqlConnection.Close() で SqlConnection.CloseInnerConnection()
https://github.com/dotnet/corefx/blob/v2.2.3/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlConnection.cs#L683

SqlConnection.CloseInnerConnection() で InnerConnection.CloseConnection()
https://github.com/dotnet/corefx/blob/v2.2.3/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlConnection.cs#L637

SqlClient.InnerConnection の定義
https://github.com/dotnet/corefx/blob/v2.2.3/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlConnectionHelper.cs#L92

DbConnectionInternal.CloseConnection() で connectionPool.PutObject()
https://github.com/dotnet/corefx/blob/v2.2.3/src/System.Data.SqlClient/src/System/Data/ProviderBase/DbConnectionInternal.cs#L270

DbConnectionPool.PutObject() で DeactivateObject()
https://github.com/dotnet/corefx/blob/v2.2.3/src/System.Data.SqlClient/src/System/Data/ProviderBase/DbConnectionPool.cs#L1454

DbConnectionPool.DeactiveObject() で DbConnectionInternal.DeactivateConnection()
https://github.com/dotnet/corefx/blob/v2.2.3/src/System.Data.SqlClient/src/System/Data/ProviderBase/DbConnectionPool.cs#L762

DbConnectionInternal.DeactiveConnection() で Deactivate()
https://github.com/dotnet/corefx/blob/v2.2.3/src/Common/src/System/Data/ProviderBase/DbConnectionInternal.cs#L241

SqlInternalConnection.Deactivate() で InternalDeactivate()
https://github.com/dotnet/corefx/blob/v2.2.3/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlInternalConnection.cs#L262

SqlinternalConnectionTds.InternalDeactivate() で _parse.Deactivate()
https://github.com/dotnet/corefx/blob/v2.2.3/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlInternalConnectionTds.cs#L768

TdsParser.Deactivate() で currentTransaction.CloseFromConnection()
https://github.com/dotnet/corefx/blob/v2.2.3/src/System.Data.SqlClient/src/System/Data/SqlClient/TdsParser.cs#L924

SqlInternalTransaction.CloseFromConnection() で innerConnection.ExecuteTransaction()
https://github.com/dotnet/corefx/blob/v2.2.3/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlInternalTransaction.cs#L253

SqlInternalConnectionTds.ExecuteTransaction() で ExecuteTransactionYukon()
https://github.com/dotnet/corefx/blob/v2.2.3/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlInternalConnectionTds.cs#L857

SqlInternalConnectionTds.ExecuteTransactionYukon() で _parser.TdsExecuteTransactionManagerRequest() https://github.com/dotnet/corefx/blob/v2.2.3/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlInternalConnectionTds.cs#L986

TdsParser.TdsExecuteTransactionManagerRequest https://github.com/dotnet/corefx/blob/v2.2.3/src/System.Data.SqlClient/src/System/Data/SqlClient/TdsParser.cs#L6608

名前に Yukon とか出てきたけど、2005 のコードネームやっけ。

メモ:SQL Server 2019 Hybrid Buffer Pool

2019 の新機能見てて何やろ~?って思ったのでメモ。
Hybrid Buffer Pool - SQL Server | Microsoft Docs
2019 CTP2.1 から。

PMEM に データベース ファイル を置いてると、バッファープール に読み込まずに直接読むっぽい。
直接読むのは クリーン ページ だけ、ダーティページは今まで通りの動き。
ダーティページって何や~?ってサクッと書くと、変更があったけど未だ物理に書き込まれていない状態のページのこと。

NVDIMM-N 対応は、2016 SP1 で入った ログファイル を NVDIMM に置いてログバッファに使う Tail of Log ってのがあったけど、
Transaction Commit latency acceleration using Storage Class Memory in Windows Server 2016/SQL Server 2016 SP1 | SQL Database Engine Blog
Configuring Storage Spaces with a NVDIMM-N write-back cache - SQL Server | Microsoft Docs
今回のでデータファイルも PMEM に置いてパフォーマンス向上するって感じなんですかね。

環境無いので試せてないからアレ。

メモ:create table で filtered な unique index を作る

ドキュメントの構文に載ってないけどなんか出来たのでメモ。

SQL Server での unique 制約は unique index で実現されてます。
なので、unique 制約を作る = unique index を作る です。

UNIQUE 制約を作る場合
Create Unique Constraints - SQL Server | Microsoft Docs

後から UNIQUE INDEX 作る場合
Create Unique Indexes - SQL Server | Microsoft Docs

filtered ってなんやーですが、SQL Server では index を作る時に条件指定して index を作ることが出来ます。
Create Filtered Indexes - SQL Server | Microsoft Docs

特定の状態のデータに対して index を作りたい場合に使う感じ。
これを利用すると、複数行に null が含む列に対して unique 制約を付けることも出来ます。*1

で、create table の時にまとめて作るのはドキュメントでは出来なさそうだけど、何か書けたのでメモ。
CREATE TABLE (Transact-SQL) - SQL Server | Microsoft Docs

ドキュメントに無い構文だけど、エラーが出なかったクエリ例

create table [サンプル] (
  [有効なデータでは一意なId] bigint not null, 
  [状態] nchar(2) not null,
  index [UX_サンプル_Id] unique ([有効なデータでは一意なId]) where [状態] = N'有効'
)

ドキュメントでは

< table_constraint > ::=  
[ CONSTRAINT constraint_name ]   
{   
    { PRIMARY KEY | UNIQUE }   
        [ CLUSTERED | NONCLUSTERED ]   
        (column [ ASC | DESC ] [ ,...n ] )   
        [   
            WITH FILLFACTOR = fillfactor   
           |WITH ( <index_option> [ , ...n ] )   
        ]  
        [ ON { partition_scheme_name (partition_column_name)  
            | filegroup | "default" } ]   
    | FOREIGN KEY   
        ( column [ ,...n ] )   
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]   
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]   
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]   
        [ NOT FOR REPLICATION ]   
    | CHECK [ NOT FOR REPLICATION ] ( logical_expression )  

< table_index > ::=   
{  
    {  
      INDEX index_name [ CLUSTERED | NONCLUSTERED ]   
         (column_name [ ASC | DESC ] [ ,... n ] )   
    | INDEX index_name CLUSTERED COLUMNSTORE  
    | INDEX index_name [ NONCLUSTERED ] COLUMNSTORE (column_name [ ,... n ] )  
    }  
    [ WITH ( <index_option> [ ,... n ] ) ]   
    [ ON { partition_scheme_name (column_name )   
         | filegroup_name  
         | default   
         }  
    ]   
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]  

}  

ってなってるので、constraint で unique 付ける場合に where は指定出来ないとなってるし、
index の方では、unique 指定出来ないし where もダメってなってる。

普通に unique index を作る場合は、

create unique index [UX_サンプル_Id] on [サンプル] ([有効なデータでは一意なId]) where [状態] = N'有効'

って構文なので、unique の位置が変わるのは注意点?

ただ、ドキュメントに載ってないのでどのバージョンで使えるのかは動かしてみないと分からない。。
とりあえず SQL Server 2016、SQL Database v12 では動いてる。
SQL DB v12 で動いてるから、新しい SQL Server なら問題なさそう。

*1:not null を条件に付ける

02/19(火) SQLWorld★大阪#49 開催します #sqlworld

2019年最初の SqlWorld :: SQLWorld★大阪#49 開催します! 31回目の平日夜開催で、前回同様 ハンズオン 形式行う予定です。

【日時】
2019年02月19日(火曜日) 19:00~21:00
 

【イベント概要】
2019年最初の SQLWorld、今回で31回目の平日夜開催~。今回も、みんなで SQL を書いてみようというハンズオン企画です!ブラウザがあれば参加出来るようにしていますので、iPad 等のタブレットでも大丈夫です。
 

【会場】
フェンリル株式会社さま大阪本社 http://www.fenrir-inc.com/
〒530-0011 大阪府大阪市北区大深町 3番1号 グランフロント大阪タワーB(オフィス)
 

【参加費】
無料
 

【持ち物】
パソコン/タブレット (DB のインストールは不要です。)
 

【参加可能人数】
13 人
 

お題に沿って、SQL を書いてみようという勉強会です。是非ご参加を~。
今回は初心者向けの優しめの問題を考え中です。

開催回数は増えていっていますが、続き物というわけでは無いので初めて参加される方でもお気軽にどぞー。

複合主キーを代理キー(サロゲートキー)に変えると、外部キー制約が弱くなる時がある

外部キー制約が弱くなるってなんやって話しですが、分かりやすいように例を交えて

取引先と取引先組織が親子関係、注文には2つの組織(請求先組織, 出荷先組織)を持つとした場合に、

取引先組織を複合主キーのまま定義するとこんな感じ。

create table [取引先] (
  [取引先Id] bigint not null primary key,
  [名称] nvarchar(100) not null
)
create table [取引先組織] (
  [取引先Id] bigint not null foreign key references [取引先] ([取引先Id]),
  [連番] bigint not null,
  [名称] nvarchar(100) not null,
  [住所] nvarchar(300) not null
  primary key ([取引先Id], [連番])
)
create table [注文] (
  [注文Id] bigint not null primary key,
  [取引先Id] bigint not null foreign key references [取引先] ([取引先Id]),
  [請求先組織連番] bigint not null,
  [出荷先組織連番] bigint not null,
  foreign key ([取引先Id], [請求先組織連番]) references [取引先組織] ([取引先Id], [連番]),
  foreign key ([取引先Id], [出荷先組織連番]) references [取引先組織] ([取引先Id], [連番])
)

これを複合主キーをやめて代理キーにするとこんな感じ。

create table [取引先] (
  [取引先Id] bigint not null primary key,
  [名称] nvarchar(100) not null
)
create table [取引先組織_サロゲートキー] (
  [取引先組織Id] bigint not null primary key,
  [取引先Id] bigint not null foreign key references [取引先] ([取引先Id]),
  [名称] nvarchar(100) not null,
  [住所] nvarchar(300) not null
)
create table [注文_サロゲートキー] (
  [注文Id] bigint not null primary key,
  [取引先Id] bigint not null foreign key references [取引先] ([取引先Id]),
  [請求先組織Id] bigint not null foreign key references [取引先組織_サロゲートキー] ([取引先組織Id]),
  [出荷先組織Id] bigint not null foreign key references [取引先組織_サロゲートキー] ([取引先組織Id])
)

一見代理キーの方がスッキリして見えるんだけど、注文の2組織が、「取引先に属している組織」かどうかが DB の制約で担保出来なくなってる。
複合主キーの場合は、外部キー制約の中に 取引先Id を含んでいるので、2組織とも取引先に属している組織って担保出来てる。

複合主キーだと in 句で指定出来ないとかデメリットもあるけど、制約で整合性を担保出来る範囲が広くなるケースもあるのでケースバイケースなんすかねー。

Visual Studio の Database Project で Temporal Table 使ってると公開が失敗することがある

列の型を変えたり、Temporal Table が要らなくなって、定義から消した場合*1にやられる。。
Cannot change data types of a temporal table columns - Developer Community

回避方法は ↑ に書いてある通り、*.dbmdl 消したり、↓ のような alter で Temporal Table を止めたりしないとダメ。

alter table [HogeTable] set (system_versioning = off)

列の型変えるでエラーを防ごうとした場合、History テーブルもちゃんと CREATE 文で作ってやったらよさそう。

失敗する例:History テーブルを自動で生成しているため、列の型を変更すると失敗する

CREATE TABLE [dbo].[Table1]
(
    [Id] INT NOT NULL PRIMARY KEY,
    [Name] NVARCHAR(20) NOT NULL,
    [SysStart] DATETIME2 (7) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEnd] DATETIME2 (7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE=[dbo].[Table1_HISTORY], DATA_CONSISTENCY_CHECK=ON))

成功する例:History テーブルを手動で生成しているので、メイン、ヒストリーテーブル両方の列の型を変更すると成功する

create table [Table1History] (
    [Id] INT NOT NULL PRIMARY KEY,
    [Name] NVARCHAR(20) NOT NULL,
    [SysStart] DATETIME2 (7) NOT NULL,
    [SysEnd] DATETIME2 (7) NOT NULL
)
GO
CREATE CLUSTERED INDEX [IX_Table1History] ON [Table1History] ([SysEnd], [SysStart])
GO
create table [Table1] (
    [Id] INT NOT NULL PRIMARY KEY,
    [Name] NVARCHAR(20) NOT NULL,
    [SysStart] DATETIME2 (7) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEnd] DATETIME2 (7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
) WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE=[dbo].[Table1History], DATA_CONSISTENCY_CHECK=ON))
GO

なんでか分からないけど、alter 文で system_versioning の設定しようとするとコンパイルエラーになるので、create 時に設定する必要あり。

Temporal Table 周りはまだ色々動き怪しい。。

定義から消したときに、削除するやつは Database Project の中での対処方法は無さそう。
手動 alter で system_versioning = off してください。

*1:ソースになくて、ターゲットにある場合に削除する

メモ:存在しない行を HOLDLOCK すると、Range でロックする

たまたま試す機会があったのでメモ。

テーブル ヒント (Transact-SQL) - SQL Server | Microsoft Docs
には HOLDLOCK は SERIALIZABLE 相当と書かれていて、
SERIALIZABLE では、

SERIALIZABLE 分離レベルで実行しているトランザクションと同じセマンティクスで、スキャンが実行されます。

となっているので、ドキュメント通りなんだけど試したから書いとく。

例1:
存在しない行に UPDLOCK を掛けても、ロックは取られない。
なので、他のトランザクションで行を追加されてしまう。

ロック掛けるセッション

begin tran

select * from [Table] (UPDLOCK) where Id = 6

f:id:odashinsuke:20190118135407j:plain
追加するセッション

begin tran
insert into [Table] values (6, 'abcabc')
select * from [Table]
rollback

f:id:odashinsuke:20190118135431j:plain
Id = 6 が追加される。

例2:
HOLDLOCK にすると、行を追加されない。

ロック掛けるセッション

begin tran

select * from [Table] (HOLDLOCK) where Id = 6

f:id:odashinsuke:20190118140337j:plain 追加するセッション

begin tran
insert into [Table] values (6, 'abcabc')
select * from [Table]
rollback

f:id:odashinsuke:20190118140401j:plain
ロックが解放されるまで追加できない。

ロック状況 f:id:odashinsuke:20190118140438j:plain

範囲でロックを取っているので、今回の場合は Id = 6 だけではなく、6 以上の値では insert 出来ない。
逆に Id = 0 や -1 は insert 可能。

Id が 1 と 5 のデータしかない場合に、Id = 3 で HOLDLOCK 掛けたら、1 より小さい値や、5 より大きい値はロックの範囲外。
2, 3, 4 はロックの範囲内。
多分こんな認識で大丈夫のはず。