Database lock mechanism and lock mechanism

Database lock mechanism and lock mechanism
I. Why is it necessary to unlock 1.1 deadlocks? 1.2 concurrency issues lead to incorrect data reading and storage, undermining data consistency?

• Loss update: When two or more transactions select the same row and update the row based on the originally selected value, because each transaction does not know the existence of other transactions, the update will be lost. The last update will overwrite the updates made by other firms. For example, two editors make an electronic copy of the same document. Each editor changes its copy independently and saves the changed copy to overwrite the original document. The edited person who saves the change copy finally overwrites the changes made by another edited person. This problem can be avoided if another editor cannot access the same file before it completes and submits the transaction.
• Dirty read: a transaction is modifying a record. before the transaction is completed and committed, the data of this record is in an inconsistent state, another transaction also reads the same record. If no control is imposed, the second transaction reads the "dirty" data and performs further processing accordingly, the uncommitted data dependency is generated. This phenomenon is vividly called "Dirty reading ".
• Repeatable read: A transaction reads data that has been read before at a certain time after reading some data, it is found that the read data has changed, or some records have been deleted! This phenomenon is called "repeatable reading ".
• Phantom read: A transaction re-reads previously retrieved data based on the same query conditions, but finds that other transactions Insert new data that meets the query conditions, this phenomenon is called phantom reading ".

Ii. Lock classification 2.1 database dimension

• Shared lock: used for operations without changing or updating data (read-only operation ). The shared lock allows concurrent transactions to read the same resource. When a shared lock exists on the data resource, no other transactions can modify the data.
• Exclusive lock: used for data modification to ensure that the same data is not updated multiple times at the same time. When an exclusive lock exists on the resource, other transactions are not allowed to lock the resource. When there are other locks on the resource, the exclusive lock cannot be applied to it.

PS: only the shared locks and shared locks are compatible with each other. The shared locks and exclusive locks are not compatible with each other.

• Update lock

2.2 database lock mechanism

DBMS	SELECT	UPDATE	INSERT	DELETE
MySQL (InnoDB)	No lock	Exclusive lock	Exclusive lock	Exclusive lock
SQL SERVER	Shared lock	Update lock	Exclusive lock	Exclusive lock

 

 

 

 

The difference between the two locks is that MySQL query and update operations are not blocked, while SQL SERVER's update lock is not blocked before it is converted into an exclusive lock, to convert an update lock to an exclusive lock, you must wait for the release of the shared lock. After the update lock is converted to an exclusive lock, the queried data must wait for the release of the exclusive lock.

Refer:
[Database lock mechanism] (http://blog.csdn.net/samjustin1/article/details/52210125)
[InnoDB Lock Mechanism] (http://blog.chinaunix.net/uid-24111901-id-2627857.html)
[SQL SERVER lock mechanism] (http://blog.itpub.net/13651903/viewspace-1091664)

2.3 programmer Ideology

• Pessimistic lock

• Optimistic lock

update table set date=1,version=version+1where id=#{id} and version=#{version};

Refer:
[Optimistic lock and pessimistic lock] (http://www.open-open.com/lib/view/open1452046967245.html)

2.4 optimistic lock another implementation method CAS

CAS is an optimistic locking technique. When multiple threads attempt to use CAS to update the same variable at the same time, only one thread can update the value of the variable, while other threads fail, the failed thread will not be suspended, but will be told that the competition failed and can be tried again.

The CAS operation contains three operands: memory location (V), expected original value (A), and new value (B ). If the memory location value matches the expected original value, the processor automatically updates the Location value to the new value. Otherwise, the processor does not perform any operations. In either case, it returns the value of this position before the CAS command. CAS effectively states that "I think location V should contain the value A. if it contains the value, place location B. Otherwise, do not change the location, only tell me the current value of this location." This is in fact the same as the conflict check of optimistic locks + the principle of data update.

Java. util. concurrent (J. U. C) is built on CAS. Compared with synchronized, CAS is a common implementation of non-blocking algorithms. Therefore, J. U. C has greatly improved its performance.

public class AtomicInteger extends Number implements java.io.Serializable {　　private volatile int value; 　　public final int get() { 　　　　return value; 　　} 　　public final int getAndIncrement() { 　　　　for (;;) { 　　　　　　int current = get(); 　　　　　　int next = current + 1; 　　　　　　if (compareAndSet(current, next)) 　　　　　　　　return current; 　　　　} 　　} 　　public final boolean compareAndSet(int expect, int update) { 　　　　return unsafe.compareAndSwapInt(this, valueOffset, expect, update); 　　} }

 

Refer:
[An Implementation of optimistic locks-CAS] (http://www.importnew.com/20472.html)

Iii. Case Analysis 3.1 Migration of preliminary review statistics

• Migration Background: The original statistical method uses the Real-Time count method to obtain statistical data. The problem is that the query is slow and the statistics for a long period of time cannot be obtained (the SQL times out), unable to get a snapshot of the statistical data of a day (the data to be reviewed in the previous day will become the data passed the review today)
• Migration Method: Use the raptor migration platform to scan the audit record table, retrieve the accumulated statistical data, add 1, and then update it to the statistical table. Due to the platform features, the data migration process is highly concurrent. Forced reading and updating will lead to loss of updates. Therefore, CAS is used here.

Step 1:

 

select id,passCount,rejectCount,hideCount,warnCount,waitCount from book.TradeItemAuditCount where type = #{type} and date = #{date} and editor = #{editor} and isDeleted = 0 limit 1

Step 2: [retry upon failure]

update book.TradeItemAuditCount set passCount = #{passCount} , rejectCount = #{rejectCount} , hideCount = #{hideCount} , warnCount = #{warnCount} , waitCount = #{waitCount} , updated = #{updated}where id = #{id} and passCount = #{oldPassCount} and rejectCount = #{oldRejectCount} and hideCount = #{oldHideCount} and warnCount = #{oldWarnCount} and waitCount = #{oldWaitCount}and isDeleted = 0 limit 1

• Processing result: a total of 4335668 data records are scanned for the review results, and the number of retries over 100 is recorded. It is found that most of the retries occur in the update operation, the qps of DB write operations on the task itself is low [no console is required to limit the speed ..]

3.2 goods stock

• The same as Case 1 above, the product inventory is used to add or subtract data records. The inventory is updated as follows:

Update inventory table set stock = stock-1where id =#{ id}

• Directly using the database's exclusive lock can easily avoid the loss of updates caused by concurrency. previously mentioned, only one transaction has the resource exclusive lock, and all concurrent update operations attempt to occupy the exclusive lock of resources, when an exclusive lock exists on the resource, other update operations need to wait for the lock to be released.
• Compared with the solution in Case 1, the solution in Case 2 directly uses the exclusive lock of the MySQL InnoDB update operation, without additional overhead, in Case 1, unnecessary query operations and repeated retry operations seriously affect the performance of data migration. Therefore, Case 1 is the opposite example ..

3.3 Product tagging

• As the qps of Top-hitting targets increases, data loss due to Standard update

{"tags":"16,32,233,22","itemState":1,"hd":"ai:4|nd:18","au":"baoming"}

• Solution

1. Optimistic lock: CAS

update TradeItemset extra=#{extra}where tradeItemId=#{tradeItemId} and extra=#{oldExtra}

Using long strings for updating conditions will affect SQL Performance.

2. Optimistic lock: use the new version field in the data version table to identify the data version. This is used as a check method for data update.

update TradeItemset extra=#{extra} , version=version+1where tradeItemId=#{tradeItemId} and version=#{version}

This scheme is greatly improved, and new fields need to be added to the table, and optimistic locks have the disadvantages: The time cost of retry. Once the concurrency increases, the number of Retries for an update operation also increases, which directly affects the response time of the exposed service. [Limit the number of retries to control the response time of the update operation to a certain extent, but there will still be loss of updates (let the caller retry the operation and share the response time of a single request ?)]

3. pessimistic lock: the root cause of the UPDATE loss is that the data between the query and modification operations is modified by another transaction. A simple UPDATE operation is also an operation that is performed after the first query and modification, no update loss occurs because there is an exclusive lock on the data (SQL server is an update lock) and other modifications are not allowed during execution. In the same way, we can solve the problem by adding an exclusive lock or an update lock to the product records to be marked.

MySQL:

start transaction;SELECT extraFROM TradeItem WHERE tradeItemId=#{tradeItemId}FOR UPDATE;UPDATE TradeItem SET extra = bdo.AddTag(tag,extra)WHERE tradeItemId=#{tradeItemId};commit;

SQL SERVER:

Begin transaction -- start a transaction select extraFROM TradeItem WITH (UPDLOCK) WHERE tradeItemId =#{ tradeItemId} UPDATE TradeItem SET extra = bdo. addTag (tag, extra) WHERE tradeItemId =#{ tradeItemId} commit transaction -- submit a TRANSACTION

This solution avoids the overhead caused by retries, and uses the exclusive lock (update lock) without adding additional lock overhead.

4. Optimistic lock Selection