server| Solution
In fact, the deepest cause of all deadlocks is one: resource competition
Performance One:
A User A accesses table A (lock table a) and then accesses table B
Another User B accesses table B (lock table B), and then attempts to access table A
At this point user a because User B has locked table B, it must wait for User B to release table B, to continue, well, his ladyship will have to wait here.
The same user B will wait for user A to release form A to continue this deadlock.
Workaround:
This deadlock is caused by bugs in your program, and there is no other way to adjust the logic of your program.
Carefully analyze the logic of your program,
1: Try to avoid locking two resources at the same time
2: When two resources must be locked at the same time, ensure that resources are locked in the same order at all times.
Performance Two:
User A reads a record and then modifies the record
This is User B modifying that record
Here in User A's transaction the nature of the lock is raised by a shared lock attempt to the exclusive lock (for update), while the exclusive lock in User B has to wait a-release because A has a shared lock
The shared lock is dropped, and a lock cannot be freed by an exclusive lock that cannot be raised because of the exclusive lock on B, and a deadlock occurs.
This deadlock is more covert, but it often happens in larger projects.
Workaround:
Let user A's transaction (that is, read-write type of operation first), in the Select is the update lock
The syntax is as follows:
SELECT * FROM table1 with (Updlock) where ....