Re: Tansaction/locking MSSQL2000

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

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/24/04 

Date: Wed, 25 Aug 2004 00:52:47 +0200

Hi I_Am_There,

Answers inline....

On Tue, 24 Aug 2004 15:07:02 -0700, I_Am_There wrote:

>
>Thank you for your response I think I understand what you are saying but I
>would like to dig just a little deeper to make sure I know and that I did not
>over simplify my question:
>
>Hugo wrote:
>> 1) The SELECT will request a shared lock on the whole table ...
>> When the SELECT statement finishes execution, the locks will be released
>Are you saying that ALL select statements acquire a shared TABLE lock on ALL
>tables in a single SELECT statement?

No, I am saying that a table lock would be used in the case of the example
you posted, which is a SELECT * with no WHERE clause.

> I thought if I use an index and supply a
>join/where clause then the system will only lock part of the table(s) that
>satisfy the join(s)/where(s) [extent, page, key, row]. If this is so then I
>would have concern (which is the basis for my original posting) that several
>things could be happening.

If you use a WHERE clause (or JOIN conditions if you join tables in the
query), the DBMS will choose between row, page or table level locks at
first, and row or page level locks might get promoted to table level locks
if it turns out that more rows (or pages) than expected are affected.

>THE BOTTOM 2 PARAGRAPHS IN A NUTSHELL: I want to know if the DB-Internals
>will automatically take care to ensure that every 'SELECT' statement [T1]
>will ALWAYS return the same Values/Set
> Given the same starting conditions (Tables A, B... With Sets {A0}, {B0}...)
> No matter what other transactions are started after T1 and run (try to
>run) during T1

When I read this at first, I thought you are asking if two completely
equal SELECT statements in the same transaction will always return the
exact same data; upon rereading, I began to doubt. The answer to that
question is: no, the default setting of SQL Server allow changes to the
data to be made in between these two SELECT statements, resulting in
different output; this default behaviour can be overriden with SET
TRANSACTION ISOLATION LEVEL, but the price you pay is lower concurrency.

If your question is indeed about two SELECT statements in the same
transaction and the above is not enough to satisfy your curiosity, then I
propose you check out the information about isolation levels in Books
Online; if that doesn't answer your question, post a follow-up message
here.

If I misinterpreted your question, read on.

>The nonsense I was try to spew out, before summarizing above:
> I want to make sure that all locks (Shared table, extent, page...) needed
>for a SELECT statement are acquired UpFront at the beginning of the statement
>execution So that NO other transaction can intefere (alter returned info)
>with the SET outcome after the SELECT transaction begins.
> If locks are done by indexes... on pages/keys... could this happen: a
>select starts and locks pages 1-2 and then T2 starts and adds row_i that ends
>up in page 3. Since the select only had page 1-2 locked it does not block T2
>from adding row_i to page 3, even though row_i should be used in the select
>statement? Or is it the fact that the DB-internals know that
>adding/updating/deleting row_i would be of interest to the already running
>SELECT Transaction and is therefore blocked?

I don't think that this is possible. If this were possible, there would be
cases of deadlocks caused by individual statements blocking each other
(e.q. connection 1 has locked page 1-2 and needs 3 as well, but connection
2 already has a lock on pages 3-4 and now wants 2). Deadlocks between
multi-statement transaction are to be expected in certain cases and can be
avoided by careful programming; this kind of deadlock would be unexpected
and unavoidable.

With the established user base of SQL Server, I think it's safe to assume
that IF this were possible, then it would already have happened, and IF it
already had happened, it would have been filed as a bug and resolved.

I don't know enough about SQL Server's internals to tell you HOW this
situation is avoided, but I'm quite sure THAT it is somehow avoided.

I hope that this answers your worries....

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Single database transaction across a multiple HTTP requests?
    ... Either this transaction is poorly designed, or you entire systems of stored ... You will need to change one or the other until you obtain a design that will ... > database engine. ... It is normal practice to issue the locks manually just before performing the ...
    (comp.lang.php)
  • Xlocking with a select statement
    ... SQL Server does not honour exclusive row level locking ... set transaction isolation level repeatable read ... This correctly blocks window two as expected. ... Sql server has taken out locks ...
    (microsoft.public.sqlserver.programming)
  • 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: ISOLATION levels and when to use SERIALIZEABLE
    ... if it takes no more locks then there is no disadvantage. ... ISOLATION levels and when to use SERIALIZEABLE ... satisfy the where clause of a query in the transaction. ... select * into orders2 from orders ...
    (microsoft.public.sqlserver.programming)
  • Re: Lock confusion
    ... SQL Server has to decide on a deadlock victim. ... DEADLOCK_PRIORITY option in your transaction to designate the victim ... In the 2005 studio manager the "Locks by Object" for the Color table ...
    (microsoft.public.dotnet.framework.adonet)