|
|
|
|
|
|
Handling Transactions
Transaction control statements in Mimer SQL are:
COMMITCOMMIT BACKUPROLLBACKSET TRANSACTION READ ONLYSET TRANSACTION READ WRITESET TRANSACTION ISOLATION LEVEL SERIALIZABLESET TRANSACTION ISOLATION LEVEL REPEATABLE READSET TRANSACTION ISOLATION LEVEL READ COMMITTEDSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSET TRANSACTION START EXPLICITSET TRANSACTION START IMPLICITSET TRANSACTION DIAGNOSTICS SIZESET SESSION CHARACTERISTICS AS TRANSACTION READ ONLYSET SESSION CHARACTERISTICS AS TRANSACTION READ WRITESET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLESET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READSET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTEDSET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSET SESSION CHARACTERISTICS AS TRANSACTION DIAGNOSTICS SIZESTART BACKUPSTART TRANSACTIONSQL Statement Restrictions in Transactions
The following SQL statements may not be used inside a transaction:
ENTERSET SHADOWLEAVESET TRANSACTIONSET DATABANKSTART BACKUPSET DATABASESTART TRANSACTIONSET SESSIONData definition statements (e.g. ALTER, DROP, UPDATE STATISTICS) may be used inside a transaction provided they are the only statement executed in that transaction.
Optimizing Transactions
It is strongly recommended that the
SET TRANSACTION READ ONLYoption be used for each transaction that does not perform updates to the database and that theSET TRANSACTION READ WRITEoption be used only when a transaction performs updates.Taking a little extra care to set these options appropriately will ensure the transaction performance remains optimal at all times.
The default transaction read option can be defined by using
SET SESSION, see Default Transaction Options. If this has not been used to set the default transaction read option, the default isREAD WRITE.Consistency Within a Transaction
The
SET TRANSACTION ISOLATION LEVELoptions are provided to control the degree to which the updates performed by one transaction are affected by the updates performed by other transactions which are executing concurrently.The default isolation level can be defined by using
SET SESSION, see Default Transaction Options. If this has not been used to set a default isolation level, the default isREPEATABLE READ. This isolation level guarantees that the end result of the operations performed by two or more concurrent transactions is the same as if the transactions had been executed in a serial fashion, except that an effect known as `Phantoms' may occur.This is where one transaction reads a set of rows that satisfy some search condition. Another transaction then performs an update which generates one or more new rows that satisfy that search condition. If the original query is repeated (using exactly the same search condition), extra rows appear in the result set that were previously not found.
The other isolation levels are:
READ UNCOMMITTED,READ COMMITTEDandSERIALIZABLE.All four isolation levels guarantee that each transaction will be executed completely or not at all and that no updates will be lost.
Refer to Mimer SQL Reference Manual, SET TRANSACTION, for a full description of the effects that are possible, or guaranteed never to occur, at each of the four isolation levels.
Default Transaction Options
- SET SESSION
The
SET SESSIONstatement is provided so that default values for certain transaction control settings can be defined.
SET SESSIONallows the default settings forSET TRANSACTION READandSET TRANSACTION ISOLATION LEVELto be defined.- SET TRANSACTION READ and SET TRANSACTION ISOLATION LEVEL
The transaction control settings defined by
SET TRANSACTION READ, see Optimizing Transactions, andSET TRANSACTION ISOLATION LEVEL, see Consistency Within a Transaction, apply to the single next transaction to be started. If these statements are not used explicitly before each transaction, the default settings apply.
 Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se |
|
|
|
|
|
|