Transaction - a sequence of operations performed as a single logical unit of work.
ACID
Atomic - all the work in the transaction is treated as a single unit. Either it is all performed or none of it is.
Consistent – a completed transaction leaves the database in a consistent internal state.
Isolations – the transaction sees the database in a consistent state. This transaction operates on a consistent view of the data. If two transactions try to update the same table, one will go first and then the other will follow.
Durability - the results of the transaction are permanently stored in the system.
Multi-Statement Transactions
BEGIN TRAN UPDATE ... SET ... = '' WHERE ... = '...' UPDATE ... SET ... = '' WHERE ... = '...' COMMIT TRAN
Rolling Back
BEGIN TRAN UPDATE ... SET ... = '' WHERE ... = '...' UPDATE ... SET ... = '' WHERE ... = '...' IF @@ROWCOUNT = 5 COMMIT TRAN ELSE ROLLBACK TRAN
Stored Procedures
CREATE PROCEDURE TranTest1
AS
BEGIN TRAN
INSERT INTO [...]
([f1], [f2], [f3], [f4], [f5])
VALUES ('...', '...', '...', '...', ...)
UPDATE ...
SET ... = ''
WHERE ... = '...'
COMMIT TRAN
GO
A = Single Unit of Work
C = Consistent Internal DB State
I =
D =
