By setting a database to single-user mode in SQL Server, you can make it accessible to only one user.If there is a session using a database set to single-user mode, any other session attempting to access the database will be put in a state of waiting to acquire an exclusive lock.
Given this behavior, caution is needed when performing operations that acquire locks on the database from the session that set it to single-user mode.
After setting single-user mode, the session holds a shared lock on the database. If, within that session, you attempt to acquire an exclusive lock on the database while another session is already waiting to acquire an exclusive lock, a deadlock may occur, causing one of the operations to fail.
For example, when using the log shipping feature with the secondary in standby mode, this behavior can affect the restore job.
Here is a specific sequence of events:
- The log shipping restore job sets the database to single-user mode before performing the restore. At this time, it acquires an exclusive lock on the database.
- The restore job transitions the exclusive lock on the database to a shared lock.
- Another session attempts to access the database and waits to acquire an exclusive lock.
- The restore job tries to acquire an exclusive lock to perform the restore, but is blocked by the session waiting for the exclusive lock, resulting in a deadlock.
- If the restore job becomes the deadlock victim, it will fail with an error.
Normally, the time between steps 2 and 4 is very short, but in environments where there is frequent access to the database, this issue is more likely to occur. Even if the restore job fails with an error, the next time the job runs, it will also restore the transaction log backups that failed previously. Therefore, it is recommended to check for any impact according to the service level of your system.
(The following content is in Japanese)
SQL Server ではデータベースをシングルユーザーモードに設定することで、そのデータベースを 1 ユーザーでのみアクセス可能な状態にすることが出来ます。シングルユーザーモードに設定したデータベースを使用中のセッションがある場合、それ以外のセッションでそのデータベースにアクセスしようとすると、排他ロックの獲得待ちの状態になります。
この動作を前提として、シングルユーザーモードを設定したセッションでデータベースに対してロックを獲得する操作を行なう場合は注意が必要です。
シングルユーザーモードに設定した後、そのセッションはデータベースに対して共有ロックを保持します。そのセッション内でデータベースへの排他ロックを獲得しようとする処理を実行する際に、既に他のセッションがアクセスしようとして排他ロックの獲得待ちとなっていた場合、デッドロックとなり、いずれかの処理は失敗する場合があります。
例えば、ログ配布機能でセカンダリをスタンバイモードで使用していると、復元ジョブでこの動作の影響を受ける場合があります。
以下に具体的な流れを紹介します。
1) ログ配布の復元ジョブは復元を実行する前にデータベースをシングルユーザーモードに設定します。この時データベースに対して排他ロックを獲得します。
2) 復元ジョブはデータベースに対する排他ロックを共有ロックに移行します。
3) 他のセッションがデータベースにアクセスしようとして排他ロックの獲得待ち状態になります。
4) 復元ジョブが復元を行うためにデータベースに排他ロックを獲得しようとしますが、排他ロック獲得待ちとなっているセッションにブロックされ、デッドロックが発生します。
5) 復元ジョブがデッドロックの Victim となった場合、復元ジョブはエラーとなります。
通常、2) と 4) の間は非常に短時間ですが、対象のデータベースに対して頻繁にアクセスが発生する環境ではこのような現象が発生する可能性が高まります。
なお、復元ジョブがエラーとなった場合でも、次のジョブ実行タイミングで前回失敗した分のトランザクションログバックアップも合わせて復元されます。
そのため、利用システムのサービスレベルに応じて影響の有無を確認することをお勧めします。
Microsoft