Oracle Database pessimistic locks and optimistic locks

OracleDatabasePessimistic lockAndOptimistic lockIs what we will introduce in this article. Sometimes, for maximum performance, databases generally have a concurrency mechanism, but the problem is Data Access conflicts. To solve this problem, most databases Use Data Locking.

There are two methods to lock data: Pessimistic lock and optimistic lock. What is a pessimistic lock? A pessimistic lock, as its name implies, is a pessimistic attitude towards data conflicts. That is to say, assume that data will conflict, therefore, the data is locked when the data is read. The optimistic lock generally considers that data does not cause conflicts. Therefore, when the data is submitted for update, the system will officially detect whether or not the data conflicts exist. If any conflict is found, in this case, the user is asked to return error information and decide how to do it.

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. First, let's take a look at the locking method for update. First, execute 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, if you do not use the for update statement to lock data, it will not cause blocking. 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. 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.

Select * from test where id = 10 for update nowait

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 will 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:

[1] The first approach is to copy the entire data to the application when the data is obtained, and compare the data obtained before the current database and the data obtained before the update at the time of submission. 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.

[2] The second optimistic lock method is to use 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.

[3] The third approach is a bit similar to the second approach, 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.

We will introduce the pessimistic and optimistic locks of Oracle databases here. I hope this introduction will be helpful to you!