Does not support row locking means concurrency control can only use a table lock, that is to say the same time, there can be only one on the table updates in the execution, it will
Affect the concurrency of the business, InnoDB support row locks, this is also one of the important reasons of MyISAM is replaced by the InnoDB,
Two-phase locking protocol
For example, the first transaction B statement execution what happens? Depending on the transaction after the execution of the two statements A holding the lock, and when to release?
You can do an experiment, is that the transaction B will be blocked in the execution of this statement, everyone will have a doubt,
The previous two statements not performed?
Why would block?
Transaction is actually A lock time is the time to perform the first statement, release the lock is commit after
But the transaction B is performed before transaction commit A
This transaction at this time also holds A id=1 row lock, so the transaction will be blocked, B
For us to know the principle what enlightenment?
If we know that the lock is released after a commit
Then we can lock the most likely cause conflict
Most likely to affect the concurrency of lock back as far as possible put
For example:
Business: customers in cinema B to buy A movie ticket
Operation:
1. In the A balance from the customer lose the ticket prices
2. Increase the account balance of cinema B this ticket
3. Add a log operation
You think of this three operations, that are most likely to affect the concurrency conflict will cause the lock
Clearly is a second operation, why? Think operation 1 just lock the user
This row, this time only have an effect on his own, if there is a customer also bought the movie
C
Tickets, so at this time of conflict is operating 2, because the somebody else also need to update the movie
Account balance, so we're going to put operation 2 last execution, because such operation involved 2
Row locks, lock time will be a little less, the greatest degree of reducing the transaction between waiting for
Improved concurrency,
The dead lock and the deadlock detection
As shown, transaction waiting for transaction B release id=2 lock, transaction B waiting for transaction release the lock id=1
This is a deadlock
Deadlock occurs there are two ways to solve the
1. Direct access to wait until the timeout, the timeout can be set by parameter innodb_lock_wait_timeout
2. Initiate deadlock detection, found a deadlock, active article rollback death chain in a certain transaction, allowing other transactions to perform,
The parameter innodb_deadlock_detect set to on, said open this logic
In the innodb innodb_lock_wait_timeout the value of the default is 50 s, mean if use the first method,
First be locked thread to over 50 s will timeout, then other threads is likely to continue,
For online services, the waiting time is often unacceptable,
But we can't take this time to set up a small, 1 s, for example, if the time is not deadlock, but the normal lock wait?
This will cause a lot of friendly fire, so we still use the deadlock detection is better, but innodb_deadlock_detect default is on
Deadlock detection is also actually takes up a lot of CPU resources, when the transaction is locked, to see if it relies on the thread lock is being locked
The cycle, and finally determine whether the cycle to wait, also is the deadlock
CodePudding user response:
If business level is not good to solve this problem, a method can be used:1. Add a redundant column
2. Update this two records transactions at the same time from the start of redundancy, conditions with id IN (1, 2), thus the two records locked together,
But performance will be low,