Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the similarities and differences of Oracle, MySQL and DB2 concurrency control mechanisms". In daily operation, I believe many people have doubts about the similarities and differences of Oracle, MySQL and DB2 concurrency control mechanisms. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods, hoping to help you answer the doubts about "what are the similarities and differences of Oracle, MySQL and DB2 concurrency control mechanisms?" Next, please follow the editor to study!

Database data consistency support mechanism: transaction, lock, log.

First of all, let's look at what a transaction is.

I. Affairs

Transaction: also known as a transaction, the smallest recoverable unit that accesses a database system.

1. ACID of the transaction

Atomicity: the transaction is a whole unit of work, and the operation of the transaction on the database is either performed or cancelled.

Consistency: all data is in a consistent state when the transaction is completed

Isolation: changes made by firms must be isolated from changes made by other firms. When a transaction views the data, the state of the data is either before or after another transaction is modified, and it is not in an intermediate state. That is, multiple transactions cannot modify the same data at the same time.

Durability: changes made to the database are permanently saved after the transaction is committed.

2. Initialization and termination of transactions

The transaction is automatically initialized the first time the executable SQL executes, and once initialized, the transaction must be terminated (COMMIT or ROLLBACK).

1) COMMIT and ROLLBACK about transactions

In most cases, the transaction terminates the transaction by executing COMMIT or ROLLBACK. After executing the COMMIT statement, all changes made to the database after transaction initialization become permanent; after executing the ROLLBACK statement, all changes made to the database after transaction initialization are undone, and the database returns to the state before the transaction started.

2) about the results of unsuccessful transactions

What happens when a transaction is terminated by a COMMIT or ROLLBACK statement, and what happens if the system fails before the transaction completes? In this case, the database manager will undo all changes that are not COMMIT, thereby restoring data consistency.

Undo the modification in DB2 through the ACTIVE LOG log file. The log file contains information about each statement executed by the transaction and whether the transaction was successfully COMMIT or ROLLBACK.

MySQL and Oracle use undo log to undo changes. Undo log records changes to rows, fails to execute a transaction for some reason, or uses ROLLBACK to restore data to the way it was before modification using undo log.

3. Isolation level of the transaction

1) potential problems

Why do transactions need multiple isolation levels that can be set? In general, locks can isolate transactions in concurrent operations and ensure data consistency. Locks improve concurrency performance, but pose potential problems:

Dirty reading: the current transaction can read uncommitted data in another transaction.

Unrepeatable: the same piece of data read within a transaction is not the same.

Illusion: transaction A reads the newly inserted data on the second read under the same conditions.

Missing updates: the update operation of one transaction is overwritten by the update operation of another transaction, resulting in data inconsistency. For example:

Transaction T1 modifies the row record to V1, and transaction T1 is not committed.

Transaction T2 modifies the row record to V2 and transaction T2 is not committed.

Transaction T1 commit.

Transaction T2 commit.

Under the current database locking mechanism, it will not lead to the problem of losing updates in the theoretical sense, but in fact, this problem may occur in all multi-user computer systems. For example:

Transaction T1 queries a row of data, puts it in local memory, and displays it to User1.

Transaction T2 queries a row of data, puts it in local memory, and displays it to User2.

User1 modifies this line of records, updates the database, and submits.

User2 modifies this line of records, updates the database, and submits.

These problems are often related to the use and form of the system database. Setting the isolation level of the transaction is to solve the above problems according to different scenarios. For example, the problem of missing updates mentioned above, SELECT in the isolation level. FOR UPDATE, that is, when reading with the intention of updating, steps 1 and 2 should be locked to avoid the loss of updates. The isolation level of the database and its locking method are described in detail below.

2) isolation level of database and its locking method

Four isolation levels defined by the ① SQL standard

READ UNCOMMITTED: read not submitted. Transactions can see all uncommitted data from other transactions. Read data without locking

READ COMMITTED: submit for reading. Transactions can only see data that has been committed by other transactions

REPEATABLE READ: repetition. Lock some rows of transaction references that meet the retrieval criteria, which cannot be modified by other transactions, but can perform INSERT operations. That is, there may be illusions.

SERIALIZABLE: serializable. Forced sorting, adding locks on each read data row, all transactions are executed one by one, and there is no interference between transactions. Release the lock after the transaction is committed. It will lead to a lot of timeouts and lock competition.

Problems caused by the four isolation levels of ②

READ UNCOMMITTED √√√ READ COMMITTED × √√ REPEATABLE READ × × √ SERIALIZABLE × × ×

Isolation levels in the ③ database

Isolation level in DB2:

CS (Cursor Stability): cursor stability. Lock the data row by row. When the row data is not modified, the lock is released and the next row is read. When the row data is modified, the lock lasts until the transaction is terminated. CS programs cannot view changes that are not COMMIT in other programs.

CS provides maximum concurrency. However, the same transaction and the same cursor are processed twice, which may return different results, that is, non-repeatability; when there are any updatable cursors on the row read by the CS program, no other application can update or delete the row.

CS is the default isolation level for DB2. Used when maximum parallelism is required but only data that other programs have COMMIT can be seen.

RR (Repeatable Read): repeatable. RR locks all rows referenced by the transaction until COMMIT. Other programs cannot modify the data, and if a piece of data is accessed twice, it returns the same result.

RR is the highest isolation level, which can best ensure data consistency, but locking a large number of data will greatly reduce the degree of concurrency and may exceed the system-defined limit on the number of locks held.

Compared to the SERIALIZABLE in the standard definition isolation level, the locking range is the same.

RS (Read Stability): read stability. RS locks all rows referenced by the transaction that meet the retrieval criteria. Other programs cannot be modified, but can perform INSERT operations, so if the data is accessed twice in the same transaction, the newly inserted data may be returned, that is, phantom reading, but the old data will not change.

Compared with RR,RS, the amount of locked data is greatly reduced, and the degree of concurrency is improved. It is more suitable to run in a concurrent environment, but it is only suitable for programs that do not issue the same query many times in the same thing, or do not require the same query to get the same results, so as to avoid illusory reading.

DB2's RS is similar to REPEATABLE READ (repeat read) in the standard-defined isolation level, avoiding dirty reads, but can lead to phantom reading problems.

UR (Uncommitted Read): uncommitted read, which means "dirty" read. UR does not add any locks and can read any data in the database, including data that has been modified but not COMMIT. There may be a gap between the data read and the real data.

The UR level is most commonly used for queries on read-only tables, or only executes queries and does not care whether you can read unCOMMIT data from other programs.

UR is equivalent to READ UNCOMMITTED in the standard defined isolation level (uncommitted reads).

MySQL supports four isolation levels defined by the standard, and the default isolation level is REPEATABLE READ (repetition), but unlike the standard SQL, MySQL's InnoDB storage engine uses Next-Key Lock (locking a range and locking the record itself) under the isolation level of REPEATABLE READ, thus avoiding phantom reads. Therefore, under the isolation level of REPEATABLE READ, the InnoDB storage engine has been able to guarantee the isolation requirements of transactions, that is, to achieve the SERIALIZABLE isolation level of the SQL standard.

The Oracle database supports READ COMMITTED (commit read) and SERIALIZABLE transaction isolation levels. The default isolation level is READ COMMITTED (commit read).

Second, lock

Transaction isolation level is the overall solution of concurrency control, which is actually a comprehensive use of various types of locks and row versioning to solve concurrency problems.

Here we mainly look at the basic locks in the database.

1. Type of lock

S-LOCK: shared lock. Also known as read lock, when the user wants to read the data, add a shared lock to the data. Multiple shared locks can be added at the same time

X-LOCK: exclusive lock. Also known as write lock. X-LOCK occurs when the SQL INSERT/UPDATE/DELETE statement is executed. Only one exclusive lock can be added, which is mutually exclusive with other exclusive locks.

U-LOCK: modify the lock. When CURSOR SELECT has a UPDATE OF clause, the record read out by FETCH will be U-LOCK.

DB2, MySQL, and Oracle all support S-LOCK and XMurlock _ DB2 as well as U-LOCK.

2. Lock type when reading data in transaction isolation level

As mentioned above, the database is X-LOCK when SQL executes INSERT/UPDATE/DELETE statements under various isolation levels, so how do you lock it when reading the data?

DB2 and MySQL do not have any locks under the Uncommitted Read isolation level.

1) DB2

When DB2 is in the other three CS, RR, and RS isolation levels, the SELECT statement or CURSOR SELECT has no UPDATE OF clause, and the record read out by FETCH will S-LOCK. The difference is that CS releases the lock of the previous row when it reads the next row of data, and RR and RS release the lock only when the transaction commits; SELET... FOR UPDATE adds U locks to the data read, CS releases locks on the previous row when reading the next row of data, and RR and RS release locks only when the transaction commits; when INSERT/UPDATE/DELETE statements are executed, X locks are released only when the transaction commits, and X locks are released only when the transaction commits, and other transactions cannot add any locks to the locked rows.

2) MySQL

MySQL's InnoDB is not locked during SELECT under isolation levels READ COMMITED and REPEATABLE READ (the default isolation level for MySQL), that is, consistent unlocked reads in MySQL; only SELECT is specified. LOCK IN SHARE MOAD on the record under the SELECT LizABle isolation level on the SELECT record on the Smurlock; under the three isolation levels, SELET … FOR UPDATE adds an X lock to all read data, which is called a consistent lock read in MySQL.

3) Oracle

Only READ COMMITED and SERIALIZABLE isolation levels are supported in Oracle. The locking mechanism under these two isolation levels is the same as that of InnoDB. The READ UNCOMMITTED isolation level is not required in Oracle because the main function of READ UNCOMMITTED is to increase concurrency when read-only, as does Oracle using consistent unlocked reads under the READ COMMITED isolation level.

3. Consistent unlocked read

Isolation levels READ COMMITED and REPEATABLE READ (the default isolation level for MySQL) both use consistent unlocked reads and are not locked during SELECT, so how do you ensure transaction isolation? These two isolation levels use snapshot data to ensure isolation. When reading, for the X-locked data, it will read a snapshot data of the row. Snapshot data refers to the data of the previous version of the row, implemented through the undo segment. The undo segment is used to roll back data in a transaction, so the snapshot data itself has no additional overhead.

The difference between READ COMMITED and REPEATABLE READ isolation levels when reading snapshot data is that RC always reads the latest snapshot data, so non-repeatable reads may occur, that is, the data read the second time is inconsistent with the first time, while RR always reads the snapshot at the beginning of the transaction, so non-repeatability does not occur.

The non-locking read mechanism does not wait for the release of the X lock on the row, which greatly improves the concurrency of the database. Is the default reading method for InnoDB.

III. Summary

Concurrency control provides maximum concurrency under the premise of ensuring data consistency, and the premise of ensuring data consistency is to ensure the isolation of transactions, which is inversely proportional to concurrency. The higher the isolation level, the lower the concurrency. Therefore, the program should choose the isolation level according to the level of concurrency and isolation.

At this point, the study on "what are the similarities and differences of Oracle, MySQL and DB2 concurrency control mechanisms" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.