Layout: Post title: “Isolation between database transactions” subtitle: “Isolation level “” Date: 2018-10-21 07:00:00 author:” “img/post-bg-2015.jpg” catalog: true tags: – database – transaction – transaction isolation

Database concurrency and transactions

Two-phase locking is the most common transaction concurrency control method in DBMSs, used to provide both serializability and recoverability for correctness. In order to access a database object a transaction first needs to acquire a lock for this object. Depending on the access operation type (e.g., reading or writing an object) and on the lock type, acquiring the lock may be blocked and postponed, if another transaction is holding a lock for that object.

What is a transaction

One user, one request, one connection, one thread, one transaction.

1. Atomic // Operation atoms either all execute or none execute.

2.consistent // Data consistency refers to accuracy.

Transaction 1 and transaction 2 each add/transfer 100 to account A, where account A should have received 200. However, due to different transaction isolation levels, the data read by transaction 2 May be old data, thus overwriting the data written by transaction 1, and ultimately account A receives 100.

Both transactions are atomic, but there is no guarantee that the data is correct/consistent.

2) The data should not violate various constraints of the database, otherwise the data is incorrect, and the data can not be inserted into what constraints? For example, if the field type is an integer and you insert a string, an error is reported. For example, if the primary key is not null, you will get an error if the primary key has no value when you insert data.

www.zhihu.com/question/30… www.zhihu.com/question/31… Stackoverflow.com/questions/1…

3.isolation // Isolation between transactions and the isolation level

Duration // Persist data to database/disk

What is isolation between transactions

1. Multiple transactions 2. Operating on the same data 3. The difference in this outcome is the difference in the isolation level.

What are the consequences?

Dirty read

Dirty read refers to reading uncommitted data from another transaction. That is, wrong data, non-existent data — because the update SQL for transaction 2 rolled back.

Specifically, transaction 1 has two identical query SQL and transaction 2 has one update SQL. The update SQL of transaction 2 occurs in the middle of the SQL of two identical queries of transaction 1, causing transaction 1-subsequent queries to read the uncommitted data of transaction 2.

In summary, the same transaction of the same query SQL, query data is not the same, this is one; Second, and more importantly, the second read is data that does not exist in the database. The second point is more important, because dirty reading means reading data that doesn’t exist, and the first point has different data. This is also the case in non-repeat reading.

The best practice, in practice, when it’s actually applied, is that it’s rarely applied, because in general, you want to see the data after it’s submitted, you don’t need to see it before it’s submitted. In fact, the default isolation level of database vendors, mysql and Oracle is not this case, but reads already committed data.

Isolation Level The isolation level of dirty reads is read uncommitted data.

How to solve the problem of reading uncommitted? Transaction 2- The lock is locked while the data is being updated and is not released until after the commit is committed – to ensure that no other transaction can read the data while it is being updated.

If you can resolve this problem, you will avoid and will not have read uncommitted cases.

The repeated read

Non-repeatable read: indicates that the data is different in the second read. Non-repeat read indicates that the data in the second read is different.

The example is the same as the previous example. Only the update SQL for transaction 2 is committed.

Now the data for transaction 1’s 2 queries is different, because the update SQL for transaction 2 has been committed, and transaction 1- subsequent queries will see the new data.

At the SERIALIZABLE and REPEATABLE READ isolation levels, the DBMS must return the old value for the second SELECT.

At READ COMMITTED and READ UNCOMMITTED, the DBMS may return the updated value;

this is a non-repeatable read.

Isolation Level Non-repeat Read The corresponding isolation level is read commit.

How to solve the problem that the data read twice is different? If the problem of non-repeat reads is resolved, that is, the two reads of transaction 1 are old data, then it is called a repeat read and the isolation level is repeat read. Repeat read means to ensure that the data is the same for the second read.

How to ensure that, is transaction 1 operation, lock until transaction 1 commits — the purpose is not to allow other transactions to update the data.

Note that the lock is a row lock, meaning that no other transaction is allowed to update this row of data unless transaction 1 is committed.

Phantom read

Phantom reads.

Example of SQL code, slightly changed, is that now select is a range of data, where condition is a range. Transaction 2 updates the data for this scope and has committed. The result is that the data in the second read of transaction 1 is different, which is called a phantom read.

The previous non-repeat read/repeat read is for the data of the same record. The lock is a row lock. Phantom read, for data in the same range (for example, data in the same table). The lock in this case is a range lock.

So rows are locked and other transactions can insert new records. Range/table locks do not allow new records to be inserted within a specified range.

Note that Transaction 1 executed the same query twice. If the highest level of isolation were maintained, the same set of rows should be returned both times, and indeed that is what is mandated to occur in a database operating at the SQL SERIALIZABLE isolation level. However, at the lesser isolation levels, a different set of rows may be returned the second time.

In the SERIALIZABLE isolation mode, Query 1 would result in all records with age in the range 10 to 30 being locked, thus Query 2 would block until the first transaction was committed. In REPEATABLE READ mode, the range would not be locked, allowing the record to be inserted and the second execution of Query 1 to include the new row in its results.

The isolation level corresponding to the isolation level is the phantom read level. The phantom read level and repeat read are the same level, so the corresponding isolation level is repeat read.

How to solve the problem of unreal reading? Use range/row locks – no new records are allowed to be inserted in the specified range.

The isolation level that can solve the illusion problem is called Serializable, which means that transactions are executed sequentially, one at a time, without jumping the queue. Not all at once, of course, but for each isolation level of the problem, lock the corresponding data, such as row data, range data. During row data, range data, and read, no other transactions are allowed to update until transaction 1 has committed.

What are the isolation levels

Read uncommitted // 2. Read Committed // 3. Repeat read // 4. Read / / sequence

The relationship between consequences and isolation levels

See above.

Database locks

1. Row lock 2. Range lock 3

The relationship between locks and SQL, transactions, and transaction isolation

See above.

An underlying implementation of transaction isolation

Understanding the problems of reading data and then understanding how to solve them gives you a basic understanding of transaction isolation and transaction isolation levels.

How do database vendors implement transactions?

Docs.oracle.com/cd/E17984_0… Docs.oracle.com/cd/E17984_0…

www.jianshu.com/p/2af078f4c… www.jianshu.com/p/d75ecc545… Amao12580. Making. IO/post / 2016/0…

How do database vendors implement transaction isolation

mysql

The default isolation level is repeat read.

oracle

The default isolation level is read Committed.

In actual combat

code

User request – Controller layer – Business layer – DAO layer – Persistence layer – database.

The client

Mysql default storage engine is InnoDB, innoDB default isolation level is repeat read. However, the client side of multiple tabs, can see the uncommitted data, why? The data that is read twice is the same as the old data. This is because multiple tabs on the client side are connected to the same transaction, so you can see uncommitted updates to other tabs.

So how to open multiple connections/transactions when the client opens multiple tabs? Look at the client documentation.

The spring transaction

To understand Spring transaction and transaction isolation, you first need to understand database transaction and transaction isolation, so it’s good to put Spring transactions together with database transaction and transaction isolation.

Refer to the Spring Transation section.

How to configure

How to use

Isolation level

The isolation level is the same as for the database. There are four scenarios.

Which class? Isolation enumeration class.

How to configure it? In the case of annotation-based, the attribute isolation level of the annotation class configures the isolation level of the current method. The default isolation level is the same as that of the current database vendor, for example, Oracle is read committed and mysql is repeat read.

How to use it? You just need to configure it.

Spread level

Same as above.

reference

1. Transation and Isolation, the official documents of database vendors.

Docs.oracle.com/database/12…

Dev.mysql.com/doc/refman/…

2. Wikipedia en.wikipedia.org/wiki/Isolat…