O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Name

START TRANSACTION

Synopsis

New in SQL99, the START TRANSACTION statement allows all the functions of SET TRANSACTION to be performed and allows a new transaction to be initiated.

Vendor

Command

SQL Server

Not supported; see BEGIN TRAN later

MySQL

Not supported

Oracle

Not supported

PostgreSQL

Not supported; see BEGIN TRAN later

SQL99 Syntax and Description

START TRANSACTION { {READ ONLY | READ WRITE}[,...]
| ISOLATION LEVEL 
  {READ COMMITTED 
  | READ UNCOMMITTED 
  | REPEATABLE READ
  | SERIALIZABLE}[,...]
| DIAGNOSTIC SIZE INT};

The only difference between SET and START is that SET is considered outside of the current transaction, while START is considered the marking of a new transaction.

BEGIN TRANSACTION

The command BEGIN TRANSACTION provides similar functionality to START TRANSACTION. Both Microsoft SQL Server and PostgreSQL support BEGIN TRANSACTION, though they have slight variations in their syntax. Oracle supports implicit, but not explicit, transactions. MySQL doesn’t support atomic transactions at all. BEGIN TRANSACTION declares an explicit transaction, but it does not set isolation levels.

The Microsoft SQL Server syntax is:

BEGIN TRAN[SACTION] [transaction_name | @transaction_variable
[WITH MARK [ 'log_description' ] ] ]

Microsoft SQL Server allows a name to be assigned to a transaction or to reference transactions using a variable. It does not affect or add to functionality. When nesting transactions, only the outermost BEGIN . . . COMMIT or BEGIN . . . ROLLBACK ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required