Comparison of Oracle and SQLServer Transaction Processing (1)

Transaction processing is one of the most critical issues in large database products. Different transaction processing methods may cause huge differences in database performance and functions. At the same time, transaction processing is 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.

The following are some differences between Oracle and SQL Server transaction processing. 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 SQL.

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.