Detailed parsing of transactions and locks in sqlserver

A few days ago, "read", "sqlserver2005 advanced programming", and "SQL Server 2008 Programming beginners (3rd edition)" were translated Chinese versions. It turns out that the directory structure is roughly the same, and its content is almost the same. Suspected of plagiarism. We can see that the transactions and locks share the same small examples and tables. Haha... I really don't want to make too many comments on such books. Domestic translation books. To be honest, most of the translations are a little stiff. And those "Original Works. Most of them are plagiarism and empty talk. For Microsoft's technical system, if you copy the Code directly from the MSDN or online books and paste several pages of code, then you can publish and sell it, maybe I can write a book too, because there is no level.

Of course, there are also a lot of excellent works, but few and difficult to find. Well, let's get down to the truth and start talking about transactions and locks. This is probably something more difficult to understand in the database.

1. Dirty reading, non-repeated reading, and phantom reading

(1) Dirty read:Dirty read means that when a transaction is accessing data and modifying the data has not been committed to the database, another transaction also accesses the data, then the data is used.

For example:

Michael Jacob's salary is 5000. In transaction A, his salary is changed to 8000, but transaction Ahas not yet submitted.

At the same time,

Transaction B is reading the salary of Michael Jacob, and the salary of reading Michael Jacob is 8000.

Then,

Transaction A has an exception and rolled back the transaction. Michael's salary rolled back to 5000.

Finally,

Transaction B reads 8000 of Michael's salary, that is, dirty data, and transaction B performs a dirty read.

(2) Non-repeated read:A transaction reads the same data multiple times. When the transaction is not completed, another transaction also accesses the same data. Therefore, the data read twice in the first transaction may be different because of the modification of the second transaction. In this way, the data read twice in a transaction is different, so it is called non-repeated read.

For example:

In transaction A, the read salary of Michael Jacob is 5000, the operation is not completed, and the transaction has not been committed.

At the same time,

Transaction B changed Michael's salary to 8000 and submitted the transaction.

Then,

In transaction A, read the salary of Michael Jacob again, and the salary becomes 8000. The result of two reads before and after a transaction is inconsistent, resulting in non-repeated reads.

(3) Phantom read:It refers to a phenomenon that occurs when a transaction is not executed independently. For example, the first transaction modifies the data in a table, which involves all the data rows in the table. At the same time, the second transaction also modifies the data in this table. This modification inserts a new row of data into the table. In the future, the user who operates the first transaction will find that there are still data rows in the table that have not been modified, just like an illusion.

For example:

Currently, there are 10 employees with A salary of 5000. Transaction A reads 10 employees with A total salary of 5000.

At this time,

Transaction B inserts a record with a salary of 5000.

This is because transaction A reads 11 employees with A salary of 5000 again. In this case, Phantom reads are generated.

Repeatable reading focuses on modification:
In the same condition, the data you read is read again and the value is different.
Phantom read focuses on adding or deleting:
Under the same condition, the number of records read for 1st and 2nd times is different.

Ii. exclusive locks, shared locks, update locks, optimistic locks, and pessimistic locks

1. Two lock Classification Methods

(1) from the perspective of the database system, there are three types of locks:

• Exclusive Lock)
A resource with an exclusive lock can only be used by locked programs. Other operations on the resource are not accepted. SQL Server automatically uses an exclusive lock when executing the data UPDATE command, namely the INSERT, UPDATE, or DELETE command. However, an exclusive lock cannot be applied to an object when other locks exist. The exclusive lock cannot be released until the transaction ends.

• Shared Lock)
A shared lock can be read by other users, but cannot be modified by other users. When the SELECT command is executed, SQL Server usually locks the object through a shared lock. Generally, after the data page with the shared lock is read, the shared lock will be released immediately.

• Update Lock)
Update locks are created to prevent deadlocks. When SQL Server is preparing to update data, it first locks the data object so that the data cannot be modified but can be read. When SQL Server determines that it wants to update data, it will automatically replace the update lock with an exclusive lock. However, when other locks exist on the object, the update locks cannot be locked.

(2) From the programmer's perspective, there are two types of locks:

• Pessimistic Lock)
Pessimistic lock, just like its name, refers to a conservative attitude towards data being modified by the outside world (including other current transactions of the system and transactions from the external system, therefore, the data is locked throughout the data processing process. Pessimistic locks often rely on the locks provided by the database (and only the locks provided by the database layer can truly guarantee the exclusive data access; otherwise, even if the locking mechanism is implemented in the system, it cannot be ensured that the external system will not modify the data ). Bytes

• Optimistic Lock)
Compared with pessimistic locks, optimistic locks adopt a more loose locking mechanism. Pessimistic locks are implemented by the database lock mechanism in most cases to ensure maximum operation exclusiveness. However, there is a large amount of database performance overhead, especially for long transactions, which is often unbearable.

The optimistic lock mechanism solves this problem to some extent. Optimistic locks are mostly implemented based on the data Version record mechanism. What is the data version? Add a version ID for the data. In the database table-based version solution, you can add a "version" field to the database table. When reading the data, read the version number together, and then add one to the version number when updating the data. In this case, the version data of the submitted data is compared with the current version information recorded in the database table. If the submitted data version number is greater than the current version number of the database table, it is updated, otherwise, expired data is considered. Bytes

2. How to Use locks in Databases

First, let's start with the pessimistic lock. In many other databases, such as SqlServer, Data Locking usually uses page-level locks. That is to say, data in a table is a serialized update insertion mechanism, at any time, only one piece of data is inserted in the same table. Other data to be inserted can be inserted in sequence until the data is inserted. The consequence is reduced performance. When multiple users access a table frequently, the response efficiency is very low, and the database is often in a false state. Oracle uses row-level locks. It only locks the data to be locked, and the rest of the data is irrelevant. Therefore, when inserting data into an Oracle table, basically, there will be no impact.

Note: There is a high possibility of pessimistic locks targeting concurrency, And we generally use optimistic locks in our applications.

The pessimistic locks of Oracle need to use an existing connection in two ways. From the difference of SQL statements, it is either for update or for update nowait. For example, let's look at an example.

First, create a database table for testing:

Create table test (ID, NAME, LOCATION, VALUE, CONSTRAINT test_pk primary key (ID) as select deptno, dname, loc, 1 FROM scott. dept

Here we use the scott User table of Oracle Sample to copy the data to our test table.

(1) for update

Then let's take a look at the locking method for update. Run the following select for update statement:

Select * from test where id = 10 for update

After the search statement is locked, open another SQL * plus window and execute the preceding SQL statement. You will find that sqlplus seems to have died, it seems that no data is retrieved, but no results are returned. It is stuck there. The reason for this is that the select for update statement in the First Session locks the data. Because the lock mechanism here is the wait status (as long as it does not represent nowait, It is wait), so the second Session (that is, the stuck SQL * plus) the current search is in the waiting status. After the last commit or rollback of the first session, the retrieval result in the second session is automatically jumped out, and the data is also locked.

However, if your search statement in the second session is as follows: select * from test where id = 10, that is, there is no lock data statement such as for update, it will not cause blocking.

(2) for update nowait

In another case, when the database data is locked, that is, after the SQL statement for update is executed, what will happen after we execute for update nowait in another session.

For example, the following SQL statement:

Select * from test where id = 10 for update nowait

Because this statement is formulated to use nowait Method for retrieval, so when the data is found to be locked by other sessions, it will quickly return the ORA-00054 error, the content is the resource is busy, however, it specifies that resources are obtained in NOWAIT mode. Therefore, in the program, we can use nowait to quickly determine whether the current data is locked. If the data is locked, we need to take appropriate business measures for processing.

Another problem here is what we will do when we lock the data and update and delete the data.

For example, if we lock the data with id = 10 in the first Session, we execute the following statement in the second session:

Update test set value = 2 where id = 10

At this time, we found that the update statement is stuck here just like the select for update statement. After the first session is locked, the update statement can run normally. After you run the update operation, the data is locked by your update statement. At this time, as long as you do not have a commit after update, other sessions still cannot lock the data for updates and so on.

In short, the pessimistic lock in Oracle is to use the Connection of Oracle to lock the data. In Oracle, the performance loss caused by the use of such row-level locks is very small, but you should pay attention to the Program Logic and do not accidentally create a deadlock for you. In addition, due to the timely locking of data, there is no conflict during data submission, which can save a lot of annoying data conflict processing. The disadvantage is that you must always have a database connection. That is to say, your database connection should be maintained throughout the entire lock-up process.

Compared with the pessimistic lock, we have an optimistic lock. Optimistic locks also said at the beginning, that is, starting from the assumption that no data conflict will be caused, and data conflict detection will be performed at the end of the submission.

In optimistic locks, we have three common practices:

• FirstWhen data is obtained, the entire data is copied to the application, and compared with the data in the current database during submission and the data obtained before the update at the beginning.

If the two data items are the same, it indicates that no conflict can be committed; otherwise, it is a concurrency conflict and needs to be resolved using the business logic.

• Type 2The Optimistic Locking method adopts the version stamp, which is used in Hibernate.

To use the version stamp, you first need to create a new column on the database table with optimistic locks, for example, number type. When each time your data is updated, the number of versions increases by 1.

For example, two sessions operate on a certain data record. Both of them obtain the current data version number 1. After the first session updates the data, it is found that the current data version is 1 at the time of submission, it is the same as the version obtained from the beginning. Submit the job, and add the version number to 1. At this time, the current data version is 2. When the second session also updates the data submission, it finds that the version number in the database is 2, which is inconsistent with the version number obtained from the first session. In this case, it is known that someone else has updated the data, in this case, the service is processed again, for example, the entire Transaction is Rollback.

When using the version stamp, you can use the version stamp verification on the application side, or Trigger on the database side for verification. However, the performance overhead of the database Trigger is still relatively large, so we recommend that you do not use the Trigger if you can verify it on the application side.

• The third method is similar to the second method, that is, adding a Table Column. However, this column uses the timestamp type to store the last update time of the data.

After Oracle9i, you can use the new data type, that is, timestamp with time zone, as the timestamp. The data precision of Timestamp is the highest in Oracle's time type, accurate to microseconds (not comparable to that of nanoseconds). Generally, in addition, the database processing time and the thinking and action time are very good at the microsecond level. In fact, as long as the precision is accurate to milliseconds or even seconds, there should be no problem.

Similar to the version stamp just now, it is also compared with the timestamp obtained before updating and submitting the data in the current database. If the timestamp is consistent, OK, otherwise, a version conflict occurs. If you do not want to write the code in the program or cannot write the code in the existing program for other reasons, you can also write the optimistic lock logic of the timestamp in the Trigger or stored procedure.

Iii. Five isolation levels of transactions

The Isolation attribute supports five transaction settings as follows:

(1) DEFAULT

The database isolation level (default) is determined by the DBA's default settings.

(2) READ_UNCOMMITTED

This is the lowest isolation level of the transaction. It allows others to see the uncommitted data of this transaction.

There will be dirty reads, non-repeated reads, and Phantom reads (the lowest isolation level, high concurrency ).

(3) READ_COMMITTED

Ensure that the modified data of one transaction can be read by another transaction only after it is committed. Another transaction cannot read the uncommitted data of the transaction.

It can avoid dirty reads, but there may be non-repeated reads and phantom read problems (locking the row being read ).

(4) REPEATABLE_READ

It can prevent dirty reads and non-repeated reads, but may cause Phantom reads (locking all rows read ).

(5) SERIALIZABLE

This is the most costly but most reliable transaction isolation level, and the transaction is processed in sequence.

Make sure that all situations do not occur (Lock table ).