MySQL transaction problems under large concurrency

The following scenario: User A and a large number of fans, this time a fan constantly to a to send props, this prop is the user to buy, to User A, will give the user A's account number to increase the corresponding money, but also to user A to add some attachment property value, such as User A's experience, blood bar length Ah, At the same time will give the fan added experience value, then the table here involves the user A's money table, Fan's money table, experience of the table, then I deal with this time to the entire process added transaction processing, but now encountered the problem is: once a large number of users at the same time to give a prop, then there will be a database deadlock problem, What should I do with this?

Reply content:

The following scenario: User A and a large number of fans, this time a fan constantly to a to send props, this prop is the user to buy, to User A, will give the user A's account number to increase the corresponding money, but also to user A to add some attachment property value, such as User A's experience, blood bar length Ah, At the same time will give the fan added experience value, then the table here involves the user A's money table, Fan's money table, experience of the table, then I deal with this time to the entire process added transaction processing, but now encountered the problem is: once a large number of users at the same time to give a prop, then there will be a database deadlock problem, What should I do with this?

These behaviors do not require immediate return results, can be asynchronous processing, keywords: Message Queuing

The problem with deadlocks is that there is a bug in the program or SQL code. Deal with the bug, even if it encounters a large concurrency, the maximum processing time is increased, but there is no deadlock.

Check the SQL statement for update .... where [Conditon], where condition is the primary key? If it is not a primary key, the database locks the entire table at update time, and it is extremely easy to deadlock when multiple threads are operating concurrently.
The workaround is that the condition after the update must use the primary key operation, at which point the database only uses row locks for the current operation record and does not deadlock.

Asynchronous operations can meet your needs.

Ps: Some operations are required to return data immediately. For example, consumer purchases must first judge the money enough. Actually, I'm here to recommend PostgreSQL.

With the recommended PostgreSQL
Asynchronous queues are a good solution

