Geek Answers Handbook

Can I be a victim of a dead end if I do not fulfill the request in a transaction?

Suppose I open a transaction and run update requests.

BEGIN TRANSACTION
UPDATE x SET y = z WHERE w = v

The request returns successfully, and the transaction remains open intentionally for a certain period of time before I decide to commit.

While I am sitting on a transaction, is it possible that the MKSQL stub machine can preempt my open transaction, which actually does nothing to either clear the stub or free up resources, since the system memory / resource limits are reached?

I know about SET DEADLOCK_PRIORITY and have read MSDN articles on the subject of deadlocks. It’s logical, since I’m not actively looking for applications for any additional resources, I can’t imagine a scenario that would call a reasonable algorithm for avoiding a dead end.

Does anyone know for sure if it is possible that just blocking can make me a valid target? Similarly, does any low resource condition cause the killing of my SPID?

+5
source share
5 answers

NO

For a deadlock to occur, all participants in the deadlock chain must have expectations for the resource (lock). If your connection is idle, it means that it is not fulfilling the request, which means that it cannot wait.

As for the other conditions that might kill your session, I can imagine at least three:

  • administrative operations that use WITH ROLLBACK_IMMEDIATE
  • mirror resiliency
  • KILL <yourspid>, , DBA
+3

: , .

- , , SELECT.

, , :

  • ,

  • ,

, , , . UPDATE .

, .

+3

, , .

1 ( ) , , , , . SQL Server , SQL Server. , .

, "", "" , , . , , .

, , Sql Server Profiler, , .. - , ...

. , , , , .

0

:

  • SQL Server . .

  • (, , tempdb). .

  • - , . , . , .

:

  • CASCADE: DELETE , CASCADE .

  • : .

  • DELETE UPDATE FROM, . , .

0
source

Just because you are not in a transaction does not mean that you are not holding locks.

-1
source

More articles:


All Articles