Mysql has a fault. The application directly breaks the connection and data is locked (production failure ).

Mysql has a fault. The application directly breaks the connection and data is locked (production failure ).

Data lock caused by direct application disconnection (production failure)

1. This is a problem that data is locked because the application restarts the connection.
   
2. General System Structure

Basic information:

• The entire architecture manages db connections in a unified manner and shares connections.
• The application connects to the db access layer through loadbalance.
• Db access layer backend proxy several databases.
• Apply to loadbalance to communicate with mysql protocol.
• The db access layer communicates with the db in JDBC mode.
  

Fault: Table data in some databases is locked for a long time, leading to application failure in some scenarios.

Fault analysis: the set autocommit = 0 command for an application to enable a transaction is run from the app-lb-db access layer-db. If a transaction has not been executed yet, and the app forcibly interrupts the connection, this will lead to app-lb disconnection, while lb does not directly disconnect the backend connection, that is, the lb-db access layer-db connection will not be broken, as a result, db will not be rolled back. And the transaction locks some records, so some data is locked. It is not closed until the db access layer detects that the connection has exceeded the idle time and sends the rollback command to the db concurrently.

Solution:

① When the frontend connection is closed, lb must close the backend connection of the same session at the same time, and the db access layer must listen to the lb connection. Once the connection is closed, a rollback is required.

② Remove lb and provide the client loadbalance. Once the application closes the connection to the db access layer, it also needs to close the connection.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!