Oracle database pessimistic lock and optimistic lock detailed

Data locking is divided into two methods, the first one is called pessimistic lock, the second is called optimistic lock. What is pessimistic lock, pessimistic lock as the name implies, is the conflict of data to take a pessimistic attitude, that is, assuming that the data will certainly conflict, so when the data began to read the data locked. The optimistic lock is that the data generally does not cause conflict, so when the data is submitted to update the time, the data will be formally conflicting or not detected, if a conflict is found, let the user return the wrong information, let the user decide how to do.

Let's start with the pessimistic lock. In many other databases such as SQL Server, data locking is usually a page-level lock, that is, the data in a table is a serialization of the update insertion mechanism, at any time the same table will only plug in 1 of data, the other want to insert the data to wait until this piece of data inserted in order to insert after. The result is a decrease in performance, when multi-user concurrent access, when a table for frequent operation, will find that the response is very inefficient, the database is often in a state of suspended animation. Oracle uses row-level locks, only locks the data that it wants to lock, and the rest of the data is irrelevant, so there's basically no impact when inserting data into an Oracle table.

Note: For pessimistic locking is more likely to be for concurrency, and generally in our application with optimistic locking enough.

Oracle's pessimistic lock requires an existing connection, divided into two ways, from the difference between SQL statements, that is, a for update, a for update nowait form. For example, let's look at an example. First set up a database table for testing.

Here is the code snippet: CREATE TABLE TEST (id,name,location,value,constraint test_pk PRIMARY KEY (ID)) as SELECT deptno, Dname, loc, 1 from SCOTT.DEP T

Here we use the Oracle's sample Scott user's table to copy the data into our test table. First, let's look at the for Update lock mode. First we execute the following select FOR UPDATE statement.

Here is the code snippet: SELECT * FROM test where id = Ten for update

After this search statement is locked, then open another Sql*plus window to operate, and then the above SQL statement execution, you will find that Sqlplus seems to have died there, as if the data can not retrieve the appearance, but also do not return any results, it belongs to the feeling of the card there. What is the reason for this time, is that the first session in the beginning of the Select FOR UPDATE statement to lock the data. Since the locking mechanism here is the state of wait (as long as it does not indicate nowait that is wait), the current retrieval in the second session (that is, the stuck sql*plus) is in a waiting state. When the first session is last commit or rollback, the result of the second session is automatically jumped out and the data is locked. But if you have a second session, your search statement looks like this

Here is the code snippet: SELECT * FROM Test where id = 10

That is, there is no such thing as a for update to lock the data, it will not cause blocking. Another scenario is when the database data is locked, that is, after executing the SQL for update, what happens after we execute for update nowait in the other session. For example, the following SQL statement. Since this statement is made using the NOWAIT method for retrieval, it quickly returns a ORA-00054 error when the data is found to be locked by another session, with the content being busy, but specifying the NOWAIT way to get the resource. So in the program we can use the NoWait method to quickly determine whether the current data is locked, if locked, it is necessary to take appropriate business measures to deal with.

Here is the code snippet: SELECT * FROM test where id = Ten for update nowait

The other problem here is, when we lock up the data, what will we do to update and delete the data? For example, we let the first session lock the id=10 data, and we execute the following statement in the second session.

Here is the code snippet: Update test set value=2 where id = 10

At this point we find the UPDATE statement as if the Select for UPDATE statement also stops here, and the update will not function until the first session is unlocked. When you update the operation, the data is locked by your update statement, this time as long as you do not have a commit after the update, the other session can not lock the data to update and so on.

In summary, the pessimistic lock in Oracle is the use of Oracle's connection to lock data. In Oracle, the performance penalty with this row-level lock is very small, just pay attention to the program logic, do not give you accidentally into a deadlock just fine. And because of the timely data locking, when the data submitted at the time of the conflict, you can save a lot of annoying data conflict processing. The downside is that you have to always have a database connection, which means that your database joins are always held while the lock is locked until the last release. In contrast to the pessimistic lock, we have an optimistic lock. Optimistic lock at the outset, it is the assumption that there will be no data collisions at the outset, and then the data conflict detection at the time of the last commit.

In the optimistic lock, we have 3 common practices to achieve:

[1] The first is when the data is obtained when the entire data is copied into the application, at the time of submission than the data in the current database and the beginning of the update before the data obtained. When two data is found to be identical, it means that no conflict can be committed, otherwise it is a concurrency conflict that needs to be resolved with business logic.

[2] The second optimistic lock approach is to use the version stamp, which is used in hibernate. With a version stamp, you first need to create a new column on the database table that you have an optimistic lock on, such as number, and when your data is updated every time, the version will increase by 1. For example, there are also 2 sessions that operate on a single piece of data. Both are taken to the current data version number is 1, when the first session of the data update, at the time of submission to view the current version of the data is also 1, and the same version that you first fetch. It is formally submitted, then the version number is increased by 1, this time the current version of the data is 2.

When the second session also updated the data submitted, found that the database version is 2, and the beginning of the session to get the version number is inconsistent, know that others have updated this data, this time to do business processing, such as the entire transaction rollback and so on operations. When using a version stamp, you can use the validation of the version stamp on the application side, or use the trigger (trigger) on the database side to authenticate. However, the trigger performance cost of the database is still relatively large, so it can be verified on the application side or not recommended trigger.

[3] The third approach is somewhat similar to the second approach, which is the addition of a table column, but this time the column is the timestamp type that stores the last updated data. The new data type, the timestamp with time zone type, can be used for timestamps after oracle9i. This timestamp data precision is the highest in Oracle's time type, accurate to microseconds (not yet to the nanosecond level), in general, plus the database processing time and the person's thought action time, the microsecond level is very very enough, in fact, as long as the accuracy of milliseconds or even seconds should be no problem. Similar to the previous version of the stamp, but also at the time of the update submission to check the current database of the time stamp and the update before the time stamp to compare, if the same is OK, otherwise the version conflict. If you do not want to write code in the program, or for other reasons can not write the code in the existing program, it is also possible to write this timestamp optimistic lock logic in the trigger or stored procedures.

Oracle database pessimistic lock and optimistic lock detailed