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 Statement

Synopsis

The START TRANSACTION statement allows you to perform all the functions of SET TRANSACTION while also initiating a new transaction.

Platform

Command

DB2

Not supported

MySQL

Supported, with limitations

Oracle

Not supported

PostgreSQL

Not supported;

see BEGIN TRAN below

SQL Server

Not supported;

see BEGIN TRAN below

SQL2003 Syntax

START TRANSACTION  [READ ONLY | READ WRITE]
   [ISOLATION LEVEL {READ COMMITTED | READ UNCOMMITTED |
      REPEATABLE READ | SERIALIZABLE}
   [DIAGNOSTIC SIZE int]

According to the ANSI standard, the only difference between SET and START is that SET is considered outside of the current transaction, while START is considered the beginning of a new transaction. Thus, SET TRANSACTION settings apply to the next transaction, while START TRANSACTION settings apply to the current transaction

While only MySQL supports the START TRANSACTION statement, three of the vendors (MySQL, PostgreSQL, and SQL Server) support a similar command, BEGIN [TRAN[SACTION]] and its synonym BEGIN [WORK]. BEGIN TRANSACTION declares an explicit transaction, but it does not set isolation levels.

Rules at a Glance

The only significant rule of the START TRANSACTION statement is that you must use this statement to control the access mode, isolation level, or diagnostic size of the current transaction. Once a new transaction starts, you must either issue new values for the setting(s) or rely on the default.

Most database platforms allow you to implicitly control ...

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