Re: Tansaction/locking MSSQL2000

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

Date: Tue, 24 Aug 2004 22:35:29 +0200

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: 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)
  • Re: Sql Server Queue?
    ... Say I lock down this stored procedure as you suggested. ... > commit transaction ... > but here you are responsable for releasing the app lock manually. ...
    (microsoft.public.sqlserver.programming)