DELETE

アトミック性

既定では、DELETE、UPDATE、INSERT、および TRUNCATE TABLE はアトミック処理として実行されます。DELETE は、正常に完了するか、すべての操作がロールバックされるかのいずれかです。指定した行のいずれかを削除できない場合、指定した行は 1 行も削除できずにデータベースは DELETE を発行する前の状態に戻ります。

現在のプロセスに対するこの既定は、SET TRANSACTION %COMMITMODE を呼び出すことによって SQL 内で変更できます。現在のプロセスに対するこの既定は、SetAutoCommit()Opens in a new tab メソッドを呼び出すことによって ObjectScript 内で変更できます。以下のオプションを使用できます。

• IMPLICIT または 1 (自動コミットがオン) — 上記のように、これが既定の動作です。DELETE ごとに個別のトランザクションが構成されます。

• EXPLICIT または 2 (自動コミットがオフ) — 進行中のトランザクションがない場合は、DELETE コマンドによってトランザクションは自動的に開始されます。ただし、COMMIT または ROLLBACK で明示的にトランザクションを終了する必要があります。EXPLICIT モードでは、トランザクションあたりのデータベース操作の数は、ユーザ定義です。

• NONE または 0 (トランザクションなしを自動化しない) — DELETE を呼び出してもトランザクションは開始されません。DELETE 操作の失敗により、指定された行の一部が削除されたり削除されなかったりすることで、データベースが整合性のない状態になる可能性があります。このモードでトランザクションのサポートを提供するには、START TRANSACTION を使用してトランザクションを開始し、COMMIT または ROLLBACK を使用してトランザクションを終了する必要があります。

現在のプロセスのアトミック性設定を確認するには、以下の ObjectScript の例のように、GetAutoCommit()Opens in a new tab メソッドを使用します。

  DO $SYSTEM.SQL.SetAutoCommit($RANDOM(3))
  SET x=$SYSTEM.SQL.GetAutoCommit()
  IF x=1 {
    WRITE "Default atomicity behavior",!
    WRITE "automatic commit or rollback" }
  ELSEIF x=0 {
    WRITE "No transaction initiated, no atomicity:",!
    WRITE "failed DELETE can leave database inconsistent",!
    WRITE "rollback is not supported" }
  ELSE { WRITE "Explicit commit or rollback required" }

FROM 構文

DELETE コマンドには、テーブルを指定する 2 つの FROM キーワードを含めることができます。この 2 つの FROM の使用法は根本的に異なります。

• table-ref の前の FROM で、1 つ以上の行を削除するテーブル (またはビュー) を指定します。これは FROM キーワードであり、FROM 節ではありません。テーブルは 1 つのみ指定できます。結合構文または optimize-option キーワードを指定することはできません。FROM キーワード自体はオプションで、table-ref は必須です。

• table-ref の後の FROM はオプションの FROM 節で、削除する行を特定するのに使用できます。1 つ以上のテーブルを指定できます。結合構文や optimize-option キーワードを含め、SELECT 文に使用できるすべての FROM 節の構文がサポートされます。この FROM 節は、(常にではありませんが) 通常は WHERE 節と共に使用します。

したがって、以下のいずれも有効な構文形式になります。

DELETE FROM table WHERE ...
DELETE table WHERE ...
DELETE FROM table FROM table2 WHERE ...
DELETE table FROM table2 WHERE ...

この構文では、Transact-SQL と互換性のある方法で複雑な選択条件がサポートされます。

以下の例は、2 つの FROM キーワードを使用する方法を示しています。ここでは、Retirees テーブル内に同じ EmpId がある場合、それらのレコードが Employees テーブルから削除されます。

DELETE FROM Employees AS Emp
       FROM Retirees AS Rt
       WHERE Emp.EmpId = Rt.EmpId

2 つの FROM キーワードで同じテーブルを参照する場合には、参照するテーブルが文字どおり同じであることも、テーブルの 2 つのインスタンスの結合であることもあります。これは、テーブルのエイリアスの使用方法によって異なります。

• 両方のテーブル参照にエイリアスがない場合には、両方とも同じテーブルを参照します。

    DELETE FROM table1 FROM table1,table2   /* join of 2 tables */
  

  

• 両方のテーブル参照に同じエイリアスがある場合には、両方とも同じテーブルを参照します。

    DELETE FROM table1 AS x FROM table1 AS x,table2   /* join of 2 tables */
  

  

• 両方のテーブル参照にエイリアスがあり、それぞれのエイリアスが異なる場合には、Caché は 2 つのテーブルのインスタンスの結合を実行します。

    DELETE FROM table1 AS x FROM table1 AS y,table2   /* join of 3 tables */
  

  

• 最初のテーブル参照にエイリアスがあり、2 番目にはない場合には、Caché は 2 つのテーブルのインスタンスの結合を実行します。

    DELETE FROM table1 AS x FROM table1,table2   /* join of 3 tables */
  

  

• 最初のテーブル参照にエイリアスがなく、2 番目にはエイリアスのあるテーブルへの単一の参照がある場合には、両方とも同じテーブルを参照し、このテーブルには指定されたエイリアスがあります。

    DELETE FROM table1 FROM table1 AS x,table2   /* join of 2 tables */
  

  

• 最初のテーブル参照にエイリアスがなく、2 番目にはテーブルへの複数の参照がある場合には、それぞれのエイリアスされたインスタンスは個別のテーブルと見なされ、それらのテーブルの結合が実行されます。

    DELETE FROM table1 FROM table1,table1 AS x,table2   /* join of 3 tables */
    DELETE FROM table1 FROM table1 AS x,table1 AS y,table2   /* join of 4 tables */
  
  

  

制限引数

restriction 引数を使用するには、現在のネームスペースに対応する admin-privilege が必要となります。詳細は "GRANT" を参照してください。

restriction 引数を指定すると、以下のように処理を制限します。

• %NOCHECK — 削除される行を参照する外部キーの参照整合性チェックを抑制します。

• %NOLOCK — 削除される行の行のロックを抑制します。単独のユーザ/処理がデータベースを更新する際にのみ使用します。

• %NOINDEX — 削除される行のために全インデックスでのインデックス・エントリの削除を抑制します。テーブル・インデックス内に孤立した値が残るため、これは十分に注意して使用する必要があります。

• %NOTRIGGER — DELETE 処理中にベース・テーブル・トリガの実行を抑制して、トリガが引き出されないようにします。

複数の restriction 引数を順不同で指定できます。複数の引数は、空白で区切られます。

親レコードの削除時に restriction 引数を指定すると、対応する子レコードの削除時に同じ restriction 引数が適用されます。

参照整合性

%NOCHECK を指定しない場合、Caché ではシステム構成設定を使用して外部キーの参照整合性チェックを実行するかどうかが決まります。システムの既定値は以下のように設定できます。

• $SYSTEM.SQL.SetFilerRefIntegrity()Opens in a new tab メソッド呼び出し。

• 管理ポータルに進み、システム, 構成, 一般SQL設定 を選択します。[INSERT、UPDATE、DALETE の外部キーについて参照整合性チェックを実行する] の現在の設定を表示して編集します。既定は “はい” です。この設定を変更すると、変更後に開始される新しいプロセスは、新しい設定になります。

DELETE 操作時には、すべての外部キー参照について、参照されるテーブルの該当する行に対する共有ロックが取得されます。この行は、トランザクションの終了までロックされています。これにより、参照される行は、DELETE のロールバックがあってもそれより前に変更されることがなくなります。

一連の外部キー参照が CASCADE として定義されている場合は、DELETE 操作により、循環参照が発生する可能性があります。Caché では、CASCADE 参照動作のある DELETE による循環参照ループの反復を防止します。元のテーブルに戻ると、Caché は、カスケード・シーケンスを終了します。

CASCADE、SET NULL、または SET DEFAULT で定義された外部キー・フィールドに対して、%NOLOCK を指定して DELETE 操作を実行した場合は、対応する参照アクションが %NOLOCK によって実行されて外部キー・テーブルが変更されます。

トランザクションでのロック

%NOLOCK を指定しない場合、INSERT、UPDATE、および DELETE 操作時に自動的にレコードに標準のロックがかかります。影響を受ける各レコード (行) は、現在のトランザクションが継続している間はロックされます。

既定のロックしきい値は、テーブルごとに 1000 ロックです。つまり、トランザクションの間にテーブルから 1000 件を超えるレコードを削除すると、ロックのしきい値に到達し、Caché は自動的にロック・レベルをレコード・ロックからテーブル・ロックに上げます。これによってトランザクション時に、ロック・テーブルをオーバーフローすることなく、大規模な削除を実行できます。

Caché は、以下の 2 つのロック・エスカレーション策のどちらかを適用します。

• “E” タイプのロック・エスカレーション：Caché は、次のことに該当する場合にこのタイプのロック・エスカレーションを使用します。(1) クラスで %CacheStorage が使用されている (これは、管理ポータルの SQL スキーマ表示で [カタログの詳細] から判断できます)。(2) クラスで、IDKey インデックスが指定されていないか、単一プロパティの IDKey インデックスが指定されている。“E” タイプのロック・エスカレーションについては、"Caché ObjectScript リファレンス" の LOCK コマンドで説明されています。

• 従来の SQL ロック・エスカレーション：クラスで “E” タイプのロック・エスカレーションが使用されない理由は、マルチプロパティの IDKey インデックスの存在にあると考えられます。この場合は、%Save ごとにロック・カウンタがインクリメントされます。つまり、トランザクション内の単一オブジェクトを 1001 回保存を行うと、Caché はロックのエスカレーションを試みます。

どちらのロック・エスカレーション策の場合も、$SYSTEM.SQL.GetLockThreshold()Opens in a new tab メソッドを使用して、現在のシステム全体用ロックしきい値を決定できます。既定は 1000 です。このシステム全体のロックしきい値は、以下の方法を使用して設定できます。

• $SYSTEM.SQL.SetLockThreshold()Opens in a new tab メソッドを使用します。

• 管理ポータルを使用する。システム, 構成, 一般SQL設定 に移動します。[ロックしきい値] の現在の設定を表示して編集します。既定は 1000 ロックです。この設定を変更すると、変更後に開始される新しいプロセスは、新しい設定になります。

ロックしきい値を変更するには、%Admin Manage Resource の USE 許可が必要です。Caché は、ロックしきい値の変更を現在のプロセスすべてに即座に適用します。

結果として、自動ロック・エスカレーションでは、デッドロックの状況が起こる可能性があります。つまり、テーブル・ロックへのエスカレーションを試みたときに、テーブル内のレコード・ロックを保持する別プロセスとの競合が起こる可能性があります。これを避けるための方策としては、次のいくつかが考えられます。 (1) ロック・エスカレーションがトランザクション内で起こる可能性が低くなるように、ロック・エスカレーションのしきい値を上げる。(2) ロック・エスカレーションが即座に起こるように、ロック・エスカレーションのしきい値を大幅に下げる。これにより、別プロセスが同一テーブル内のレコードをロックする機会が少なくなります。(3) トランザクションが継続している間はテーブル・ロックを適用し、レコード・ロックは実行しない。これは、LOCK TABLE、UNLOCK TABLE (テーブル・ロックがトランザクションの終了まで持続するよう、IMMEDIATE キーワードはなし) の順に指定することで、トランザクション開始時に実行できます。その後、%NOLOCK オプションを使用して削除を実行します。

自動ロック・エスカレーションは、ロック・テーブルのオーバーフローを防ぐことを目的としています。ただし、大量の削除などを実行したために <LOCKTABLEFULL> エラーが発生した場合は、DELETE によって SQLCODE -110 エラーが発行されます。

トランザクションでのロックの詳細は、"Caché SQL の使用法" の “データベースの変更” の章にある "トランザクション処理" を参照してください。

埋め込み SQL とダイナミック SQL の例

以下に示す一連のプログラム例のうち、最初のプログラムでは、3 つの列を持つ SQLUser.WordPairs という名前のテーブルを作成します。その次のプログラムではテーブルに 6 つのレコードを挿入します。以降のプログラムでは、カーソル・ベースの埋め込み SQL を使用してすべての English レコードを削除し、ダイナミック SQL を使用してすべての French レコードを削除します。最後のプログラムでは、残りのレコードを表示し、その後テーブルを削除します。

CreateTable
   ZNSPACE "Samples"
   &sql(CREATE TABLE SQLUser.WordPairs (
        Lang        CHAR(2) NOT NULL,
        Firstword   CHAR(30),
        Lastword    CHAR(30) )
       )
  IF SQLCODE=0 {
    WRITE !,"Table created" }
  ELSEIF SQLCODE=-201 {WRITE !,"Table already exists"  QUIT}
  ELSE {
    WRITE !,"CREATE TABLE failed. SQLCODE=",SQLCODE }

InsertSixRecords
  #SQLCompile Path=Cinema,Sample
  ZNSPACE "Samples"
  &sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
   ('En','hello','goodbye'))
  IF SQLCODE = 0 { WRITE !,"1st record inserted" }
  ELSE { WRITE !,"Insert failed, SQLCODE=",SQLCODE
         QUIT}
  &sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
   ('Fr','bonjour','au revoir'))
  IF SQLCODE = 0 { WRITE !,"2nd record inserted" }
  ELSE { WRITE !,"Insert failed, SQLCODE=",SQLCODE  QUIT}
  &sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
   ('It','pronto','ciao'))
  IF SQLCODE = 0 { WRITE !,"3rd record inserted" }
  ELSE { WRITE !,"Insert failed, SQLCODE=",SQLCODE  QUIT}
  &sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
   ('Fr','oui','non'))
  IF SQLCODE = 0 { WRITE !,"4th record inserted" }
  ELSE { WRITE !,"Insert failed, SQLCODE=",SQLCODE  QUIT}
  &sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
   ('En','howdy','see ya'))
  IF SQLCODE = 0 { WRITE !,"5th record inserted" }
  ELSE { WRITE !,"Insert failed, SQLCODE=",SQLCODE  QUIT}
  &sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
   ('Es','hola','adios'))
  IF SQLCODE = 0 { WRITE !,"6th record inserted",!!
     SET myquery = "SELECT %ID,* FROM SQLUser.WordPairs"
     SET tStatement = ##class(%SQL.Statement).%New()
     SET qStatus = tStatement.%Prepare(myquery)
       IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
     SET rset = tStatement.%Execute()
     DO rset.%Display()
     WRITE !,"End of data" }
  ELSE { WRITE !,"Insert failed, SQLCODE=",SQLCODE }

EmbeddedSQLDeleteEnglish
  #SQLCompile Path=Sample
  NEW SQLCODE,%ROWCOUNT,%ROWID
  &sql(DECLARE WPCursor CURSOR FOR 
        SELECT Lang FROM WordPairs
        WHERE Lang='En')
   &sql(OPEN WPCursor)
   FOR { &sql(FETCH WPCursor)
        QUIT:SQLCODE 
        &sql(DELETE FROM WordPairs
       WHERE CURRENT OF WPCursor)
    IF SQLCODE=0 {
    WRITE !,"Delete succeeded"
    WRITE !,"Row count=",%ROWCOUNT," RowID=",%ROWID }
    ELSE {
    WRITE !,"Delete failed, SQLCODE=",SQLCODE }
    }
    &sql(CLOSE WPCursor)

DynamicSQLDeleteFrench
  SET sqltext = "DELETE FROM WordPairs WHERE Lang=?"
  SET tStatement = ##class(%SQL.Statement).%New(0,"Sample")
  SET qStatus = tStatement.%Prepare(sqltext)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rtn = tStatement.%Execute("Fr")
  IF rtn.%SQLCODE=0 {
    WRITE !,"Delete succeeded"
    WRITE !,"Row count=",rtn.%ROWCOUNT," RowID of last record=",rtn.%ROWID }
  ELSE {
    WRITE !,"Delete failed, SQLCODE=",rtn.%SQLCODE }

DisplayAndDeleteTable
  ZNSPACE "Samples"
  SET myquery = "SELECT %ID,* FROM SQLUser.WordPairs"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data"
  &sql(DROP TABLE SQLUser.WordPairs)
   IF SQLCODE=0 {
    WRITE !!,"Table deleted"
    QUIT }
  ELSE {
    WRITE !,"Table delete failed, SQLCODE=",SQLCODE }