Re: Tansaction/locking MSSQL2000

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

From: I_Am_There (I_Am_There_at_discussions.microsoft.com)
Date: 08/24/04 

Date: Tue, 24 Aug 2004 15:07:02 -0700

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? 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.

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

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?

"Hugo Kornelis" wrote:

> On Tue, 24 Aug 2004 12:55:07 -0700, I_Am_There wrote:
>
> >Before I begin: If you saw my posting of this question on google last night,
> >then the only difference is this piece of info (I am in "autocommit
> >transaction mode"). I was answered
> >(http://groups.google.com/groups?dq=&hl=en&lr=&ie=UTF-8&safe=off&threadm=1kiyjow3fhlds.4ucwc5i5fogf%24.dlg%4040tude.net&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26safe%3Doff%26group%3Dcomp.databases.ms-sqlserver),
> >[and I thank the responders help] but I did not get solid clarity as the
> >response ended with 'I am not an expert on this; someone else in the group
> >may have better information.'
> >
> >(MSSQL2000) I have read the transaction/locking sections in the
> >MS-help, online and several books. What I want to understand is the
> >transaction behavior in single statements [not a BEGIN TRANSACTION
> >Statement1, Statement2... COMMIT].
> >
> >If I have a Table: "Letters" with 1 column "L" and the table presently
> >has rows{A,B,C,D}
> >
> >Case 1 (Insert):
> > First start transaction T1 "SELECT * FROM Letters"
> > Next start transaction T2 [separate connection] "INSERT INTO Letters
> >VALUES( 'Z' )"
> > Is it possible that T2 ends before T1 and the select returns
> >{A,B,C,D,Z}
> > Is it possible that T1 ends before T2 and the select returns
> >{A,B,C,D} [No 'Z']
> > Is this a race condition and I need to use a TABLOCK or TABLOCKX;
> >and are TABLOCK/TABLOCKX only hints? I mean does the use of TABLOCK
> >guarantee a lock on the table? Do I need to use 'SET TRANSACTION
> >ISOLATION LEVEL SERIALIZABLE' and if I use 'TRANSACTION ISOLATION
> >LEVEL' is there a means of telling the system which tables I will
> >touch so that I can avoid a deadlock [upfront tell the system what
> >tables I need to lock so there is not a race later]?
> >
> >Case 2 (Delete basically the same):
> > First start transaction T1 "SELECT * FROM Letters"
> > Next start transaction T2 "DELETE FROM Letters L = 'D'"
> > Is it possible that T2 ends before T1 and the select returns {A,B,C}
> >[No 'D']
> > Is it possible that T1 ends before T2 and the select returns
> >{A,B,C,D}
> >
> >Case 3 (Update basically the same):
> > First start transaction T1 "SELECT * FROM Letters"
> > Next start transaction T2 "UPDATE Letters SET L = 'Z'"
> > Is it possible that T2 ends before T1 and the select returns
> >{A,B,Z,Z} [Some letters were seen to become 'Z']
> > Is it possible that T1 ends before T2 and the select returns
> >{A,B,C,D}
>
> Hi I_Am_There,
>
> If, as you write it, the SELECT transaction is started BEFORE the
> insert/update/delete transaction, it will execute and finish first before
> the second transaction is started.
>
> The gory details:
>
> 1) The SELECT will request a shared lock on the whole table (a shared lock
> means that other connections may also issue their shared locks -for
> reading-, but no exclusive locks -for modifying- will be allowed); when
> the lock is taken, the data will be returned to the client.
> If the table (or part of the table) is already held by an exclusive lock
> (by another connection, possibly from another user), the request for a
> shared lock will be queued and execution of the SELECT has to wait until
> the blocking lock is released.
> When the SELECT statement finishes execution, the locks will be released
> (if the normal isolation level is used - otherwise the locks might be held
> until the end of the transaction).
>
> 2) The INSERT, UPDATE or DELETE will request an exclusive lock, probably
> at row or page level. If the lock held by the SELECT is not yet released,
> this can't be granted (it doesn't matter is the lock request by the SELECT
> operation is granted or queued, in both cases, this lock requests is made
> later and will not be honored). The request for an exclusive lock will be
> queued and execution of the statement has to wait.
> When the shared lock issued by the SELECT statement is released, the
> exclusive lock is granted and the INSERT, UPDATE or DELETE will be
> executed.
> When the transaction is finished (due to ROLLBACK/COMMIT, releasing the
> connection or end of statement with implicit transaction not enabled), the
> exclusive locks will be released. Note that exclusive locks arer always
> held until the end of the transaction, whereas shared locks will be
> released at the end of the statement (unless the isolation level is set
> higher than the default level).
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Relevant Pages

  • Re: Working Transactions somehow started not to work
    ... Transaction and locking are not properties of recordsets but are properties ... Opening a served-based cursor will put a lock on ... If a connection is closed, ... SL> and are not associated necessarily with server-based cursors. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Can extra processing threads help in this case?
    ... SQLite for just the financial aspect of the transaction. ... in DATAFILE sharing. ... CReader LOCK() ...
    (microsoft.public.vc.mfc)
  • Re: computational model of transactions
    ... It seems to me easy enough to tell if an UPDATE statement references ... the appropriate choice of transaction isolation level. ... transactions to obtain a shared lock, but not the exclusive lock required to ...
    (comp.databases.theory)
  • Re: controlling lock order in transactions
    ... Andrew J. Kelly SQL MVP ... > ROLLBACK TRANSACTION; ... > find in the clients sp that could be causing the dead lock is the join on ...
    (microsoft.public.sqlserver.programming)
  • Re: controlling lock order in transactions
    ... I believe the default isolation level for .net may be ... My transaction ONLY performs INSERTS. ... Yes it will put an exclusive lock on the row being inserted but Serializable ... COMMIT TRANSACTION ...
    (microsoft.public.sqlserver.programming)