Tansaction/locking MSSQL2000

[heynothanks]

(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}

[Ross Presser]

On 23 Aug 2004 22:52:34 -0700, heynothanks wrote:

(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}

If the connection is in autocommit transaction mode (the default if no SET
IMPLICIT_TRANSACTIONS ON has been set), then every statement gets its own
transaction. It's as if every statement started with BEGIN TRANSACTION And
ended with END TRANSACTION.

If the connection is in implicit transaction mode, then the first statement
(SELECT, UPDATE, DELETE, INSERT, or some other types - see books online)
starts a transaction WHICH WILL REMAIN OPEN UNTIL you explicitly use COMMIT
TRANSACTION or else disconnect the connection. This can definitely be a
gotcha if you leave connections open for a long time!

In either mode, for the scenarios you describe, there is no way that
statement T2 can interfere with statement T1. If you were in implicit
transaction mode, then statement T1 would, I believe, start a read lock on
table Letters, which would not be released until the next COMMIT
TRANSACTION or until you closed the connection, and statement T2 would hang
until that happened. If you were in autocommit transaction mode, then
statement T1 would block T2 until T1 completed.

Only if you specifically used the NOLOCK hint on T1, would it be possible
for T2 to screw things up. I believe the behavior would then depend on the
specific timing.

I am not an expert on this; someone else in the group may have better
information.

[Erland Sommarskog]

heynothanks (he*********@yahoo.com) writes:

(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']
Yes, this is possible with the default transaciotn isolation level
which is READ COMMITTED, which says that once you have read a row,
you release that lock.

Here is a set-up to play with this kind of situation:

CREATE FUNCTION delay() RETURNS INT AS
BEGIN
EXEC master.dbo.xp_cmdshell 'osql -E -Q "WAITFOR DELAY ''00:00:05''"'
RETURN 0
END
go
CREATE TABLE a (a char(1) NOT NULL)
go
INSERT a (a)
SELECT 'A' UNION SELECT 'B' UNION
SELECT 'C' UNION SELECT 'D'
go
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SELECT *, dbo.delay() FROM a

Then run INSERT commands on a second connection.

For real-life queries, the existence of keys can affect the situation,
and you need to test each crucial case.

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

You can specify (SERIALIZABLE) as a hint on a query. As for deadlock
it depends on what you want to do. A common situation is to read the
most recently generated id in a table to get the next id. In this
case the hint to use is UPDLOCK. If you only use SERIALIZABLE, you
get a dead if two processes tries to convert shared locks to exclusive.
Update locks are shared locks, but only process at a time can have
an Update lock on a resource.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ross Presser]

On Tue, 24 Aug 2004 09:24:41 -0400, Ross Presser wrote:

I am not an expert on this; someone else in the group may have better
information.

As I half expected, my knowledge was wrong. I've canceled my posting,
though it's probably too late.