Differences between ms SQL Server and Oracle in Database Transaction Processing

Background

Software Programming in Jiri GA is fascinating: getting started with Database Transaction Processing (suitable for beginners)ArticleThe difference between ms SQL Server and Oracle in Database Transaction Processing caused some controversy, So I recorded my thoughts on database transaction processing.

 

Introduction

This article describes the differences between ms SQL Server and Oracle in database transaction processing, and how Oracle implements transaction processing.

 

What is a transaction?

Database Transaction is the processing unit of a group of database operations. Transactions comply with acid features:

Atomic: atomicity, either all or nothing. All or none.

Consistent: consistency. All dependencies and constraints are consistent.

Isolated: separation. Different transactions do not affect each other.

Durable: persistence. The data submitted for the transaction must be persistent.

 

Why use transactions?

There are two main functions to implement transactions:

1. Ensure the database consistent (consistent state) and keep all dependencies and constraints consistent ). Even if the database management system fails (such as power failure), it can be restored to a consistent state. For example, in a bank transfer system, Michael Jacob transfers 3000 RMB to Michael Lee, and adds 3000 RMB to Michael Jacob's account minus 3000 RMB and Mr. Li's account minus RMB. Otherwise, the system's account will be unbalanced. For example, some sales systemsSummary tableIt is a master table and a slave table, and must be updated synchronously.

2. Separate different transaction operations during concurrency. For example, data in the editing process is not queried by other transactions. This is also relative. In special effects, dirty read (dirty read) may be supported, but it is not the scope discussed here.

 

Transaction type of SQL Server 2008

1. automatically submit the transaction autocommit transactions

This is the default transaction type of SQL Server. Each SQL statement (SQL statement) is a separate transaction, which is automatically submitted after the statement is executed. The caller does not need to manually control the transaction process.

2. Display transaction explicit transactions

The caller needs to call the API or use the begin transaction statement of the T-SQL to open the transaction. You need to call commit or rollback transaction to submit or roll back.

3. Implicit Transaction implicit transactions

Use set implicit_transactions on to change the transaction mode to the implicit mode. The caller does not need to execute the begin transaction statement to open the transaction. The database engine automatically opens transactions when executing SQL statements. The caller needs to call commit or rollback transaction to submit or roll back. When the database engine executes the next SQL statement, a new transaction is automatically opened.

Reference: controlling transactions (database engine)

Oracle transaction type

Oracle's transaction processing type is a bit like SQL Server's implicit transaction. The transaction is automatically opened when the first executable SQL statement is executed, and then the caller needs to execute commit or rollback to submit or roll back the transaction. If there is a DDL statement, oracle will also automatically submit the transaction.

Reference: Transaction Management

 

Oracle transaction implementation

The structure of Oracle is divided into logical and physical differences. The logical structure is the tablespace, while the physical structure is the data file.

Logical implementation

Oracle implements transactions inLogicallyIt is implemented by undo tablespace. Undo tablespace contains undo segements (segment), while undo segements contains undo data. Undo data is a logical unit that supports transactions.

Source: Oracle Database 10g: Administration Workshop I

Undo data is used to save the data before and after modification to support rollback, long-time query, flashback query, and recovery of failed transactions.

With undo data,Roll back a transactionIt becomes easy because undo data saves the data before and after modification, ensuring the atomicity of the transaction.Failed transaction recoverySimilar to transaction rollback, when a transaction stops abnormally due to network interruption or other causes, the database engine can return to the consistent state.

At the same time, undo data also supportsLong query(Read-consistent). For example, if a table t exists, the primary key is key, the field is F1, and the data is as follows, although there are only four data records, it is assumed that the query takes a long time.

Key	F1
1	A
2	B
3	C
4	D

The transaction is queried at the beginning and has not ended yet. Transaction 2 changes the data whose key is 1 to Z, transaction 2 commits, and the data is changed to the following table. However, at the end of a transaction query, A, B, C, and D are read, because the query reads snapshots from undo.

Key	F1
1	Z
2	B
3	C
4	D

Flashback QueryIt is a function introduced by Oracle 10 Gb. It can query the data before modification after commit. For example, transaction 3 in the preceding example wants to query a, B, c After commit of transaction 2, d. You can use the flashback query. This is also supported by undo data.

 

Physical implementation

From the above logic implementation, we only know the role of transactions and undo data, and we still don't know how Oracle's support for transactions is implemented. The following describes how Oracle supports transactions in the physical structure. First look at a physical structure.

Source: Oracle Database 10g: Administration Workshop I

To simplify the process, I only use my own language to describe transaction-related components. SGA can be interpreted as global memory. The database buffer cache stores the data cache read from the data file. The purple cylinder is a data file. The redo log buffer is the redo log cache, that is, the memory block for storing logs. All data modifications are recorded in the redo log buffer. For example, an example of using transaction 2 to update the key as 1 is returned. When transaction 2 Updates key to 1 data from A to Z. The Oracle Database Engine records the rowid of data with key 1, data a before modification, and data Z after modification in redo log buffer. If transaction 2 continues to update data whose key is 2 to Y, the rowid of the Data whose key is 2 is recorded in redo log buffer as well as B before the modification and Y after the modification. Each piece of data is recorded, and it is a pipeline record. Once transaction 2 is committed, the lgwr process (log writer, log Writing Process) will write the redo log buffer data in order to log datafile, that is, the log data file, when the log file is written, the Oracle database engine generates an SCN (System Change number, system update number). At this time, the Oracle Database Engine notifies the caller that the submission is complete. Here we can see that Oracle does not need to write the updated data back to the data file when submitting, but to the log file. Because sequential Writing of linear log files is much faster, writing data files requires random reading and writing. Due to linear record and SCN number control, the database engine can obtain the latest data through redo log (redo log file) operations. Of course, the data file will still be updated at the checkpoint, but it is not necessary to update the data file when the transaction is committed, which can greatly improve the performance.

Because of this mechanism, rollback becomes very simple. It is easy to read the SCN before submission.

 

Suggestions for beginners on Database Transaction Processing

I also walked through from a newbie step by step. Now I am not an old bird. It is a bit of experience. If you are a beginner, you can take a look at it if you think it is useful.

For the processing of database transactions, you do not need to have a deep understanding of the principles of the database at the beginning. Of course, you still need to know about the principles in the future. The high priority is as follows:

First of all, transactions are not profound and mysterious. All the systems I have done since the beginning of the line, including the current embedded systems, are used for transactions. I don't think there are any problems with most system transactions, but some constraints and synchronization mechanisms. If there are problems, from the perspective of system design, I don't necessarily want to find a solution from the perspective of database technology. For example, Oracle's long-time query if undo data (historical data) to overwrite, Oracle will throw an exception "ORA-01555: Snapshot too old", if this problem occurs, from the perspective of system design, why are there so many queries? Why are other transactions updating data in this query? Is it enough to query these queries only once, check whether the table needs to be locked. However, this problem can be solved through database optimization, but I think the problem is first from the perspective of its own system design.

Second, you need to know the differences between the transaction types of different databases. For example, ms SQL Server automatically commits transactions by default. When using ms SQL Server, you need to know that each statement has a separate transaction in operation. Oracle is similar to implicit transactions and must be manually committed or rollback.

Third, you need to know the one-to-one correspondence when using transactions, especially the nested transactions. When many problems occur, pay attention to the need to end opened transactions for exception handling.

 

The first is a psychological problem, and the second is a technical problem. If you do well, you can get started with the development system. In the future, problems will be solved step by step.

 

Ms SQL server for the implementation of the transaction next article again. The above is my understanding of Oracle implementation, and I hope to point out that I can improve the article.