access2010 SQL Azureいってみようか -13-

同時実行制御ってことを考えてみた。

楽観的同時実行制御ってこと。ODBCリンクテーブルで使えるなら、まぁなくてもいい話

CREATE TABLE dbo.table01
(
    ID  int IDENTITY NOT NULL,
    F01 nvarchar(20) COLLATE Japanese_CI_AS NOT NULL,
    TS  timestamp NOT NULL,
    CONSTRAINT PK_table01 PRIMARY KEY CLUSTERED 
    (
        ID ASC
    )
)
GO

CREATE UNIQUE NONCLUSTERED INDEX idx_F01 ON dbo.table01 
(
    F01
)
GO

さらっと作ったテーブルのフィールドは一意であることにしてみた

まずは、ストアドプロシージャを作成。

CREATE PROCEDURE dbo.proc_table01test01
    @ID  int, 
    @F01 nvarchar(20),
    @TS  timestamp
AS
BEGIN
    SET NOCOUNT ON;
    
    UPDATE dbo.table01 SET F01 = @F01
    WHERE ID = @ID AND TS = @TS
END

timestampの値がローカルの値と同じでればレコードは更新されていないので、F01フィールドは更新できる。
で、実行してみる。

Sub test()
On Error GoTo ErrHnd
    Dim dbs As DAO.Database, qdf As DAO.QueryDef
    Set dbs = CurrentDb
    Set qdf = dbs.CreateQueryDef("")
    qdf.Connect = strCn
    qdf.SQL = "exec proc_table01test01 1,N'テスト01', 0x000000000001DB63"
    qdf.ReturnsRecords = False
    qdf.Execute

Done:
    
    Exit Sub
ErrHnd:
    If Err.Number = 3146 Then
        Dim oErr As Error, msg As String
        For Each oErr In DBEngine.Errors
            msg = msg & oErr.Number & vbCrLf & _
                        oErr.Source & vbCrLf & _
                        oErr.Description & vbCrLf
        Next
        MsgBox msg
    Else
        MsgBox Err.Number & "/" & Error
    End If
    Resume Done
End Sub

実行結果がわからないのは当たり前。ストアドプロシージャに手を入れてみる。

CREATE PROCEDURE dbo.proc_table01test02
    @ID  int, 
    @F01 nvarchar(20),
    @TS  timestamp
AS
BEGIN
    SET NOCOUNT ON;
    
    UPDATE dbo.table01 SET F01 = @F01
    WHERE ID = @ID AND TS = @TS
    SELECT @@ROWCOUNT
END

@@ROWCOUNT (Transact-SQL)を使って、更新された行数を確認できるようにしてみた。そして、実行。ちょっとコードは変わる。

Sub test()
On Error GoTo ErrHnd
    Dim dbs As DAO.Database, qdf As DAO.QueryDef, rs As DAO.Recordset
    Set dbs = CurrentDb
    Set qdf = dbs.CreateQueryDef("")
    qdf.Connect = strCn
    qdf.SQL = "exec proc_table01test02 1,N'テスト01', 0x000000000001DB63"
    Set rs = qdf.OpenRecordset
    If rs(0) = 1 Then
        MsgBox "更新された。"
    Else
        MsgBox "更新できなかった"
    End If
Done:
    
    Exit Sub
ErrHnd:
    If Err.Number = 3146 Then
        Dim oErr As Error, msg As String
        For Each oErr In DBEngine.Errors
            msg = msg & oErr.Number & vbCrLf & _
                        oErr.Source & vbCrLf & _
                        oErr.Description & vbCrLf
        Next
        MsgBox msg
    Else
        MsgBox Err.Number & "/" & Error
    End If
    Resume Done
End Sub

なんとか結果は判りそうです。

一意であるフィールドに重複する値で更新しようとするとこんな感じに。
重大度10の3621/ステートメントは終了されました。と、重大度14の2601/一意インデックス '%.*ls' を含むオブジェクト '%.*ls' には重複するキー行を挿入できません。が出ている。accessの中の人にお仕事をお願いできそう。

RAISERROR (Transact-SQL)を使ってみる。更新できなかったときエラーとして検知できる。

CREATE PROCEDURE dbo.proc_table01test03
    @ID  int, 
    @F01 nvarchar(20),
    @TS  timestamp
AS
BEGIN
    SET NOCOUNT ON;
    
    UPDATE dbo.table01 SET F01 = @F01
    WHERE ID = @ID AND TS = @TS
    IF @@ROWCOUNT = 0
        RAISERROR(N'更新できませんでした。',
                  11,
                  1)
END

Sub test()
On Error GoTo ErrHnd
    Dim dbs As DAO.Database, qdf As DAO.QueryDef, rs As DAO.Recordset
    Set dbs = CurrentDb
    Set qdf = dbs.CreateQueryDef("")
    qdf.Connect = strCn
    qdf.SQL = "exec proc_table01test03 1,N'テスト01', 0x000000000001DB98"
    qdf.ReturnsRecords = False
    qdf.Execute
Done:
    
    Exit Sub
ErrHnd:
    If Err.Number = 3146 Then
        Dim oErr As Error, msg As String
        For Each oErr In DBEngine.Errors
            msg = msg & oErr.Number & vbCrLf & _
                        oErr.Source & vbCrLf & _
                        oErr.Description & vbCrLf
        Next
        MsgBox msg
    Else
        MsgBox Err.Number & "/" & Error
    End If
    Resume Done
End Sub

更新できたときは静か。更新できなかった場合はこんな感じに。ちょっといいかなと思ったけど、

こういうこともあるので、もうちょっと整理しようか。

こういうのもありかな

CREATE PROCEDURE dbo.proc_table01test04
    @ID  int, 
    @F01 nvarchar(20),
    @TS  timestamp
AS
BEGIN
    SET NOCOUNT ON;
    
    UPDATE dbo.table01 SET F01 = @F01
    WHERE ID = @ID AND TS = @TS
    IF @@ROWCOUNT = 0
        BEGIN
            DECLARE @msg nvarchar(100) = N'更新できませんでした'
            IF (SELECT COUNT(*) FROM dbo.table01 WHERE ID = @ID) = 0
                BEGIN
                    SET @msg = N'レコードがありません'
                END
            RAISERROR(@msg,
                      11,
                      1)
        END
END

TRY...CATCH (Transact-SQL)を使ってもうちょっと整理してみる。

CREATE PROCEDURE dbo.proc_table01test05
    @ID  int, 
    @F01 nvarchar(20),
    @TS  timestamp
AS
BEGIN TRY
    DECLARE @msg nvarchar(100)
    SET NOCOUNT ON;
    
    UPDATE dbo.table01 SET F01 = @F01
    WHERE ID = @ID AND TS = @TS;
    IF @@ROWCOUNT = 0
        BEGIN
            IF (SELECT COUNT(*) FROM dbo.table01 WHERE ID = @ID)=1
                    SET @msg = N'更新済みでした'
            ELSE
                BEGIN
                    SET @msg = N'レコードがありません'
                END
            RAISERROR(@msg,11,1)
        END
END TRY
BEGIN CATCH   
    DECLARE @Err_msg nvarchar(100) = CAST(ERROR_NUMBER() AS nvarchar(10))
                                   + N'/'
                                   + ERROR_MESSAGE() , 
            @Err_Severity int = ERROR_SEVERITY(),
            @Err_state int = ERROR_STATE()
    
    RAISERROR(@Err_msg,
              @Err_Severity,
              @Err_state)
END CATCH

もういい加減にしないと頭が沸騰しそうだ。実行してみる。

だいぶすっきりした。中の人に仕事を頼みやすくなった気がする。
これぐらいにしとこうか。ピットフォールがないか確認してない。