Begin the Commitment ;-) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Begin the Commitment ;-)

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-04-28 : 14:58:49

I've begun using

BEGIN TRAN

in my interactive work. It opens the time window so wide, I was wondering how SQL resolves two outstanding transactions?

Suppose TRAN1 deletes some rows, and TRAN2 updates the same rows. What happens if TRAN1 and TRAN2 both complete for both users, then TRAN1 is committed before TRAN2?

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-28 : 15:12:02

One user will be blocked - an update will lock the row so that the other user cannot perform an update until it is released.
Try it in two query analyser windows.

You should make sure that transactions are as short as possible.
Do not put a transaction round a single sql statement.
Try not to persist transactions across trips to the server -never with client interaction in the middle.
Try to send all the data to an SP and get it to do as much work as possible before starting the transaction before the first update.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamC
White Water Yakist

3467 Posts

Posted - 2004-04-28 : 15:23:08

Thanks Nigel.

Didn't realize BEGIN TRAN locked the tables.

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-28 : 15:33:39

Yep - it will hold the lock until the commit.

But it doesn't do this on a select so

begin tran
select @id = max(id) from tbl
insert tbl (id) select @id + 1
commit tran

will not work - I've seen this at a few companies. Even one where they refused to believe me even when I showed it putting in duplicates.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamC
White Water Yakist

3467 Posts

Posted - 2004-04-28 : 15:40:55

Huh? What doesn't work about it?

I'm guessing the table isn't locked, so another insert could come between the select and the insert?

The workaound would be:

INSERT tbl (id) SELECT MAX(ID)+1 FROM tbl

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-28 : 15:52:28

Yep.
It's usually held on another table and

begin tran
select @id = id + 1 from idtbl
update idtbl set id = @id
insert tbl (id) select @id + 1
commit tran

instead of
begin tran
update idtbl set id = id + 1, @id = id + 1
insert tbl (id) select @id
commit tran

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-28 : 16:05:29

Sam....the key here is short

And be careful with xp_cmdshell

It's launches another spid from a sproc..

Still don't understand how it blocked sometime, and other times it didn't..

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources