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,
- 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!
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.