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

This article will explain in detail the reasons why the database needs locking mechanism and what is the type of locking mechanism. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Why does the database need a locking mechanism? What locking mechanisms are there?

Why lock it?

The database is a shared resource used by multiple users, such as a user table t_user, and the person in front of the two browsers logs in to the same account and changes the phone number. When multiple users access data concurrently, multiple transactions will access the same data at the same time in the database. If there is no control over concurrent operations, incorrect data may be read and stored, the consistency of the database may be destroyed (dirty reading, non-repeatable reading, phantom reading, etc.), and deadlocks may occur. In order to solve this problem, locking is a very important technology, and it is a good scheme for database concurrency control. To put it simply, before a transaction executing a sql statement wants to manipulate a table record, it sends a request to the database to lock the recordset you access, and other transactions cannot update the data until the transaction releases the lock.

What locks do you have?

Locks include row-level locks, table-level locks, pessimistic locks and optimistic locks.

1. Row-level lock: a lock that prevents another transaction from modifying this row

INSERT, UPDATE, DELETE, SELECT... FOR UPDATE [OF columns] [WAIT n | NOWAIT]; SELECT... FOR UPDATE statement that allows the user to lock more than one record at a time for updating.

Use commit or rollback to release the lock.

MySql's innodb storage engine defaults to row-level locks. Features: large unlocking, slow locking; deadlock will occur; locking granularity is the smallest, the probability of lock conflict is the lowest, and the degree of concurrency is the highest. It is suitable for applications where a large number of different data are updated according to the index, while there are concurrent queries, such as some online transaction processing systems.

two。 Table-level locks: 5

1) Row sharing (ROW SHARE)-exclusive locking of the table is prohibited, similar to row exclusivity, except that other transactions can also add any exclusive locks to this table. (except for exclusive)

2) Row exclusive (ROW EXCLUSIVE)-exclusive locks and shared locks are prohibited. Other transactions can still perform queries, inserts, updates, deletions, or locks on rows of data in the same table concurrently, but there can be no other exclusive locks.

5) EXCLUSIVE-the most restrictive table lock that only allows other users to query the rows of the table. Modification and locking of tables are prohibited

Row-level locks and table-level locks are distinguished according to the granularity of locks. Row records and tables are resources, and locks act on these resources. If the granularity is relatively small (such as row-level locks), you can increase the concurrency of the system but require a larger system overhead, which will affect performance and lead to deadlocks, because if the granularity is small, the number of locks to operate will increase. If it works on a table, it has large granularity, low overhead, fewer locks to maintain, and no deadlocks, but concurrency is quite expensive because locking the entire table restricts other transactions' access to other records in the table.

Pessimistic lock:

Pessimistic Lock, as its name suggests, refers to a conservative and pessimistic view of data being modified by the outside world (including other current transactions of the system, as well as transactions from external systems). Every time a transaction goes to manipulate data, it assumes that other transactions will modify the data that needs to be accessed, so locks, row locks, table locks, read locks, write locks, etc., are required before access, and are locked before the operation is done. The data is locked during the whole data processing process. The realization of pessimistic locking often depends on the locking mechanism provided by the database (only the locking mechanism provided by the database layer can really ensure the exclusivity of data access, otherwise, even if the locking mechanism is implemented in this system, there is no guarantee that the external system will not modify the data.

A typical pessimistic lock call that relies on the database: the sql statement select * from account where name= "Erica" for update locks all records in the account table that meet the retrieval criteria (name= "Erica"). Before this transaction is committed (locks during the transaction are released when the transaction is committed), these records cannot be modified by the outside world.

Hibernate pessimistic Lock implementation: based on Database Lock Mechanism

Query q=Session.createQuery ("select * from t_profit where amount > 10000")

Q.setLockMode ("Profit", LockMode.UPGRADE); / / Profit is an alias for the Profit class

List ps=q.list ()

Sql:select executed. From t_profit where amount > 10000 for update.

LockMode.NONE: no lock mechanism

Automatically acquire pessimistic lock when LockMode.WRITE:insert,update record

LockMode.READ automatically acquires pessimistic locks when reading

LockMode.UPGRADE: locking using the for update clause of the database

LockMode.UPGRADE_NOWAIT:oracle-specific implementation, locked with the for update nowait clause of oracle

Optimistic lock:

Optimistic Lock, contrary to joys and sorrows, each time a transaction manipulates the data, it assumes that other transactions will not modify the data that need to be accessed, so it is not required to be locked before the access, just to determine whether anyone else has modified the data during the access. It is suitable for multi-read applications. It is better when there are really fewer conflicts, so as to save the overhead and improve the throughput. But if conflicts really occur frequently, you have to judge the performance of retry each time, and the sorrowful lock is better at this time. If the database provides a mechanism similar to write_condition, it is actually the optimistic locks provided.

Most of its implementation is based on the data version versin recording mechanism. For example:

1. The income statement t_profit has a version field with a current value of 1, while the total asset balance field (balance) is $10000

two。 Operator A reads version=1 and subtracts 2000, 000 from total assets.

3.A before the end of the operation, operator B also reads the version=1, minus 5000 million-5000 million.

4. After the operation is completed, add 1 to version and modify it to 2. Subtract 2000 from the total assets and submit it to update the database. The update is successful.

5.B operation, also add version plus 1, modify to 2, subtract 5000 from the total assets and submit to update the database. At this time, it is found that the version is already 2, such as the version after B modification plus 1, which does not meet the optimistic locking policy: "the submitted version must be larger than the record of the current version before it can be implemented." Therefore, the operation request of B is rejected, which avoids the possibility that the result of B's modification of the old data of version=1 overwrites the result of An operation. If there is no optimistic lock, then A minus 2000 leaves 8000, but B operates with 10000-5000 remaining 5000. If B's submission is successful, the total asset balance is 5000, but the actual situation should be 8000-5000-3000. There is a discrepancy between the total balance sheet record and the actual expenditure.

Hibernate's implementation of optimistic locks:

About the reason why the database needs the locking mechanism and what is the type of locking mechanism is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.