Re: SELECT statement behavior with Key Range Lock

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks Erland.

Your thoughts on the Serializable Isolation level are interesting. Our
databases experiencing this issue have their isolation level set to Read
Committed. Just for clarification, is there any way possible that the
Isolation Level can automatically change to Serializable, for instance, or
does it have to be an actual SET command?

I stated it the Key Lock was a RANGE(S-S) Key Lock (Shared range, shared
resource lock, serializable range scan) because the deadlock graph said the
Key Lock on the resource was a RANGE(S-S) and the following web site gave me
the interpretation. So that is why I am stating it is serializable.

http://msdn.microsoft.com/en-us/library/ms191272.aspx


Erland Sommarskog wrote:
Now, before getting to my question, I recognize there is opportunity to
rewrite this statement so that it is more optimal. It does not need a
SELECT * (ALL). I will be taking care of that.

As other have said, don't change that. In an EXISTS clause the * is only
a placeholder.

When a process using this statement gets deadlocked, and is the deadlock
victim, this process has a RANGE(S-S) Key Lock (Shared range, shared
resource lock, serializable range scan)

This means that at some point the transaction isolation level was
raised to serializable. At this level, you don't know permit rows to
be inserted that would alter the result of a SELECT statement. So
fot instance, if your IF EXISTS has yielded false, you do not permit
no rows to be inserted into Tasks that would satisfy the condition,
until you have committed.

Serializable is sometimes needed, but it has severe impact on the
concurrency of a system, and it is also very prone to lead to deadlocks.

on an index in a different table (let's name the index IX1). The
interesting thing is that the above IF NOT EXISTS(SELECT...)
statement is the first code executed in the procedure,

Thus, you had an open transaction when you entered the procedure.
This may be perfectly legal, if the procedure is called by an outer
procedure, or if the transaction was started from the client. But if
you believe that no transaction should be open at this point, this
would indiciate that you have an orphaned transaction. That is a
transaction that was started somewhere, but then neither committed
nor rolled back. Such transaction tend to cause a great mess.

My question: Am I to assume that when performing a SELECT that is
granted a Range(S-S) Key Range Lock, that the SELECT is not limited to
the single table it references in the SELECT statement, but extends
outward to include any indexes in other tables that may have a reference
to the columns used in the SELECT statement?

No, as I outlined above, you already had the locks before you started
to run the IF EXISTS.


--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200908/1

.

Relevant Pages

  • Re: ISOLATION levels and when to use SERIALIZEABLE
    ... Setting an isolation level tells SQL Server ... satisfy the where clause of a query in the transaction. ... acquiring KEY RANGE locks to protect a range of data in an index. ... select * into orders2 from orders ...
    (microsoft.public.sqlserver.programming)
  • Re: Pessimistic Locking
    ... > A normal select statement executed in a transaction with serializable ... > isolation level was still possible, ... > was called which should lock the row. ... > in a transacion with serializable isolation level and another client has ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: transactions on reads?
    ... which is to say you needn't make it a transaction explicitly. ... --Run the codes in #1 ... select the isolation level explicitly enable dirty read, ... Davolio and #3 get a dirty data. ...
    (microsoft.public.sqlserver.programming)
  • Re: concurrent transactions
    ... The deadlock is normal with any isolation level (it was my mistake in the ... Because that you need a locking hint when executing the select: ... You execute this with RC isolation level and will be fine. ... So I don't need a serializable transaction? ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Transaction isolation levels
    ... SELECT 'X' INTO #TEMP ... locks the row in table TICKETS until the entire transaction has ... The lock taken out on TICKETS will be a shared lock ... that under the default isolation level will be released. ...
    (comp.databases.ms-sqlserver)