The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

Transaction and locking performance

Hi! I'm asking about the performance impact of a long running (5min) transaction. The scenario is a simple spooling and messaging system. So, I've got a file system, containing the data files to be transmitted (via some rest endpoint) and a database table containing one row per file name. And 2 columns, name and a status column.

The process goes like this:

  • get the first row that
    • has a status of "ready"
    • where a GET_LOCK(name, 0) does not fail.
  • start a transaction that sets the status to 'transmitted"
  • try to send
  • if successful,
    • commit
    • release the lock
  • if unsuccessful, die (lock gets released and transaction rolled back)

Now, the transaction/lock pair can endure for, for instance, 5 minutes, maybe longer. Is this going to be a problem? Can several rows in that table be involved in transactions at the same time?

There may be a hundred rows and possibly two or three workers trying to transmit.

Lots of Greetings!

Volker

Answer Answered by Ian Gilfillan in this comment.

With InnoDB/XtraDB (the default storage engine), several rows can be involved in transactions at the same time, and having long transactions is not necessarily a problem, depending on what your system is trying to do. Take a look at XtraDB/InnoDB Lock Modes and the Isolation Levels to get a better understanding of the way it works, and the possibilities.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.