Comparison between Oracle and SQL Server Transaction Processing

Transaction processing is a key issue for all large database products. database vendors have spent a lot of energy in this aspect, different transaction processing methods may cause huge differences in database performance and functions.

Transaction processing is also a problem that must be deeply understood by database administrators and database application developers. Negligence on this issue may lead to application logic errors and inefficiency.

Next we will discuss some of their differences in transaction processing for Oracle and SQL Server, which are currently widely used large database products. Unless otherwise stated, the applicable database product versions of this article are Oracle9i and SQL Server 2000. The example SQL statement is executed in SQL * Plus for Oracle, SQL Server 2000 is executed in osql.

1. Concepts of transactions

Transactions can be seen as a unit composed of several operations on the database. These operations are either completed or canceled to ensure that the data meets the consistency requirements. A typical example of A transaction is the transfer operation in A bank. Account A transfers A certain amount of money to account B. This operation involves two steps, one is to subtract A certain amount of deposits from account A, and the other is to add the same amount of deposits to account B. The two steps are either completed or canceled, otherwise the bank will suffer losses. Obviously, the two steps in this transfer operation constitute a transaction.

Transactions in the database also have the following ACID features.

ACID is the first letter of four English words. These four English words are Atomicity, Consistency, Isolation, and Durability, which are translated into Atomicity, Consistency, Isolation, and Durability.

Atomicity: indicates that the operations in the transaction are completed or all are canceled.

Consistency: operations in transactions ensure that data in the database is not logically inconsistent. Consistency is generally implicitly included in other attributes.

Isolation: The current transaction is isolated from other unfinished transactions. At different isolation levels, the read operations of transactions have different results.

Durability: After a COMMIT command is issued to a transaction, the transaction effect is persistent even if a system failure occurs. On the contrary, when the system fails during transaction execution, all transaction operations are rolled back, that is, the database is returned to the status before the transaction starts.

Modifications to the data in the database are completed in the memory. These modifications may have been written to the hard disk or not to the hard disk. If a fault such as power failure or system error occurs during the operation, the database can ensure that the database data modification results of unfinished transactions will be canceled after the next database startup even if they have been written to the disk. For closed transactions, even if the modification result has not been written to the disk, the database will "redo" through the records in the transaction log after the next start, that is, the lost data modification result will be re-generated and written to the disk, this ensures that the transaction is permanently modified. This also ensures that all operations in the transaction are completed or all are revoked.

Ii. Differences between transaction settings and types

There are three transaction types in SQL Server: Implicit Transaction, explicit transaction, and automatic commit transaction. The default value is automatic commit.

Automatic commit means that SQL Server automatically starts a transaction for each SQL statement sent by the user and completes the transaction after the execution, in this transaction mode, an SQL statement is a transaction.

Explicit transactions refer to starting a Transaction with Begin Transaction in the automatic Commit mode, ending a Transaction with Commit or Rollback, and ending a Transaction with Commit is a permanent modification of the Transaction, even if a power failure occurs. For example, the following is an example of an explicit transaction in SQL Server.

Begin Tran Update emp Set ename=’Smith’ Where empno=7369 Insert Into dept Values(60,’HR’,’GZh’)Commit

Implicit transactions refer to the transaction type Set with the Set Implicit_Transactions On command in the current session. At this time, any DML Statement (Delete, Update, Insert) will start a transaction, the transaction ends with Commit or Rollback.

There are no SQL Server transaction types in Oracle. By default, any DML statement starts a transaction until the user issues a Commit or Rollback operation, this is similar to the implicit transaction mode of SQL Server.

Iii. transaction isolation level

In the SQL92 standard, there are four transaction isolation levels: Read Uncommitted, Read Committed, Read Repeatable, and Serializable. The Read Uncommitted and Read Committed are statement-level, read Repeatable and Serializable are for transaction level.

The statements for setting transaction isolation levels in Oracle and SQL Server are the same, and both use the standard SQL92 syntax, that is:

Set Transaction Isolation Level Read Committed

In the preceding example, Read Committed can be replaced with any of the other three isolation levels.

1. isolation level and implementation mechanism in SQL Server

All four isolation levels are provided in SQL Server.

In SQL Server, the meanings of these isolation levels and their implementation methods are discussed below.

Read 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.

Read Committed: This is the default isolation level of SQL Server. A transaction with this isolation level can only Read the UPDATE results submitted by other transactions. Otherwise, a wait occurs, however, other sessions can modify the records read in this transaction without waiting for the transaction to end. Obviously, at this isolation level, two identical read operations in a transaction, the results may be different.

Read Repeatable: in a transaction, if no record is added between two Read operations with the same condition, and no other update operations are performed, 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 Server locks the read records throughout the transaction to achieve this isolation level. In this way, before the transaction ends, other sessions cannot modify the records read in the transaction, however, SQL Server can only wait for the transaction to end, but does not prevent other sessions from adding records to the table or modifying other records.

Serializable: in a transaction, the read operation results in records committed by other transactions before the transaction starts. SQL Server locks the table throughout the transaction to achieve this isolation level. At this isolation level, all DML operations on this table are not allowed, that is, to wait until the transaction ends, this ensures that the results of the two read operations in a transaction are certainly the same.

2. isolation level and implementation mechanism in Oracle

In Oracle, there is no Read Uncommitted or Repeatable Read isolation level. In this way, a session is not allowed to Read the Uncommitted data modification results of other transactions in Oracle, this avoids read errors caused by transaction rollback. The Read Committed and Serializable levels in Oracle are similar to those in SQL Server, but their implementation methods are quite different.

In Oracle, the so-called rollback segment (earlier than Oracle9i) or the revocation segment (Oracle9i version) exists. When Oracle modifies data records, the modified results will be stored in the rollback or withdrawal segments. This is because of this mechanism that Oracle implements transaction isolation levels completely different from SQL Server. In Oracle, read operations do not impede update operations, and update operations do not impede read operations. In this way, read operations do not wait until the update transaction ends at various isolation levels in Oracle, the update operation does not wait because of the read operation in another transaction, which is also an advantage of Oracle transaction processing.

The default setting of Oracle is Read Committed isolation level (also called statement-level isolation). At this isolation level, if a transaction is performing DML operations on a table, at this time, another session reads the records of this table, Oracle will read the records stored before the rollback segment or the Undo segment, instead of waiting for the end of the update transaction like SQL Server.

At the Serializable isolation level (also known as transaction-level isolation), The read operation in the transaction can only read the data results submitted before the transaction starts. If another transaction is modifying the record at the time of reading, then Oracle will find the original unmodified record in the rollback segment or undo segment (and store it in the rollback segment or undo segment before the transaction where the read operation is located starts ), at this time, the read operation will not wait because the corresponding record is updated.

Iv. Impact of DDL statements on transactions

1. Impact of DDL statements on transactions in Oracle

In Oracle, when executing DDL statements (such as Create Table and Create View), a Commit command is automatically issued before execution, and a Commit or Rollback command is then issued, that is, DDL is executed like the following pseudo code:

Commit; DDL_Statement; If (Error) then Rollback; Else Commit; End if;

The following example shows the impact of DDL statements on transactions in Oracle:

Insert into some_table values(‘Before’); Creaate table T(x int); Insert into some_table values(‘After’); Rollback;

Since the statement is submitted before Oracle executes the Create table statement, and the Commit command is automatically issued After the Create table statement is executed, only the row inserted After is rolled back, the line inserted to Before will not be rolled back, and the result of the Create table command will not be rolled back. Even if the Create table statement fails, the Before insertion will be submitted. If the final Commit command is issued, the Insert Before and Create table operations are submitted Before, so the Commit command only affects the Insert After operation.

2. Impact of DDL statements on transactions in SQL Server

In SQL Server, DDL statements have the same effect on transactions as other DML statements. That is to say, no Commit command is automatically issued before or after a DML statement is issued.

In SQL Server 2000, the database rolls back to the status Before the Before is inserted after Rollback is triggered for the same Oracle example above, that is, the rows inserted to Before and After will be rolled back, and the data table T will not be created.

If a Commit operation is triggered, all the results of the three operations are submitted.

5. Impact of database disconnection on transactions

In addition, SQL * Plus, a management client tool corresponding to Oracle, is osql in SQL Server 2000, both of which are command line tools. The usage and functions are similar, however, in SQL * Plus, when you exit the connection, the Commit command is automatically issued and then exited. In osql, If you exit the connection, the Rollback command is automatically issued, this has no effect on the automatic commit mode of SQL Server, but the effect is obvious if it is in the implicit transaction mode. Other client management tools for the two database products are similar.

1. 10 million records inserted and queried in Oracle
2. Oracle DBA responsibilities and daily work analysis
3. How to automatically back up Oracle databases in UNIX