(2) database transactions and isolation-Example

This example demonstrates SQL Server, Oracle
Differences between different transaction isolation levels and the characteristics of the two databases
To simulate the concurrent environment, SQL Server opens two query windows in the SMO program. Oracle can use two SQL * Plus programs to connect to the same database to simulate
Two concurrent users in SQL Server and Oracle use transaction 1, which is short for transaction 2.
All test examples are based on the status after the script of the initial test table is run.
In the following example, the SET transaction isolation level statement changes the session isolation level until the session ends. Therefore, after the test is completed, the default level must be changed back.
Last, but not least. The following demos and explanations are based on the principle of easy-to-understand. The actual situation may be more complex, but it is sufficient for developers to understand the simplified model to this extent.

Test Table script:
SQL Server

Create Table [Customer] (
[Custid] [int] not null,
[Fname] [nvarchar] (20 ),
[Lname] [nvarchar] (20 ),
[Address] [nvarchar] (50 ),
[City] [nvarchar] (20 ),
[State] [nchar] (2) default ('CA '),
[Zip] [nchar] (5) not null,
[Phone] [nchar] (10)
)
Insert into customer values (1, 'gary ', 'mckee', '2017 main', 'palm springs', 'CA', 111 5551212)
Insert into customer values (2, 'Tom ', 'Smith', '2014 geogia ', 'fresno' 'jp', 609 5551212)
Insert into customer values (3, 'jams ', 'bond', 'st geogie 21', 'Washington ', 'ny', 20331,440 5551864)

Oracle

Create Table customer (
Custid int not null,
Fname nvarchar2 (20 ),
Lname nvarchar2 (20 ),
Address nvarchar2 (50 ),
City nvarchar2 (20 ),
State nchar (2) default 'CA ',
Zip nchar (5) not null,
Phone nchar (10)
);
Insert into customer values (1, 'gary ', 'mckee', '2017 main', 'palm springs', 'CA', 111 5551212 );
Insert into customer values (2, 'Tom ', 'Smith', '1970 geogia ', 'fresno', 'jp', 609 5551212 );
Insert into customer values (3, 'jams ', 'bond', 'st geogie 21', 'Washington ', 'ny', 20331,440 5551864 );

1. Comparison between SQL Server and Oracle statement Processing


By default, a single SQL Server statement is automatically submitted, that is, a single statement is automatically processed as a transaction. The Oracle principle is to delay the submission as much as possible, unless you encounter an explicit command or DDL statement.
SQL Server

Open transaction 1:
Run: Select * from customer

The table has three records.
Run: insert into customer values (4, 'Hello', 'World', 'Paradise road 01', 'heiven', 'xy', 00001,123 4564321)

To transaction 2:
Run: Select * from customer

We can see the records with the newly inserted custid 4 in transaction 1.

Oracle

Open transaction 1 and run:
Select * from customer;

You can see that the table has three records and runs:
Insert into customer values (4, 'Hello', 'World', 'Paradise road 01', 'heiven', 'xy', 00001,123 4564321 );

Go to transaction 2 and run:
Select * from customer;

Three records are displayed. The newly inserted record in transaction 1 is not automatically submitted and cannot be seen.
Go to transaction 1 and run:
Commit;

Go to transaction 2 and run:
Select * from customer;

Now we can see four records.

2. Update loss

Sqlserver is fully compatible with the four isolation levels defined in ANSI 92. Its default isolation level is read committed. At this level, the update may be lost. The same is true for Oracle by default. Therefore, they are no longer repeated.
SQL Server

Start transaction 1 and run:
SET transaction isolation level read committed
Begin tran
Select * from customer -- three records are displayed.

Now switch to transaction 2. At this time, transaction 1 has not ended. Run in transaction 2:
SET transaction isolation level read committed
Begin tran
Select * from customer
-- Three records are displayed, which are the same as those in transaction 1.
Now assume that transaction 1 continues to run, modify the data and submit it:
Update customer set state = 'tk 'Where custid = 3
Commit

Back to transaction 2, transaction 2 modifies data based on the previously queried results:
Update customer set zip = 99999 where State = 'ny'
Commit

As a result, because transaction 1 has modified the where condition data of transaction 2, transaction 2 has not successfully modified the data (in fact, it should be an update failure caused by phantom read. However, if the number of records meeting the condition is large, the update of transaction 2 may update a smaller number of records than expected, or it may be counted as "missing" part of the update that should have been completed. I think that as long as I understand what actually happened, I don't have to look into words too much ). There may be other situations when updates are lost, such as transaction 2.
Update customer set state = 'ko 'Where custid = 3

After both transactions are completed, the result of transaction 2 is reflected in the database, but the update of transaction 1 is lost, and transaction 2 does not know that it overwrites the update of transaction 1.

3. Dirty read demonstration

The default isolation level of sqlserver is read committed. When you manually change it to uncommitted read, the transaction can read data not committed by other transactions. Due to its own special implementation mechanism, Oracle, it can be understood that the basic isolation level is Repeatable read (which is different from the ANSI standard, as described in the following example ).
SQL Server

Open transaction 1 and run:
Begin tran
Select * from customer
Update customer set state = 'tn 'Where custid = 3

Go to transaction 2 and run:
SET transaction isolation level read uncommitted
Begin tran
Select * from customer

At this time, we can see that transaction 1 has been updated but has not yet been committed (State value TN of record 3 ). If transaction 1 finds that the data processing is incorrect, it goes to transaction 1 and rolls back:
Rollback

At this time, if transaction 2 is further processed based on the read data, it will cause errors. The data it reads is not updated to the database and is "dirty.

Oracle

The ANSI definition of uncommitted read (read uncommitted) level is not intended to intentionally introduce errors, but to provide a possible maximum concurrency level, that is, the data update of one transaction does not affect the reading of other transactions. Oracle implements data update at the kernel level without blocking reading. It can be said that it provides compatibility with the level of uncommitted read, but there is no dirty read problem. (For more information, see the corresponding PPT document.) Therefore, Oracle does not manually set the read uncommitted level statement.

4. Repeated read is not allowed.

SQL Server has no dirty read problem by default, but there is a non-repeated read problem. By default, Oracle is committed to read data. However, due to its own special mechanism, there is no non-repeated read problem at the statement level. That is to say, when a query lasts for a long time, the query results are consistent with the query start time (even if other transactions modify the data to be queried ), the SQL Server has a problem (the new feature of SQL Server 2005 provides optional statement-level consistency support, called the row version mechanism. In fact, it can be said that it is based on the multi-version of oracle, ).
As transaction consistency at the statement level is difficult to demonstrate, the following example shows the Non-repeated read occurrence at the transaction level and commit read isolation level:
SQL Server

Open transaction 1 and run:
SET transaction isolation level read committed
Begin tran
Select * from customer where State = 'CA'

You can get one record, which is run in transaction 2:
SET transaction isolation level read committed
Begin tran
Update customer set state = 'jp 'where State = 'CA'
Commit

Transaction 2 inserts a record and submits it. Back to transaction 1, transaction 1 continues to run. At this time, it re-queries the same data and makes further modifications, but it is found that the data read has changed.
Select * from customer where State = 'CA'

-- Two reads are inconsistent, and subsequent data processing should be canceled. Otherwise incorrect
Update customer set City = 'garden 'where State = 'CA'
Commit

Failed to obtain records. That is to say, two identical queries in the same transaction obtain different results, resulting in read duplication.

Oracle

Although Oracle provides statement-level transaction read consistency at the default isolation level, non-repeated read still occurs at the transaction level. It is the same as SQL Server, so it is no longer repeated.

5. Phantom reading

When the isolation level is set to Repeatable read, the problems in the preceding example can be solved. It is implemented internally by keeping the read lock during the transaction.
SQL Server

Start transaction 1, modify the transaction level to be repeated, and execute:
SET transaction isolation level Repeatable read
Begin tran
Select * from customer where State = 'CA'

Get one record as in the previous example. At this time, transaction 2 runs:
SET transaction isolation level Repeatable read

Begin tran
Update customer set state = 'jp 'where State = 'CA'
Commit

Transaction 2 remains waiting and does not end. Return transaction 1, run:
Select * from customer where State = 'CA' -- two reads with consistent results

Update customer set City = 'garden 'where State = 'CA'
Commit

After transaction 2 is successfully completed, transaction 1 is returned and transaction 1 is completed. The lock mechanism is used to block the modification of other transactions and maintain the read consistency during the transaction. However, if you insert data, the following problems still occur:
Start transaction 1, modify the transaction level to be repeated, and execute:
SET transaction isolation level Repeatable read
Begin tran
Select * from customer where State = 'CA'

Get one record. At this time, transaction 2 runs:
SET transaction isolation level Repeatable read
Begin tran
Insert into customer values (4, 'hellow', 'World', 'Paradise 001', 'garden ', 'CA', 00000,111 9995555)
Commit

Transaction 2 was committed immediately and ended normally. Return transaction 1, run:
Select * from customer where State = 'CA'

Two records are displayed. This phenomenon is called phantom reading.

Oracle

Due to its special mechanism, Oracle does not provide the option of consistent read isolation level. To achieve consistent read performance, you must actually upgrade the transaction to a serializable level.

6. Implementation of different databases at the serialization level

At this level, we can think that the data in the transaction is consistent at any time. This level makes it seem that no other user is modifying the data, and the database is "Frozen" at the beginning of the transaction (at least, for the data involved in this transaction ). However, different databases have different implementation mechanisms.
SQL Server

Start transaction 1 and run:
SET transaction isolation level serializable
Begin tran
Select * from customer where State = 'CA'

One record is obtained, and transaction 2 starts to run:
SET transaction isolation level serializable
Begin tran
Insert into customer values (4, 'hellow', 'World', 'Paradise 001', 'garden ', 'CA', 00000,111 9995555)
Commit

Transaction 2 is suspended, and it is waiting for transaction 1 to end. Return to transaction 1 and continue:
Select * from customer where State = 'CA'
Update customer set City = 'garden 'where State = 'CA'
Commit

After a short wait, transaction 1 receives a message similar to the following format:
Message 1205, Level 13, status 56, 1st rows
The transaction (process ID 51) and another process are deadlocked on the locked resource and have been selected as the deadlock victim. Run the transaction again.
Transaction 2 updates the data and ends normally. This is because both transactions are set to the serialization level. In case of a conflict, SQL Server selects to sacrifice one of the transactions according to certain rules to ensure the serialization of the transactions. In the preceding example, if the isolation level of transaction 2 is changed to commit read, transaction 2 will wait until transaction 1 is completed and then complete properly (because transaction 2 has no serial requirements, there will be no deadlocks ).

Oracle

In Oracle, multi-version can avoid deadlocks to some extent.
Start transaction 1 and run:
SET transaction isolation level serializable;
Select * from customer where State = 'CA'; -- the set Tran statement starts the transaction implicitly.


Obtain one record and start transaction 2:
SET transaction isolation level serializable;
Insert into customer values (4, 'hellow', 'World', 'Paradise 001', 'garden ', 'CA', 00000,111 9995555 );
Commit;

Transaction 2 can be found to be completed immediately without blocking. Return to transaction 1 and continue:
Select * from customer where State = 'CA ';
Update customer set City = 'garden 'where State = 'CA ';
Commit;

The second query in transaction 1 is consistent with the start time of the transaction, as if the commit completed in transaction 2 does not exist. The transaction is successfully updated, and data consistency at the start of the transaction is maintained.
However, if the transaction 1 or 2 modifies the same data row, an error occurs,
Start transaction 1 and run:
SET transaction isolation level serializable;
Select * from customer where State = 'CA'; -- the set Tran statement starts the transaction implicitly.


Obtain one record and start transaction 2:
SET transaction isolation level serializable;
Update customer set state = 'ko 'where State = 'CA ';
Commit;

Transaction 2 can be found to be completed immediately without blocking. Return to transaction 1 and continue:
Select * from customer where State = 'CA ';
Update customer set City = 'garden 'where State = 'CA ';
Commit;

Error message:
Row 3 has an error:
ORA-08177: Unable to continuously access this transaction processing

In general, Oracle uses a multi-version method to achieve a lower level of serialization, resulting in a deadlock, unless the two transactions modify the same data rows, it generally does not cause a conflict.

7. interaction at different isolation levels

The preceding example shows two transactions with the same isolation level. What is the difference between transactions with different isolation levels? In fact, for a transaction, the isolation level of other transactions is unknown to it. Furthermore, even if there are any other transactions in the database, it is unknown about how many transactions there are. There are two factors that affect transaction data access: the isolation level of the transaction itself, and the lock status of the data to be accessed by the transaction.
SQL Server

Start transaction 1 and run:
SET transaction isolation level serializable
Begin tran
Select * from customer where State = 'CA'

The query of transaction 1 obtains one record, forwards it to transaction 2, and runs:
SET transaction isolation level read uncommitted
Begin tran
Select * from customer

Transaction 2 queries three records, returns to transaction 1, and runs:
Update customer set City = 'garden 'where State = 'CA'

Switch to transaction 2 and run:
Select * from customer
Update customer set state = 'ko 'where State = 'CA'
Commit;

Because the isolation level of transaction 2 is uncommitted read, the modifications just made in transaction 1 can be seen immediately from the query, even if transaction 1 has not ended. Further update is suspended because transaction 1 adds an exclusive lock to the record. Return to transaction 1 and submit it:
Commit

Transaction 1 ends normally and the exclusive lock is released, so that transaction 2 can continue to modify data and finally complete.

Oracle

Only read committed and serializable (read only is not discussed for the time being) are set for the Oracle database isolation level. In addition to the special lock mechanism, the impact between transactions at different isolation levels is exclusive to the previous example (Example 6) if both are serializable, the other can be considered as non-blocking.

8. Page locks and row locks (limited to SQL Server)

SQL Server locks can be performed at the row level. However, it also has automatic lock expansion and lock conversion. Therefore, there are some unexpected situations. The following is a demonstration:
Start transaction 1 and run:
SET transaction isolation level read committed
Begin tran
Select * from customer where State = 'CA'
Update customer set City = 'garden 'where State = 'CA'

Theoretically, at the committed read level, the above update statement only adds an exclusive lock to the data row with the state value of CA. Other data in the table should be updated by other transactions, start of transaction 2 as follows:
SET transaction isolation level read committed
Begin tran
Select * from customer
Update customer set state = 'ko 'where State = 'jp'
Commit

Transaction 2 is found to be in a blocking state. Although they do not update the same record. Return to transaction 1 and run:
Commit

Transaction 2 continues to run until the end of transaction 1.
If we add an index to a table:
Create nonclustered index [idx_state] on [DBO]. [Customer] ([State])

Repeat the preceding steps and you will find that blocking does not exist.
PS: this phenomenon should be related to the default locking parameter/mechanism of the database and should be adjustable. However, no further information is available. Therefore, only the phenomena are listed.

Oracle

Oracle has only one lock on the Data row at the data level, so these problems do not exist in SQL Server.

9. Period of the SET transaction statement

All the preceding examples are demonstrated in the session window. Once the SET transaction statement is used, the entire session is affected. Unless the isolation level is changed explicitly, the session ends. For example:
Start transaction 1. Assume that the session is at the default isolation level (Read committed) of SQL server at the beginning ):
Begin tran
Select * from customer where State = 'CA'
Select * From SYS. dm_tran_locks

System View SYS. dm_tran_locks allows you to view the current locking status. Currently, only database-level locks are available. Continue running:
SET transaction isolation level Repeatable read
Select * from customer where State = 'CA'
Select * From SYS. dm_tran_locks
Commit

The next statement changes the isolation level to Repeatable read. The next query will see the row-Level Lock record. After the preceding transaction is committed, run:
Begin tran
Select * from customer where State = 'CA'
Select * From SYS. dm_tran_locks
Commit

The row-Level Lock record is still displayed in view SYS. dm_tran_locks. The entire session is affected.

However, if a stored procedure or function is called, changes to the transaction isolation level in the process or function will not affect the calling environment. The following example shows how to create a stored procedure script:
Create procedure [DBO]. [test_tran_level]
As
Begin
Begin tran
SET transaction isolation level Repeatable read
Select * from customer
Update customer set state = 'ss' where custid = 3
Select * From SYS. dm_tran_locks
Commit
End

Then, call the process in the session window. The current isolation level of the session window is the default committed read:
Exec test_tran_level

The running result shows the read lock information, and then runs in the session:
Begin tran
Select * from customer where State = 'CA'
Select * From SYS. dm_tran_locks
Commit

View SYS. dm_tran_locks does not have a read lock record, indicating that the transaction isolation level is still committed read.