@Transactional() decorator doesn't work

[vfa-locltb]

Hi, @Aliheym, thank you for making this library.

I have one issue about this library. The @Transactional() decorator doesn't seem to work. It doesn't roll back when an action fails.

  @Transactional()
  async delete(user: UserEntity) {
        await this.userRepository.softDelete(user.id);

        await this.otherService.deleteRelationEntityUser(user.id);

        // This fails
        await this.userRepository.delete(user.id);
});

When the third function fails, I expect it to roll back all the changes made to database from the first 2 functions. But it never rolled back. This user is still soft deleted, so is the database changes of the second function.

I setup as you instructed in the readme file. But I can't seem to wrap my head around how this doesn't work.

Please help me, feel free to ask me to provide necessary information for you to investigate this issue.

Update: I checked the query log and saw that it create a transaction in every functions instead of a big transaction that wraps all of the functions' query

[Aliheym]

Hi, sorry for the delay for more details could you provide some query logs or some examples for your case? Thanks.

[prelipceang]

I had a similar issue, and the problem in my case was mixing sync/async calls to db

[h4l-yup]

@Aliheym I had a similar issue, and it seems the main issue is that when there is a transactional command (like save) in the transactional decoratored method, it just make another connection and executed. So the connections START TRANSACTION query used and {INSERT QUERY} query used are different. Then the insert query cannot be rollbacked.

I think in @vfa-locltb 's example, first and second queries are not using the same connection with the transactional decorator so they cannot be rollbacked when an error occurred.

I think it is a kind of serious problem.

[ybjeon01]

@Aliheym , like h4l-yup, I found that @Transactional() makes an START TRANSACTION, but repo.save() makes another transaction too. and I confirmed that two different connections in psql

While working on a particular task, similar to the situation with 'h4l-yup', I got the same issue with transactions in PostgreSQL. Specifically, when using the @Transactional() decorator, it initiates a START TRANSACTION command. However, I also noticed that calling the repo.save() method within the same context creates another separate transaction. To verify this, I looked into the connections in psql command, SELECT * FROM pg_stat_activity; and confirmed that these two operations indeed resulted in two different connections."

[starnayuta]

I found that @Transactional() does not work in the following cases.

• @InjectEntityManager()
• Keep dataSource.manager as a member variable

The following cases worked fine.

• @InjectRepository()
• @InjectDataSource()
• Accessing dataSouce.manager with getter

Test code and other details

Test code ``` ts @Injectable() export class UsersService implements OnModuleInit { private readonly dataSourceManager constructor( @InjectRepository(User) private readonly usersRepository: Repository, @InjectDataSource() private readonly dataSource: DataSource, @InjectEntityManager() private readonly manager: EntityManager ) { this.dataSourceManager = dataSource.manager } get getterManager(): EntityManager { return this.dataSource.manager } async onModuleInit(): Promise { addTransactionalDataSource(this.dataSource); } @Transactional() async test(): Promise { await this.testRepository() await this.testDataSource() await this.testManager() await this.testDataSourceManager() await this.testGetterManager() throw new Error("Rollback") } async testRepository(): Promise { const user = new User(); user.firstName = "test" user.lastName = "Repository" await this.usersRepository.save(user) } async testDataSource(): Promise { const user = new User(); user.firstName = "test" user.lastName = "DataSource" await this.dataSource.manager.save(user) } async testManager(): Promise { const user = new User(); user.firstName = "test" user.lastName = "manager" await this.manager.save(user) } async testDataSourceManager(): Promise { const user = new User(); user.firstName = "test" user.lastName = "DataSourceManager" await this.dataSourceManager.save(user) } async testGetterManager(): Promise { const user = new User(); user.firstName = "test" user.lastName = "GetterManager" await this.getterManager.save(user) } ``` Logging ``` txt query: START TRANSACTION query: INSERT INTO `user`(`id`, `firstName`, `lastName`, `isActive`) VALUES (DEFAULT, ?, ?, DEFAULT) -- PARAMETERS: ["test","Repository"] query: SELECT `User`.`id` AS `User_id`, `User`.`isActive` AS `User_isActive` FROM `user` `User` WHERE `User`.`id` = ? -- PARAMETERS: [50] query: INSERT INTO `user`(`id`, `firstName`, `lastName`, `isActive`) VALUES (DEFAULT, ?, ?, DEFAULT) -- PARAMETERS: ["test","DataSource"] query: SELECT `User`.`id` AS `User_id`, `User`.`isActive` AS `User_isActive` FROM `user` `User` WHERE `User`.`id` = ? -- PARAMETERS: [51] query: START TRANSACTION query: INSERT INTO `user`(`id`, `firstName`, `lastName`, `isActive`) VALUES (DEFAULT, ?, ?, DEFAULT) -- PARAMETERS: ["test","manager"] query: SELECT `User`.`id` AS `User_id`, `User`.`isActive` AS `User_isActive` FROM `user` `User` WHERE `User`.`id` = ? -- PARAMETERS: [52] query: COMMIT query: START TRANSACTION query: INSERT INTO `user`(`id`, `firstName`, `lastName`, `isActive`) VALUES (DEFAULT, ?, ?, DEFAULT) -- PARAMETERS: ["test","DataSourceManager"] query: SELECT `User`.`id` AS `User_id`, `User`.`isActive` AS `User_isActive` FROM `user` `User` WHERE `User`.`id` = ? -- PARAMETERS: [53] query: COMMIT query: INSERT INTO `user`(`id`, `firstName`, `lastName`, `isActive`) VALUES (DEFAULT, ?, ?, DEFAULT) -- PARAMETERS: ["test","GetterManager"] query: SELECT `User`.`id` AS `User_id`, `User`.`isActive` AS `User_isActive` FROM `user` `User` WHERE `User`.`id` = ? -- PARAMETERS: [54] query: ROLLBACK ``` Record after execution ``` json [ { "id": 52, "firstName": "test", "lastName": "manager", "isActive": true }, { "id": 53, "firstName": "test", "lastName": "DataSourceManager", "isActive": true } ] ```

[Aliheym]

I found that @Transactional() does not work in the following cases.

• @InjectEntityManager()
• Keep dataSource.manager as a member variable

The following cases worked fine.

• @InjectRepository()
• @InjectDataSource()
• Accessing dataSouce.manager with getter

Test code and other details

Test code ``` ts @Injectable() export class UsersService implements OnModuleInit { private readonly dataSourceManager constructor( @InjectRepository(User) private readonly usersRepository: Repository, @InjectDataSource() private readonly dataSource: DataSource, @InjectEntityManager() private readonly manager: EntityManager ) { this.dataSourceManager = dataSource.manager } get getterManager(): EntityManager { return this.dataSource.manager } async onModuleInit(): Promise { addTransactionalDataSource(this.dataSource); } @Transactional() async test(): Promise { await this.testRepository() await this.testDataSource() await this.testManager() await this.testDataSourceManager() await this.testGetterManager() throw new Error("Rollback") } async testRepository(): Promise { const user = new User(); user.firstName = "test" user.lastName = "Repository" await this.usersRepository.save(user) } async testDataSource(): Promise { const user = new User(); user.firstName = "test" user.lastName = "DataSource" await this.dataSource.manager.save(user) } async testManager(): Promise { const user = new User(); user.firstName = "test" user.lastName = "manager" await this.manager.save(user) } async testDataSourceManager(): Promise { const user = new User(); user.firstName = "test" user.lastName = "DataSourceManager" await this.dataSourceManager.save(user) } async testGetterManager(): Promise { const user = new User(); user.firstName = "test" user.lastName = "GetterManager" await this.getterManager.save(user) } ``` Logging ``` txt query: START TRANSACTION query: INSERT INTO `user`(`id`, `firstName`, `lastName`, `isActive`) VALUES (DEFAULT, ?, ?, DEFAULT) -- PARAMETERS: ["test","Repository"] query: SELECT `User`.`id` AS `User_id`, `User`.`isActive` AS `User_isActive` FROM `user` `User` WHERE `User`.`id` = ? -- PARAMETERS: [50] query: INSERT INTO `user`(`id`, `firstName`, `lastName`, `isActive`) VALUES (DEFAULT, ?, ?, DEFAULT) -- PARAMETERS: ["test","DataSource"] query: SELECT `User`.`id` AS `User_id`, `User`.`isActive` AS `User_isActive` FROM `user` `User` WHERE `User`.`id` = ? -- PARAMETERS: [51] query: START TRANSACTION query: INSERT INTO `user`(`id`, `firstName`, `lastName`, `isActive`) VALUES (DEFAULT, ?, ?, DEFAULT) -- PARAMETERS: ["test","manager"] query: SELECT `User`.`id` AS `User_id`, `User`.`isActive` AS `User_isActive` FROM `user` `User` WHERE `User`.`id` = ? -- PARAMETERS: [52] query: COMMIT query: START TRANSACTION query: INSERT INTO `user`(`id`, `firstName`, `lastName`, `isActive`) VALUES (DEFAULT, ?, ?, DEFAULT) -- PARAMETERS: ["test","DataSourceManager"] query: SELECT `User`.`id` AS `User_id`, `User`.`isActive` AS `User_isActive` FROM `user` `User` WHERE `User`.`id` = ? -- PARAMETERS: [53] query: COMMIT query: INSERT INTO `user`(`id`, `firstName`, `lastName`, `isActive`) VALUES (DEFAULT, ?, ?, DEFAULT) -- PARAMETERS: ["test","GetterManager"] query: SELECT `User`.`id` AS `User_id`, `User`.`isActive` AS `User_isActive` FROM `user` `User` WHERE `User`.`id` = ? -- PARAMETERS: [54] query: ROLLBACK ``` Record after execution ``` json [ { "id": 52, "firstName": "test", "lastName": "manager", "isActive": true }, { "id": 53, "firstName": "test", "lastName": "DataSourceManager", "isActive": true } ] ```

Thank you. It will help me a lot.