Shulou(Shulou.com)06/01 Report--

How the database locking mechanism is, many novices are not very clear about it. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can gain something.

There will be a lot of problems when the database is concurrent, but sometimes it will improve the efficiency of the program, and sometimes it will produce very serious BUG. In order to solve the problem caused by concurrency, the underlying database uses the mechanism of database lock to solve the problem of concurrency, which is similar to the synchronous lock in Java. Although the locking mechanisms of different databases may be different at the bottom, their implementation principles are all the same. Let's take a look at the underlying implementation principle of the database locking mechanism.

Classification of locks

According to the object locked, it can be divided into table locking and row locking. The difference is that the former locks the entire table, while the latter locks specific rows in the table. From the relationship of concurrent transaction locking, it can be divided into shared locking and exclusive locking. The difference between them is that shared locks prevent exclusive locks, but allow other shared locks. Exclusive locking prevents both other exclusive locks and other shared locks.

Application scenario

According to the lock classification described above, if we want to make data changes to the database, that is, UPDATE, then the database must add an exclusive lock on the changed row, which is the only way to ensure the data security of the database. In addition, INSERT, DELETE and other operations implicitly add row locks. Let's take the Oracle database as an example to focus on database locking.

Oracle lock

Row sharing locks: row sharing locks can be implicitly obtained through SELECT FOR UPDATE statements. Row sharing locks can also be explicitly obtained through LOCK TABLE IN ROW SHARE MQDE statements in Oracle. The characteristic of a row sharing lock is that it does not prevent changes to data rows, but it prevents other operations from acquiring exclusive locks. Row sharing locks also allow multiple concurrent row sharing and row exclusive locks, as well as data table sharing or shared row exclusive locking.

Row exclusive locks: row exclusive locks can be acquired implicitly through INSERT, UPDATE, and DELETE statements, or explicitly through LOCK TABLE IN ROW EXCLUSIVE MODE statements. The characteristic of a row exclusive lock is that it prevents other operations from acquiring a shared lock, shared exclusive lock, or exclusive lock.

Table shared lock: can be obtained through LOCK TABLE IN SHARE MODE display. The characteristic of table shared lock is that it can prevent other operations from acquiring row exclusive lock or other table shared row exclusive lock or table exclusive lock. It allows multiple row shared and table shared locks in the table. The lock gives the session consistent access to the table at the transaction level, because other sessions cannot change the locked table until the user commits or rolls back the transaction and releases the lock on the table.

Table sharing row monopoly: can be obtained through the LOCK TABLE IN SHARE ROW EXCLUSIVE MODE statement. The feature of table shared row monopoly is that it prevents other sessions from acquiring a table shared, row exclusive, or table exclusive lock, which allows other rows to share locks. This lock is similar to a table shared lock, except that it can only place one table shared row exclusive lock on one table at a time. For example, if A session owns the lock, B session can perform SELECT FOR UPDATE operations, but if B session tries to update the selected row, it needs to wait.

Table monopoly: can be obtained through the LOCK TABLE IN EXCLUSIVE MODE display. The exclusive feature of the table is that it prevents any other locks on the table by other sessions.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.