commit/rollback multiple times when not using CLS #4491
Description
Hi.
I had an issue as of late using latest stable version of Sequelize on our proprietary app. We don't use CLS (manual transaction life-cycle management on our side) and are on PostgreSQL. I would receive multiple instances of SET TRANSACTION ISOLATION LEVEL must be called before any query errors. This problem was devastating because connections in connection pool were exhausted and as a result all REST APIs to our server were timed-out. I narrowed the problem down to executing rollback on the same transaction multiple times.
So I think that there are two problems with Sequelize. First is that it allows rollback/commit on a transaction multiple times and thus it releases (pools) the same connection multiple times. Second is that if any error should happen while initializing a new transaction (promise chain in method prepareEnvironment) a connection is lost and exhausted. I can not rollback such a transaction on my side because transaction context object is not given to me in the first place, if such error is to occur.
I understood that by doing so, the connection is pooled multiple times and thus can be acquired and used in two different transactions concurrently. I'm fixing this in my code but nevertheless sequelize should not have had pooled the same connection multiple times.
In order to fix the first problem, I suggest adding a guard if to lib/transaction.js method rollback and commit so that any transaction is not rolleback/committed multiple times.
In order to fix the second problem, I suggest that in method prepareEnvironment we should check for any error during transaction startup, if any error should happen we should rollback half-initialized transaction (to fix connection exhaust bug).
I will make a pull request soon.