1 START_TRANSACTION Starts a transaction using the specified attributes. If DEFAULT is specified, then the attributes are derived from the user's profile. 2 Environment You can use the START TRANSACTION statement: o In interactive SQL o Embedded in host language programs to be precompiled o As part of a procedure in an SQL module o In dynamic SQL as a statement to be dynamically executed 2 Format START --+-> DEFAULT TRANSACTION --------------------------+--> +-> TRANSACTION -+------------------------------+-+ +--+-+-> transaction-mode -+-+-+ | +-> isolation-level --+ | +---------- , <-----------+ transaction-mode = --+-> READ ONLY ---+--> +-> READ WRITE --+ isolation-level = ---> ISOLATION LEVEL --+-> READ COMMITTED ----+--> +-> REPEATABLE READ ---+ +-> SERIALIZABLE ------+ 2 Arguments 3 DEFAULT If the keyword DEFAULT is used, the user-specific default transaction is started. This default is defined in the profile for the current session user. If none is specified, a READ ONLY transaction will be started. SQL> CREATE PROFILE READ_USERS cont> DEFAULT TRANSACTION READ ONLY WAIT 10; SQL> ALTER USER JONES PROFILE READ_USERS; A START DEFAULT TRANSACTION statement executed by JONES will start a READ ONLY WAIT 10 transaction. For information on profiles see the ALTER and CREATE PROFILE statements. 3 ISOLATION_LEVEL Syntax options: ISOLATION LEVEL READ COMMITTED ISOLATION LEVEL REPEATABLE READ ISOLATION LEVEL SERIALIZABLE Defines the degree to which database operations in an SQL transaction are affected by database operations in concurrently executing transactions. It determines the extent to which the database protects the consistency of your data. Oracle Rdb supports isolation levels READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. When you use SQL with Oracle Rdb databases, by default, SQL executes a transaction at isolation level SERIALIZABLE. The higher the isolation level, the more isolated a transaction is from other currently executing transactions. Isolation levels determine the type of phenomena that are allowed to occur during the execution of concurrent transactions. Two phenomena define SQL isolation levels for a transaction: o Nonrepeatable read Allows the return of different results within a single transaction when an SQL operation reads the same row in a table twice. Nonrepeatable reads can occur when another transaction modifies and commits a change to the row between transaction reads. o Phantom Allows the return of different results within a single transaction when an SQL operation retrieves a range of data values (or similar data existence check) twice. Phantoms can occur if another transaction inserted a new record and committed the insertion between executions of the range retrieval. Each isolation level differs in the phenomena it allows. The following table shows the phenomena permitted for the isolation levels that you can explicitly specify with the START TRANSACTION statement. Table 1-9 Phenomena Permitted at Each Isolation Level Nonrepeatable Isolation Reads Phantoms Level Allowed? Allowed? READ Yes Yes COMMITTED REPEATABLE No Yes READ SERIALIZABLE No No For read-only transactions, which always execute at isolation level SERIALIZABLE if snapshots are enabled, the database system guarantees that you will not see changes made by another user before you issue a COMMIT statement. See the Oracle Rdb Guide to SQL Programming for further information about specifying isolation levels in transactions. 3 READ_ONLY Retrieves a snapshot of the database at the moment the read-only transaction starts. Other users can update rows in the table you are using, but your transaction retrieves the rows as they existed at the time the transaction started. You cannot update, insert, or delete rows, or execute data definition statements in a read-only transaction with the exception of declaring a local temporary table or modifying data in a created or declared temporary table. Read-only transactions are implicitly isolation level serializable. Because a read-only transaction uses the snapshot (.snp) version of the database, any changes that other users make and commit during the transaction are invisible to you. Using a read-only transaction lets you read data without incurring the overhead of row locking. (You do incur overhead for keeping a snapshot of the tables you specify in the RESERVING clause, but this overhead is less than that of a comparable read/write transaction.) Because of the limited nature of read-only transactions, they are subject to several restrictions. 3 READ_WRITE Signals that you want to use the lock mechanisms of SQL for consistency in data retrieval and update. Read/write is the default transaction. Use the read/write transaction mode when you need to: o Insert, update, or delete data o Retrieve data that is guaranteed to be correct at the moment of retrieval o Use SQL data definition statements When you are reading a row in a read/write transaction, no other user can update that row. Under some circumstances, SQL may lock rows that you are not explicitly reading. o If your query is scanning a table without using an index, SQL locks all the rows in the record stream to maintain isolation level serializable. o If your query uses indexes, SQL may lock part of an index, which has the effect of locking several rows. 2 Examples Example 1: Starting a Default Transaction in a Multistatement Procedure or as a Single Statement SQL> START DEFAULT TRANSACTION; SQL> SQL> BEGIN cont> COMMIT; cont> START DEFAULT TRANSACTION; cont> END; SQL> SQL> ROLLBACK; Example 2: Starting Several Variations of the START TRANSACTION Statement SQL> START TRANSACTION READ WRITE, cont> ISOLATION LEVEL READ COMMITTED; SQL> COMMIT; SQL> SQL> -- Defaults to serializable SQL> START TRANSACTION READ WRITE; SQL> COMMIT; SQL> SQL> -- Defaults to read write SQL> START TRANSACTION ISOLATION LEVEL READ COMMITTED; SQL> ROLLBACK; SQL> SQL> -- Defaults to read write serializable SQL> START TRANSACTION; SQL> SQL> BEGIN cont> COMMIT; cont> START TRANSACTION cont> ISOLATION LEVEL READ COMMITTED, cont> READ WRITE; cont> END; SQL> COMMIT;