Sanity-check my assumption on Transactions please.

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 2005 Forums

Transact-SQL (2005)

Sanity-check my assumption on Transactions please.

Author

Topic

davepl
Starting Member

4 Posts

Posted - 2010-06-09 : 15:51:26

This is my first time using transactions, so I want to ensure that one assumption I have is correct. If I did the following pseudocode to make a $100 withdrawl from the customer's account

Begin Transaction
Check to ensure customer has > $100 in their account
Deduct $100 from their account
Dispense $100 from machine
End Transaction

Now I know that if anything goes wrong with -my- actions during the transactions that I can roll back what -my- steps were.

What I'm not certain of is that after the "Check they have $100" step some other parallel query doesn't remove money from the account. In other words, do all of my checks and queries hold true throughout the transaction, such that other queries are blocked from changing them?

Those are two very different things (making my operations undoable vs making my checks atomic), so I thought I'd better ask!

Many thanks,
Dave

Davepl

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-09 : 15:55:54

When you write BEGIN TRAN the transaction isn't really starting.
It starts first with next statement. And when it does, SQL Server holds proper locks on table so that noone else can touch same records until you either commmit or rollback the transaction.

N 56°04'39.26"
E 12°55'05.63"

Kristen
Test

22859 Posts

Posted - 2010-06-09 : 16:00:19

I would change

Check to ensure customer has > $100 in their account
Deduct $100 from their account

to be

Update customer account to reduce balance by $100
WHERE CustomerID = 1234 AND AccountBalance > $100

IF @@ROWCOUNT <> 1 ... ROLLBACK and RETURN

rather than doing a lookup first.

But it looks fine to me (not sure if you have to do anything to HOLD a lock from the SELECT that checks the balance until the UPDATE that reduces the balance - but my update-and-check-one-row-changed gets around that)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-09 : 16:01:06

[code]BEGIN TRAN

DECLARE @Amount MONEY

SET @Amount = 100

-- This places a lock on this record (or page) so that noone else can use and/or touch this record.
UPDATE Accounts
SET Amount = Amount - @Amount
WHERE CustomerID = 1
AND Amount >= @Amount

IF @@ROWCOUNT = 0 OR @@ERROR <> 0
BEGIN
RAISERROR('Could not withdraw %d dollars from account.', 18, 1, @Amount)
ROLLBACK TRAN
RETURN -1000
END

UPDATE Machine
SET Amount = Amount + 100
WHERE CustomerID = 1

IF @@ROWCOUNT = 0 OR @@ERROR <> 0
BEGIN
RAISERROR('Could not dispense %d dollars to machine.', 18, 1, @Amount)
ROLLBACK TRAN
RETURN -1010
END

COMMIT TRAN[/code]

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-09 : 16:02:01

N 56°04'39.26"
E 12°55'05.63"

davepl
Starting Member

4 Posts

Posted - 2010-06-11 : 16:13:04

Thanks... the horse is indeed now dead, but I had to make sure. The info on the locks was what I needed to confirm. Thanks for the help!

As a random side note, when I was reading up on it, it sounds like MSSQL server is smart enough to use a lightweight transaction unless you start touching DBs across multiple machines, at which point it escalates to Distributed Transaction Coordinator. So people are doing a lot more complicated things than I plan to :-)

Davepl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources