Features of transaction processing in SQL Server and Oracle

SQL ServerAndOracleFeatures of medium Transaction Processing

1Transaction Features (AcidAttribute)

NAtomicAtomicityThat is, the transaction should be taken as a unit of work, and the transaction is processed completely. All the work is either saved in the database or completely rolled back, but not retained..

NConsistentConsistency Transactions ensure that data in the database is not logically inconsistent. Consistency is typically implicitly included in other attributes.

NIsolatedIsolationMultiple transactions are performed at the same time, and they should not interfere with each other.It is necessary to prevent a transaction from unreasonably accessing and reading incomplete data when it processes the data to be modified by other transactions..

NDurablePermanentAfter the transaction is committed, the work is permanently saved..

2Transaction mode and start and end time

ØSQL Server

NAutomatic transactions

Indicates thatSQLStatement,SQL ServerA transaction is automatically started, and the commit operation is automatically performed after execution to complete the transaction. It can also be said that in this transaction mode,SQLA statement is a transaction.

It can be understood that everyDMLOrDDLThe statement is finally converted to the following statement execution

Begin

Commit

DML/DDL

Commit(WhenDML/DDLErrorRollback)

End

NImplicit transactions

Used in current sessionSet implicit_transactions ON/OFFEnabled/Stop implicit transactions. Implicit Transaction fromSet implicit_transactions onThe firstDMLStatement (data operation statement, suchInsert,Update,Delete)DDLStatement (a Data Structure dictionary statement, suchAlter,Create) Start the transaction.CommitOrRollbackEnd the transaction.

NExplicit transactions/User-Defined transactions

PassBegin transactionShow start transaction,Commit/rollbackCommit or roll back a transaction.

ØOracle

OracleNo ImageSQL ServerThe same definition of transactions, noTransactionKeyword. The transactions are composedDML/DDLThe statement starts automatically.Commit/rollbackSubmit/Rollback.

SelectThe statement is not strictly meaningful.DML, InSQL ServerHe does not start the transaction, so he can onlyBegin transactionInSelectStart transaction. InOracleBecause the transaction start method is not displayed, this method does not work. What should I do?SelectOfFor updateSub-statement can makeOracleTheSelectThe statement is used to enable transactions implicitly.

For updateClause also has an important purpose inSQL ServerAndOracleDefines modifiable cursors.

3Transaction isolation level

3.1Negative impact of concurrency

NUpdate loss (Lost update): TransactionAI do not know the transaction in the execution process.BA record has been modified. IfAIf the record is also modified, the transactionBMay be lost.

NDirty read (Dirty read): The transaction reads data that has not been committed by other transactions;

NNon-repeated read (Nonrepeatable read): Due to the impact of other parallel transactions, this transaction reads the same data successively and gets different results (records have been modified ).

NPhantom read (Phantom read): Due to the impact of other parallel transactions, the transaction reads the same data successively and gets different results (new records appear in the results ).

3.2Concurrency Control

There are many concurrent control technologies. The most important thing is the lock mechanism. The locks implemented by various database systems are similar. This article does not detail the lock mechanism.

3.3ANSIDefined4Transaction isolation level

NRead uncommitted: A session can read the uncommitted UPDATE results of other transactions. If the transaction ends with a rollback, the read results may be incorrect, therefore, most database applications do not use this isolation level.

NRead committed: This isSQL ServerThe default isolation level, set to this isolation level of transactions can only read the results of updates committed by other transactions, otherwise, wait, but other sessions can modify the records read in this transaction, you don't have to wait until the transaction ends. Obviously, at this isolation level, the results of two identical read operations in a transaction may be different.

NRepeatable read: In a transaction, if no record is added between two read operations with the same condition, and there are no other update operations, the number of records increases under this query condition, the two read results are the same. In other words, the record read for the first time in a transaction will not change during this transaction.SQL ServerThis isolation level is achieved by locking the read records during the entire transaction. In this way, before the transaction ends, other sessions cannot modify the records read in the transaction, however, you can only wait for the transaction to end,SQL ServerIt does not prevent other sessions from adding records to the table, nor prevent other sessions from modifying other records.

NSerializable:Simply put,SerializableIs to make the transaction seem to be executed in sequence one by one.For example, transactionsAAnd TransactionsBFor concurrent executionTaAndTBThe order of submission, the data in the database must have two time periods:AAfter submission andBAfter the transaction is committed, the data at both times should be complete and correct, and the data at each time point is only related to the data at the beginning of the transaction and the transaction logic, it is irrelevant to whether other transactions are being executed.

3.4SQL ServerIsolation level in

SQL ServerImplemented inANSIDefined4And the following two levels are added:

NRead_committed_snapshot: If the transactionAThe data has been modified but has not been committed. The transactionBWhen trying to read the dataBIsolation level:

URead committed: Waiting for transactionsACan be read only after submission;

URead_committed_snapshot: Ignore transactionsAUpdate the data and read the last submitted data version;

NAllow_snapshot_isolation: If the transactionAToRepeatable readIsolation level, transactionAIf you read some dataAThe data will be locked to ensure Repeatable read. Other transactions can only wait.ASubmit or roll back. IfAToAllow_snapshot_isolationOther transactions do not have to wait and can update the data while ensuring that the transactionA.

3.5OracleIsolation level in

OracleOnlyANSIDefined4At the isolation levelRead committedAndSerializable.