Row-level locks balance data consistency and efficiency

During database maintenance, the database administrator may have this requirement. The Administrator requires that some data in a table be changed only by himself or a specific user.
Enough query. For data verification purposes, users must temporarily refuse to modify certain data. This is like protecting some rows in an Excel table. When a user accesses this table, other users can only
View the content in the Table. Other users cannot change or delete the protected data.

Can this requirement be fulfilled in Oracle databases? There are multiple ways to achieve this in the database. For example, set the tablespace of the table to read-only, or change the permission
Complete. set other users to have only read-only permission on the table. However, these methods are all a little tricky. If a tablespace is set to read-only, no user can change the data.
If there are many tables in the tablespace, there will be many affected tables. For example, permission control can reduce the scope, but the database administrator may only need to protect some data in the table. That is, apart from the protected data
Other users can still access the service normally, such as deleting and updating the service. Therefore, although the above two methods can ultimately achieve this requirement, it will greatly affect other
The user's normal use. Therefore, these two methods are not recommended by the author. I recommend that you use row-level locks. Next I will elaborate on this method in detail, with special emphasis on
Meaning.

For example, there are two users in the database: System and Victor, and there is a dtm_pro table. The database administrator wants to protect some data in this table. If you want
Records with recid1 less than 450 cannot be changed by other users. Other users cannot delete or modify these records. Database Management
The Administrator requires that records with recid1 greater than or equal to 450 can be deleted or changed normally. As shown in, this is the specific implementation process.

　　

We can see that in the query statement, the author adds for update of thread # Wait
5 clause. This clause can implement row-level locks to protect data in the result set.

1. Features of Row-level locks.

In Oracle databases, resources are shared through locks. To put it simply, a lock is an additional method of controlling each other's data when multiple users access the same shared resource at the same time.
Permission control. In Oracle databases, locks can be divided into row-level locks and table-level locks. I want to talk about Row-level locks. As the name implies, a row-Level Lock is a control mechanism that locks some rows in a table. Understanding
In this row-Level Lock, I think you need to pay attention to the following aspects.

First, you must note that the row-Level Lock is called because the affected range is only partial rows, not all rows in the table. As shown in the preceding example, the affected rows are restricted by the where condition.
. That is, only the result set queried by the where condition is locked. Other rows are not affected. Other users can delete or change rows that are not locked. In this way, you can apply the lock to other
The adverse impact of database operations on the user is minimized. In this way, both efficiency and security can be achieved.

The second thing to note is that row-level locks are exclusive. Assume that the above two Users have the right to lock the table dtm_pro. In the above example, the user system first
Recid1 is locked for records smaller than 450. At this time, other users, such as Victor, cannot lock these records in this table even if they have this right. Because row-level locks are exclusive. If you want
Locks will cause deadlocks. In this case, the deadlock will exist for a long time unless the database administrator uses the statement to unlock it.

Ii. Classification of Row-level locks.

Row-level locks in Oracle databases are mainly divided into two types: Automatic row-level locks. This automatic row-Level Lock does not need to be used in the statement for update.
Statement database will automatically lock the method. This automatic row-Level Lock is mainly triggered by some specific statements. If you use update to update some data in a table, the transaction is not submitted after the update is complete.
The database automatically locks the affected records to achieve data consistency. Although this row-Level Lock is automatically added to the database, you need to manually unlock it when unlocking it. Add
Enter commit (write the updated data to the database) or rollback (cancel the Data Change) to manually unlock. Therefore, when writing statements such as delete or update, do not forget to add
The last unlock statement. Otherwise, a deadlock may occur when others update or delete these records.

The second type is manual locking. Automatic lock is only valid for some update or delete operation statements, but not for common query statements. You can use select statements to query data.
The related records are not locked. Now, if you need to force lock on some data queried, you need to use for update
The statement manually locks related records. After manual locking, the locking effect is the same as that of the database system. After the lock is completed, the locked user can delete, update, and query related records.
Other users only have the query permission for these records. Similarly, after the related work is completed, the user should even unlock the lock. The system will not automatically unlock this.

It can be seen from this that the lock is not necessarily a specific object of the database system. When users or database administrators find it necessary, they can also manually lock some records to prevent other users
Make changes to these. When a user finds that some data is faulty and there is a possibility of fraud, the suspected data is locked to prevent the user from tampering with the data. In this case
Row-level locks are very useful. At this time, only the specific records are locked, without affecting the use of other data in the table. However, the database administrator must note that the lock is a mechanism to protect data security.
Efficiency often conflicts with each other. Therefore, when using locks, you need to pay attention to the negative impact on database operation efficiency and user work efficiency.

3. considerations during the use of Row-level locks.

Although row-level locks can protect part of the data, they are different from the efficiency of the database. Therefore, when managing row-level locks, the database administrator must pay attention to
Negative impact of efficiency. Specifically, I think the following three points of attention should be paid to by everyone.

First, row-level locks are exclusive locks. For this exclusive lock, users need to understand its two meanings. First, other users cannot lock locked records. As shown in the preceding example
User System has locked two records. At this time, user Victor cannot modify or delete data, nor re-lock these records. This is the meaning of exclusive lock. Only one user
You can lock a specific record. Second, other users can still lock data that has been locked. In the above example, although the user system has locked two of the records, the user
Victor can still use row-level locks to lock other records. Therefore, this exclusive lock is only for a specific record, not for the entire table. Different users can
Add row-Level Lock to the record. At this time, the record cannot be repeated.

Second, pay attention to the deadlock issue. As shown in, the user system has locked some records. If user Victor also locks these records
A deadlock occurs because row-level locks are exclusive locks. When a deadlock occurs, user Victor's session will remain waiting for voluntary unlocking. If the user's system has not been unlocked for one hundred years, then
Victor will keep waiting. Obviously, this is not what the database administrator wants to see. Therefore, it is best to add the wati clause when manually adding row-level locks. This clause is used to control when
The time when the session is waiting to be unlocked. If the time is exceeded and the other party has not unlocked the resource, the system will automatically give up the operation and return the error message to the user: the resource is occupied, and the operation is performed
Current wait error. This clause can effectively avoid the adverse impact of deadlocks on user sessions. You can set the waiting time according to your needs. However, do not
Time is too long.

Third, when implementing row-level locks, it is best to use the where Condition Statement to limit the affected records. As in the above example, I added the where Condition Statement, which is finally affected by the row-Level Lock.
There are only two records. If this where statement is not added, the entire table's records will be locked. This will have a great impact on the operations of other users.
Update and delete rows. Therefore, when using row-level locks, it is best to minimize the scope, that is, to only apply row-level locks to necessary records. In this way, while protecting data consistency
To minimize the impact, improve database operation efficiency, and achieve a balance between efficiency and security.