SQL and Oracle's differences in database transaction processing

Software programming in Gizhgara: Getting Started with database transactions (for beginners to read) articles about MS SQL Server and Oracle's differences in database transaction processing are a bit controversial, so keep track of my thoughts on database transactions.

Brief introduction

This article describes the differences between MS SQL Server and Oracle on database transaction processing, and how Oracle implements transactions.

What is a transaction

A database transaction (DB Transaction) is a unit of processing for a set of database operations. Transactions conform to acid characteristics:

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

Consistent: Consistency, all dependencies, and constraints consistent.

Isolated: Separation, different affairs do not affect each other.

Durable: persistence, the data that commits the transaction needs to be persisted.

Why to use transactions

There are two main functions of implementing a transaction:

1. Ensure the consistent (consistency state) of the database, keep all dependencies and constraints consistent. Even if the database management system fails (for example, a power outage), it can revert to a consistent state. For example, a bank transfer system, John to Lee four to 3000 Yuan RMB, John account on the 3000 and Dick account number plus 3000 need to complete at the same time, otherwise the system's account is uneven. Also for example some sales system of summary table and BOM, is a main table and a from the table, need to synchronize updates.

2. Concurrency separation of different transaction operations. For example, the data in the edit process is not queried by other transactions. This is also relative, and may need to support dirty read (dirty reading) under special effects requirements, but not the scope of this discussion.

transaction type for SQL Server 2008

1. Autocommit Transaction autocommit transactions

This is the default transaction type for SQL Server, and each individual SQL statement (SQL statement) is a separate transaction that is automatically committed when the statement is executed. The caller does not need to manually control the transaction flow.

2. Display Transaction EXPLICIT transactions

The caller needs to invoke the API or use the BEGIN TRANSACTION statement of T-SQL to open the transaction. You need to invoke commit or ROLLBACK TRANSACTION to commit or rollback.

3. Implicit transaction implicit transactions

Use set implicit_transactions on to turn the transaction pattern into an implicit pattern. The caller does not need to execute the BEGIN TRANSACTION statement to open the transaction. The database engine automatically opens the transaction when it executes to the SQL statement. The caller needs to invoke a commit or ROLLBACK TRANSACTION to commit or rollback. A new transaction is automatically opened when the database engine executes the next SQL statement.

Reference: Controlling transactions (Database Engine)

Oracle's transaction type

Oracle's transaction type is somewhat like an implicit transaction of SQL Server. When the transaction is automatically opened when executing to the first executable SQL statement, then the caller performs a commit or rollback to commit or rolls back the transaction, and Oracle automatically submits the transaction if there is a DDL statement.

Reference: Transaction Management

Implementation of Oracle's transactions

The structure of Oracle is logically and physically different. The logical structure is the table space, and the physical structure is the data file.

Logical implementation

Implementation transactions under Oracle are logically implemented by the undo tablespace. The Undo tablespace contains the Undo segements (segment), and the undo Segements contains the undo Data. The Undo data is a logical unit that supports transactions.

Figure from Oracle Database 10g:administration Workshop I

The Undo data is used to save changes before and after, to support rollback, long queries, and flashback queries, as well as the ability to recover from failed transactions.

Because of the undo data, it becomes easier to rollback the transaction because the undo data preserves the changes before and after, guaranteeing the atomicity of the transaction. Failure transaction recovery and transaction rollback are similar, and the database engine can revert to a consistent state when a network outage or other cause causes the transaction to stop abnormally.

At the same time, undo data also supports long time queries (read-consistent), for example, there is a table T, the primary key is key, the field is F1, the data is as follows, although there are only 4 data, it is assumed that it will take a long time to query.

Transaction at the start of the query, has not ended, and transaction two began to modify the key to 1 of the data is Z, transaction two to submit, the data into the following table. However, the a,b,c,d is read out at the end of a transaction query because the query reads the snapshot from the undo.

The flashback query is an Oracle 10g feature that can be queried for data before it is submitted, such as the above example transaction three wants to query after transaction two is submitted A,b,c,d can be completed by flashback query. This is also supported by the undo data.

Physical implementation

From the logical implementation above, it is not clear how the Oracle's support for the transaction is realized, just knowing the role of the transaction and the undo data. The following is a physical structure that describes how Oracle supports transactions. Please look at a physical structure chart first.

Figure from Oracle Database 10g:administration Workshop I

In order to simplify, I only use personal language to describe the parts related to the transaction. The SGA can be understood as global memory. The database buffer cache stores the data cache that is read from the data file. The purple cylinder is a data file. Redo log buffer for the cache, that is, save the log memory block, all the data changes will be recorded in the Redo log buffer inside. For example, use back transaction two to update key 1 data. When transaction two updates the key to 1 data from A to Z. The Oracle Database Engine records the ROWID of the key 1 data, the modified data A, and the modified data z in the redo log buffer. If transaction two continues to update the data for key 2 to Y, then the rowid of the key 2 data, the modified data B, and the modified data y are also recorded into redo log buffer. Each of the data is recorded and is a pipelined record. Once transaction two commits, the LGWR process (log Writer, logging process) will be redo log buffer data in order to write to log datafile inside, that is, log data files, when the log file is completed, The Oracle database engine will generate a SCN (System change number, systems update), by which time the Oracle database engine notifies the caller that the commit is complete. Here you can see that Oracle does not have to write the updated data back to the data file at the time of submission, but instead writes it to the log file. Because the sequential write linear log file speed much faster, and write data file is required to read and write randomly. Because of the linear record and the SCN control, the database engine can get the most current data through the operation of the redo log file. Of course, the data file will be updated at the end of the checkpoint, but it is not necessary to update the data file when the transaction is submitted, which can greatly improve the performance.

Because of this mechanism, rollback becomes very simple and it is easy to read the SCN before it is submitted.

Suggestions on database transaction processing for Beginners

I am also a step by step from the novice, and now is not a veteran, is a bit of experience, if it is just getting started students, you feel useful to look at, useless on the past.

For the processing of database transactions, the beginning of the time does not need to be very in-depth understanding of the principle of the database, of course, it is necessary to understand the priority after the row. High priority is as follows:

First of all, the business is not something profound and mysterious, and all the systems I have done from the beginning, including the embedded system, are used in the transaction. I do not think that most system transactions have any problems, just some constraints and synchronization mechanism, really have a problem from the perspective of their own system design, it is not necessarily said from the perspective of database technology to find solutions. For example, Oracle long time query if the Undo data (historical data) to overwrite, Oracle will throw an exception "ora-01555:snapshot too old", if this problem, I will start from the point of view of the system design, why there is such a large query, Why the other transactions in this query will update the data, whether the query is just a check is enough, check the process needs to lock the table and so on. However, this problem can be solved by database tuning, but I think the point of view of the problem is to start from its own system design.

Second, you should know the difference between the transaction types of different databases, for example, Ms SQL Sever is the default for autocommit transactions, and you need to know that each statement has a separate transaction in action. Oracle is similar to an implicit transaction and must be manually commit or rollback.

Third, the use of transactions to know the corresponding, especially nested transactions, the beginning and finish. When a lot of problems occur at the end of the day, note that exception handling needs to end an already open transaction.

The 1th is the psychological problem, the 23rd is the technical problem, do well I think can get started to develop the system. Later encountered problems in a step-by-step depth.