データベース Server

【SqlServer】AccessVBAからストアド実行!ODBC更新失敗やらフリーズする!?

ODBC・・・リンクテーブルでの更新に失敗

マイクロソフトデータベースアクセス(以下Access)とSqlServerにてシステム構築したのですが、Accessから更新すると、ODBC・・・リンクテーブルでの更新に失敗となり結局、ストアドプロシージャ(以下ストアド)にてSqlServer側で更新処理をすることにしました。

【SqlServer】AccessVBAからストアド実行させてもデータ変わらない!?ODBC更新失敗

 

もう、ストアドにすれば問題ないだろ!って思っていましたが、今度はAccessがフリーズしてしまう現象になってしまいました。

エラーにでもなってヒントをもらえるならまだ良しとしますが、フリーズしてプロセスを切らない限り、ずーーとフリーズしていました。

本当に困ったソフトです。

 

ストアドの実行方法に問題!?(関係なし)

VBAからストアドを実行する方法を使っていましたが、パススルークエリから簡単にストアドを実行できるというので試しにやってみました。

【SqlServer】ストアドプロシージャを使って初めての処理

 

 

ストアドを実行するには、VBAしか方法がないと思っていましたので目から鱗でしたが、やっぱりフリーズしました。

ストアドの実行方法による原因ではないようです。

 

VPN接続の問題(未テスト)

今回のシステムでの最大の問題は、更新すると、ODBCが切れたり、Accessがフリーズする事です。

しかし、実際に運用使用している方の環境では、ODBCが切れる現象は出ても、Accessがフリーズする現象は出てないようです。

●さくらのクラウド(SqlServer)==サイト間VPN==VPNルーター(実際に運用環境)

自分が試している環境が、

●VPNルーター==IPsecリモート接続==自分のPC

という環境でテストをしています。

 

もしかすると、VPNルーター環境での接続なら問題なくAccessの更新はできるのか!?ということをテストしたいのですが、やれるタイミングが無くてやっていません。

ただ、SqlServerへのリモートディスクトップ接続には何もストレスはありませんので、そこそこの回線スピードは出ているのかと推測します。

 

ストアド実行時のフリーズ原因(行ロック)

ストアドの更新(UPDATE)コードの部分をコメントアウトして実行すれば、もちろんちゃんと動いてくれます。

ストアド実行時のAccessでのフリーズは、更新が原因なのは推測できます。

更に、SqlServerからもストアドの実行すると、毎回では無いもののクエリをキャンセルしない限り実行している表示から変わらない状態になりました。

さらに詳しく

トランザクション内でUPDATE後に処理待ちになる

というQ&Aの記事を見つけました。

SQLServer2005のストアドプロシージャで以下の処理を実行すると
処理待ち状態になり、固まってしまいます。

行ロックが行われていないだけではないでしょうか。
"SQL Server 行ロック"とかでググってみるといろいろ出てくると思いますよ。

 

行ロックって何?

他のトランザクションがテーブルにロックをして解放するまで待ち状態にするような仕組みのようです。

下記のサイトにて実験している記事があるので参考になりますね。

 

行ロック状態を確認できるクエリがありました。

SELECT
     resource_type AS type                       --オブジェクトの種類
    ,resource_associated_entity_id as entity_id  --エンティティID
    ,( CASE WHEN resource_type = 'OBJECT' THEN
          OBJECT_NAME( resource_associated_entity_id )
      ELSE
          ( SELECT
              OBJECT_NAME( OBJECT_ID )
           FROM
             sys.partitions
           WHERE
               hobt_id=resource_associated_entity_id )
      END)
        AS object_name
    ,request_mode                     --ロックの種類
    ,request_type                     --要求の種類
    ,request_status                   --状態
    ,request_session_id AS Session_id --セッションID
        ,(SELECT hostname 
          FROM sys.sysprocesses 
          WHERE spid = request_session_id) AS ProcessName
FROM
    sys.dm_tran_locks
WHERE
    resource_type <> '調べるデータベース名'
ORDER BY
    request_session_id

 

ストアドでフリーズしていた時に、上のクエリをSqlServerで実行すると、沢山の行ロック状態が確認することができました。

今回のケースは、行ロックが原因だったようです。

 

行ロックってどうやって解除するのか?

--UPDATE文実行時の例
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED UPDATE テーブル名 SET 更新内容 WHERE 更新条件

 

UPDATEの前に、「SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED」を付加すれば、ロック中でも参照や更新が可能という記事を見つけました。

自分の場合は、更新がされない状態になってしまいました。

 

いろいろと解説しているサイトがあるのですが、自分の理解力が無くて良く分かりません。

さらに調べてみると、デッドロックと言うらしいという事がわかりました。怖い!

なんとなく理解できそうなサイト見つけました。ただ、回避策は理解できない。

 

ストアドに下記のコードをいれると変化がありました。

SET LOCK_TIMEOUT 0

これは、ロックされているテーブルや行を待つ時間のようです。

0ということは、待たない・・・つまり、スグにロックエラーとなるってことでしょうかね。

このコードをいれたら、フリーズする事がなくなりましたが、更新もできませんでした。

そこで、0じゃなく、10にしたら更新できたりできなかったりになりました。あー辛い。

できなかったりできたりと言うのが一番嫌な結果ですよね。

 

調査は続けますが・・・

SqlServerからのリンクテーブルをAccessVBAで更新かけると、ODBC・・・更新に失敗しましたとなったので、ストアドに変更したら、Accessフリーズ(ストアド実行中状態)となり困ってしまいました。

ストアドで処理した方が早いに決まっているのでストアドにて処理したいと考えていますが、もしかすると、1つのストアド内で、2つのUPDATEしているのが今回の原因か!?って今思いました。

USE [SGDB]
GO
/****** Object:  StoredProcedure [dbo].[order_edit]    Script Date: 2022/09/25 14:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[order_edit]
@eno VARCHAR(10)
AS
BEGIN TRY

--SET LOCK_TIMEOUT 10

    BEGIN TRANSACTION

ここから処理コード

UPDATE
                [order]
SET                             
                更新するフィールド設定

FROM                         [order] INNER JOIN
                                      [order_job] ON [order].NO = [order_job].NO
WHERE                       ([order].NO = @eno)

UPDATE
                [trader_ag]
SET                             
                 更新するフィールド設定

FROM                         [trader_ag] INNER JOIN
                                      order_job ON [trader_ag].od_no = order_job.NO
WHERE                       ([trader_ag].od_no = @eno)


--ここまで処理コード
 
    COMMIT TRANSACTION
 
END TRY
 
BEGIN CATCH
    ROLLBACK TRANSACTION
 
    --RETURN ERROR_NUMBER()
 
END CATCH
 
RETURN 0

上のUPDATEと下のUPDATEで、同じorder_job.NOと言うキーを使っています。

同じトランザクション内で更新処理をしているのが問題なのかは、後ほど実験して報告したいと思います。

 

 

-データベース, Server
-, , , , , , , ,

Translate »

© 2022 PCTips