Nesting transactions

[eloyleonardo]

Issue type:

[x] question
[ ] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[x] postgres
[ ] sqlite
[ ] sqljs
[ ] websql

TypeORM version:

[x] latest
[ ] @next
[ ] 0.x.x (or put your version here)

I have a transactional method and from it i call other transactional methods. When i do this the other method create a new transaction, there are any option to create a nested transaction ? Something like the propagation options of jpa/spring transaction.

The code above illustrate the question.

Steps to reproduce or a small repository showing the problem:

@Transaction()
public async save(@RequestBody() band: Band, @TransactionRepository(Band) bandRepository: Repository<Band>) {
        /* new transaction 1 started */ 
        await bandRepository.save(band);
        await this.snooze(30000);
        await  this.saveMoreAwesomeBand(band.name, [band, band, band, band, band, band]);
        /* in this case the transaction 2 was commited but the 1 will be rollbacked */ 
        throw new Error('teste');
       //return await bandRepository.findAll();
    }

@Transaction()
private async saveMoreAwesomeBand(name: string, bands: Array<Band>, @TransactionRepository(Band) bandRepository?: Repository<Band>) {
        /* new transaction 2 started */
        for (let i = 0; i < bands.length; i++) {
            const band = bands[i];
            delete band.id;
            band.name = name + ' ' + i;
            await bandRepository.save(band);
        }
        /* transaction 2 commited */
    }

[ProTip]

I'm super interested in this as well. The unit of work experience in entity framework is fantastic, however the technical limitations of the javascript/node seem to make that impossible without a sort of AsyncLocal<T> storage :|

[RDeluxe]

What's the current workaround for this, if any @pleerock ?

[chadjaros]

+1. I would really like to set up nested transactions so that I can do db integration tests without having to re-seed the database for every test.

[odavid]

Hi All,
I've created a small project that adds a Spring Style Transactional Decorator and can control propagation.
See https://github.com/odavid/typeorm-transactional-cls-hooked

Hope this helps,
Ohad

[clovis1122]

Hey @AlexMesser and @pleerock, is there some progress with this? Having some ideas on how to tackle the issue. The solutions I'm thinking about involves detecting if there's a transaction open at the database level, which would not only solve the nested transaction issue but also allow greater control over how Typeorm manages the transactions currently.

The algorithm goes as follows:

1. Check if there's a database transaction active.
2. If there's not, create a new one.
3. If there is, create a Savepoint (Postgres, MsSQL, etc) or throw an error (MySQL, for example, does not support nested transactions). Save the savepoint's name inside the transaction manager instance.
4. On commit, commit the savepoint. On error, rollback to savepoint. If there was no transaction active before this one began, commit/rollback as usual.

I made failing tests for both use cases at #3921.

[benmesirow]

You can check if a transaction is in progress using the isTransactionActive property on QueryRunner. Example:

if (repo.queryRunner.isTransactionActive) {
  // run your runInTransaction stuff here
} else {
  repo.manager.transaction(async manager => {
    // run your runInTransaction stuff here
  });
}

[lopezm94]

What would happen in case of starting a transaction inside another?

globalManager.transaction((localEntitytManager1) => {
    localEntitytManager1.getRepository(X).create(x)
    localEntitytManager1.transaction((localEntityManager2) => {
        localEntitytManager2.getRepository(Y).create(y)
    }
})

Since nested transactions are not allowed, does localEntityManager2 use the same transaction as localEntitytManager1? Or does it create an entirely different transaction? (I'm using MySQL)

[benmesirow]

@lopezm94 localEntityManager1 and localEntityManager2 are the same object, so you'd get an error saying something like "transaction in already in progress"

[geritol]

Any progress on this?
This feature would be quite useful to make hooks eg BeforeDelete and AfterDelete run in the already started transaction.

[lbguilherme]

I can contribute an implementation of this using SAVEPOINTs on supported databases and open a PR. Would it be acceptable?

[burgalon]

@lbguilherme did you get to start on this? I'm considering starting a similar effort

[lbguilherme]

@burgalon Please do. I couldn't find time for this yet.

[mohsinamjad]

+1