@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

@Injectable()
export class UsersService implements OnModuleInit {
  private readonly dataSourceManager

  constructor(
    @InjectRepository(User)
    private readonly usersRepository: Repository<User>,

    @InjectDataSource()
    private readonly dataSource: DataSource,

    @InjectEntityManager()
    private readonly manager: EntityManager
  ) {
    this.dataSourceManager = dataSource.manager
  }

  get getterManager(): EntityManager {
    return this.dataSource.manager
  }

  async onModuleInit(): Promise<void> {
    addTransactionalDataSource(this.dataSource);
  }
  
  @Transactional()
  async test(): Promise<void> {
    await this.testRepository()
    await this.testDataSource()
    await this.testManager()
    await this.testDataSourceManager()
    await this.testGetterManager()
    throw new Error("Rollback")
  }

  async testRepository(): Promise<void> {
    const user = new User();
    user.firstName = "test"
    user.lastName = "Repository"
    await this.usersRepository.save(user)
  }

  async testDataSource(): Promise<void> {
    const user = new User();
    user.firstName = "test"
    user.lastName = "DataSource"
    await this.dataSource.manager.save(user)
  }

  async testManager(): Promise<void> {
    const user = new User();
    user.firstName = "test"
    user.lastName = "manager"
    await this.manager.save(user)
  }

  async testDataSourceManager(): Promise<void> {
    const user = new User();
    user.firstName = "test"
    user.lastName = "DataSourceManager"
    await this.dataSourceManager.save(user)
  }

  async testGetterManager(): Promise<void> {
    const user = new User();
    user.firstName = "test"
    user.lastName = "GetterManager"
    await this.getterManager.save(user)
  }

Logging

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

[
  {
    "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

@Injectable()
export class UsersService implements OnModuleInit {
  private readonly dataSourceManager

  constructor(
    @InjectRepository(User)
    private readonly usersRepository: Repository<User>,

    @InjectDataSource()
    private readonly dataSource: DataSource,

    @InjectEntityManager()
    private readonly manager: EntityManager
  ) {
    this.dataSourceManager = dataSource.manager
  }

  get getterManager(): EntityManager {
    return this.dataSource.manager
  }

  async onModuleInit(): Promise<void> {
    addTransactionalDataSource(this.dataSource);
  }
  
  @Transactional()
  async test(): Promise<void> {
    await this.testRepository()
    await this.testDataSource()
    await this.testManager()
    await this.testDataSourceManager()
    await this.testGetterManager()
    throw new Error("Rollback")
  }

  async testRepository(): Promise<void> {
    const user = new User();
    user.firstName = "test"
    user.lastName = "Repository"
    await this.usersRepository.save(user)
  }

  async testDataSource(): Promise<void> {
    const user = new User();
    user.firstName = "test"
    user.lastName = "DataSource"
    await this.dataSource.manager.save(user)
  }

  async testManager(): Promise<void> {
    const user = new User();
    user.firstName = "test"
    user.lastName = "manager"
    await this.manager.save(user)
  }

  async testDataSourceManager(): Promise<void> {
    const user = new User();
    user.firstName = "test"
    user.lastName = "DataSourceManager"
    await this.dataSourceManager.save(user)
  }

  async testGetterManager(): Promise<void> {
    const user = new User();
    user.firstName = "test"
    user.lastName = "GetterManager"
    await this.getterManager.save(user)
  }

Logging

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

[
  {
    "id": 52,
    "firstName": "test",
    "lastName": "manager",
    "isActive": true
  },
  {
    "id": 53,
    "firstName": "test",
    "lastName": "DataSourceManager",
    "isActive": true
  }
]

Thank you. It will help me a lot.