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

1 preface

Deadlock and lock performance should be considered in database large concurrency operation. Seeing that most of the information on the Internet is vague (especially updating locks), a brief explanation is made here for the convenience of the following description, where T1 is used for a database to execute a request, T2 for another request, or T1 for one thread and T2 for another thread. T3Magee T4, and so on. Take SQL Server (2005) as an example.

2 types of locks

Shared lock (Shared lock).

Example 1:--T1: select * from table (imagine that it takes as long as an hour to execute Imagine the following sql statements like this) T2: update table set column1='hello' procedure: T1 running (with shared lock) T2 running If T1 has not finished executing T2 etc. Else lock is released T2 executes endifT2 because T2 tries to add an exclusive lock to the table table before executing update, and the database stipulates that shared lock and exclusive lock cannot coexist on the same resource at the same time. So T2 must wait for T1 to finish executing and release the shared lock before it can add an exclusive lock before it can start executing the update statement. Example 2:--T1: select * from tableT2: select * from table here T2 does not have to wait for T1 to finish, but can be executed immediately. Analysis: when T1 runs, table is locked, for example, lockAT2 is run, and a shared lock is added to table, such as lockB. Two locks can exist on the same resource at the same time (as on the same table). This is called a shared lock compatible with a shared lock. This means that the shared lock does not prevent other session from reading resources at the same time, but prevents other session update examples such as 3:--T1: select * from tableT2: select * from tableT3: update table set column1='hello' this time, T2 does not have to wait for T1 to run, while T3 has to wait for T1 and T2 to run. Because T3 must wait for the shared locks of T1 and T2 to be released before adding an exclusive lock and then performing a update operation. Example 4: (occurrence of deadlock)-- T1:begin transelect * from table (holdlock) (holdlock means to add a shared lock until the end of the thing) update table set column1='hello'T2:begin transelect * from table (holdlock) update table set column1='world' assumes that T1 and T2 simultaneously reach select,T1 to add a shared lock to table, and T2 also adds a shared lock. When the select of T1 is finished and the update is ready to be executed, according to the locking mechanism, the shared lock of T1 needs to be upgraded to an exclusive lock to execute the next update. Before upgrading the exclusive lock, you must wait for other shared locks on the table to be released, but because the shared lock such as holdlock is only released after the transaction, it leads to T1 and so on because the T2 shared lock is not released (wait for T2 to release the shared lock so that you can upgrade to an exclusive lock). In the same way, T2 and so on are caused because the shared lock of T1 is not released. There's a deadlock. Although the statement such as 5:--T1:begin tranupdate table set column1='hello' where id=10T2:begin tranupdate table set column1='world' where id=20 is the most common, many people think it has the opportunity to produce deadlocks, but it actually depends on the situation. If id is the primary key with an index on it, T1 will find the record (id=10 record) at once. Then add an exclusive lock to the record, T2, again, locate the record through the index at once, and then add an exclusive lock to the id=20 record, so that T1 and T2 update each other and do not affect each other. T2 doesn't have to wait. But if id is a normal column, there is no index. So when T1 adds an exclusive lock to the id=10 row, T2 needs to scan the full table in order to find id=20, then the table will be pre-loaded with a shared lock or update lock or exclusive lock (depending on the database execution policy and manner, for example, the first execution and the second execution of the database execution strategy will be different). However, because T1 has added an exclusive lock to a record, the full table scan of T2 cannot be carried out, which leads to T2 waiting. How to solve the deadlock? One way is as follows: for example, 6:--T1:begin transelect * from table (xlock) (xlock means to add an exclusive lock directly to the table) update table set column1='hello'T2:begin transelect * from table (xlock) update table set column1='world', when the select of T1 is executed, the exclusive lock is directly added to the table, and T2 is executed when select You need to wait for the T1 thing to complete before it can be executed. Ruled out a deadlock. But when the third user comes to execute a query, it also has to wait because of the existence of an exclusive lock, and so does the fourth and fifth user. In the case of large concurrency, it is too friendly for everyone to wait, so update locks are introduced here.

Update lock (Update lock)

In order to solve the deadlock, update lock is introduced. For example, 7:--T1:begin transelect * from table (updlock) (update lock) update table set column1='hello'T2:begin transelect * from table (updlock) update table set column1='world' update lock means: "I just want to read it now, and others can read it, but I may update it in the future." I have qualified from a shared lock (for reading) to an exclusive lock (for updating). Only one update lock for a thing can be qualified. T1 executes select with update lock. T2 is running, ready to add an update lock, but found that there is already an update lock there, so we have to wait. When there were user3 and user4... later When you need to query the data in the table table, it is not blocked because the select of T1 is being executed, and you can still query it, which is more efficient than example 6. Example 8:--T1: select * from table (updlock) (update lock) T2: select * from table (updlock) (wait until T1 releases update lock, because there cannot be two update locks on the same resource at the same time) T3: select * from table (add shared lock, but do not have to wait for updlock to release This example shows that shared locks and update locks can be on the same resource at the same time. This is called a shared lock and an update lock is compatible. For example, 9:--T1:beginselect * from table (updlock) (update lock) update table set column1='hello' (key point: when T1 does update, you don't need to wait for T2 to release anything, but directly upgrade the update lock to exclusive lock. Then execute update) T2:beginselect * from table (the update lock added by T1 does not affect the read of T2) update table set column1='world' (the update of T2 needs to wait for the update of T1 to finish) We use this example to deepen the understanding of the update lock. In the first case: T1 arrives first, T2 arrives immediately. In this case, T1 first adds an update lock to the table, T2 adds a shared lock to the table, assuming that the select of T2 is executed first, ready to execute update, and finds that there is an update lock, T2 and so on. At this point, the T1 executes the select, prepares to execute the update, upgrades the update lock to an exclusive lock, then executes the update, completes the execution, the transaction ends, releases the lock, and T2 executes update. The second case: T2 first, T1 close; in this case, T2 first adds a shared lock to the table, and after T1 arrives, T1 adds an update lock to the table, assuming that T2 select ends first, prepares the update, and finds that there is an updated lock, then wait, and the later steps are the same as in the first case. This example shows that exclusive locks and update locks are incompatible and cannot be added to the same subresource at the same time.

Exclusive lock (exclusive lock, Exclusive Locks)

This is simple, that is, other transactions can neither read nor change the resources locked by exclusive locks. Example 10T1: update table set column1='hello' where id1000 assumes that T1 reaches first and T2 then arrives. During this process, when T1 executes to id10T1, it will lock the first page first, then release the lock after reading the first page, then lock the second page, and so on. Assuming that the first 10 rows of records happen to be one page (of course, it is generally impossible to have only 10 rows per page), then T1 does not block T2 updates when it executes to the first page of the query. For example, when 18:--T1: select * from table (rowlock) T2: update table set column1='hello' where id=10T1 executes, each row is shared locked, read, then released, and then locked on the next line When T2 executes, it attempts to lock the row of id=10, and T2 can perform the update operation smoothly as long as the row is not locked by T1. For example, 19:--T1: select * from table (tablock) T2: update table set column1='hello' where id = 10T1 executes and adds a shared lock to the entire table. T1 must be fully queried before T2 can allow locking and start updating. The above three examples specify the granularity of the lock manually, and you can also set the granularity of the lock automatically by setting the transaction isolation level. With different levels of transaction isolation, the database will have different locking strategies (such as what type of lock to add and what granularity to add). Please refer to the online manual for details. 5 the priority of the isolation level of locks and transactions is manually specified. For example, 20:--T1: GO SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO BEGIN TRANSACTION SELECT * FROM table (NOLOCK) GOT2: update table set column1='hello' where id=10T1 is the highest level of transaction isolation, serial locks The database system should have automatically added table-level locks to the subsequent select statements, but because the NOLOCK was specified manually, the select statement did not add any locks, so T2 did not have any blocking. 6 other important Hint of the database and their differences 1) holdlock adds a shared lock to the table, and the thing is not finished, the shared lock is not released. 2) tablock adds a shared lock to the table. As long as the statement is not completed, the shared lock will not be released. Different from holdlock, see the following example: example 21-T1: begin tran select * from table (tablock) T2: begin tran update table set column1='hello' where id = 10 T1 releases the shared lock after executing select, and then T2 can execute update. This is called tablock. Let's take a look at holdlock example 22-T1: begin tran select * from table (holdlock) T2: begin tran update table set column1='hello' where id = 10 T1 after executing select, the shared lock will still not be released and will still be held by hold, and T2 will therefore have to wait and cannot update. When T1 finally executes commit or rollback to show that this thing is over, T2 gets the power of execution. 3) TABLOCKX adds an exclusive lock to the table example 23:-T1: select * from table (tablockx) (forcibly add an exclusive lock) other session will not be able to read and update the table, unless T1 is finished, the exclusive lock will be automatically released. Example 24:-- T1: begin tran select * from table (tablockx) this time, the select execution alone is not enough. The exclusive lock will not be released until the whole thing is completed (after the commit or rollback has been executed). 4) what is the difference between xlock and tablockx with exclusive lock? It can be used like this, example 25:-- select * from table (xlock paglock) adds an exclusive lock to page while TABLELOCX cannot. Xlock can also be used like this: the select * from table (xlock tablock) effect is equivalent to the timeout wait of the select * from table (tablockx) 7 lock

Example 26

SET LOCK_TIMEOUT 4000 is used to set the lock wait time in milliseconds, and 4000 means that you can wait 4 seconds to view the lock timeout setting of the current session with select @ @ LOCK_TIMEOUT. -1 means waiting forever. T1: begin tran udpate table set column1='hello' where id = 10T2: set lock_timeout 4000 select * from table wehre id = 10

When T2 executes, it waits for T1 to release the exclusive lock for 4 seconds. If T1 has not released the exclusive lock, T2 throws an exception: Lock request time out period exceeded.

8 attached: compatibility table for various locks | Requested mode | IS | S | U | IX | SIX | X | Intent shared (IS) | Yes | Yes | Yes | Yes | Yes | No | | Shared (S) | Yes | Yes | Yes | No | Update (U) | Yes | Yes | No | No | No | No | | Intent exclusive (IX) | Yes | No | No | Yes | No | No | | Shared with intent exclusive (SIX) | Yes | No | Exclusive (X) | No | 9 how to improve concurrency efficiency

Pessimistic lock: realized by using the locking mechanism of the database itself. Through the understanding of the database lock, we can comprehensively use the transaction isolation level and reasonable manual lock according to the specific business situation, such as reducing the granularity of the lock to reduce concurrent waiting.

Optimistic locks: use programs to deal with concurrency. The principles are easy to understand and can be understood at a glance. There are about three ways

Add the version number to the record.

Time-stamp the record.

Read the data to be updated in advance and compare it afterwards.

Whether it is the locking mechanism of the database system itself or the optimistic locking mechanism at the business data level, it is essentially the reading, writing and judgment of the status bit.

Get [download address]

Springmvc4 mybatis integration framework source code bootstrap html5 mysql oracle sqlsever spring SSM

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.