knex.js，typescript和mariadb的交易问题 [英] Transaction issue with knexjs, typescript and mariadb

问题描述

我想用打字稿进行交易，以刷新表中的某些数据.为此，需要执行以下步骤:

I want to have a transaction, in typescript, to refresh some data in a table. To do so the following steps need to apply:

1. 截断表中的所有记录
2. 将AUTO_INCREMENT设置为1
3. 将新记录插入表中

如果出现问题，我想回滚事务，而不更改db表中的现有记录.

If something goes wrong I would like to rollback the transaction and not alter the existing records in the db table.

我尝试了不同的尝试，但我想我缺少了一些东西，希望有人能发现我在做错什么.

I have tried different things but I think I am missing something and I wish someone can spot what I am doing wrong.

第一次尝试

await knex.transaction(async (trx) => {
  await knex(tableName).truncate().transacting(trx);
  await knex.raw(`ALTER TABLE ${tableName} AUTO_INCREMENT=1;`).transacting(trx);
  await knex(tableName).insert(data).transacting(trx);
  await trx.commit();
});

第二次尝试

await knex.transaction(async (trx) => {
  try {
    await knex(table).truncate().transacting(trx);
    await knex.raw(`ALTER TABLE ${table} AUTO_INCREMENT=1;`).transacting(trx);
    await knex(table).insert(data).transacting(trx);
    await trx.commit();
  } catch (e) {
    await trx.rollback();
  }
});

第三次尝试

const promisify = (fn: any) => new Promise((resolve, reject) => fn(resolve));
const trx: knex.Transaction  = <knex.Transaction> await promisify(this.knex.transaction);
try {
  await this.knex(this.table).truncate().transacting(trx);
  await this.knex.raw(`ALTER TABLE ${this.table} AUTO_INCREMENT=1;`).transacting(trx);
  await this.knex(this.table).insert(data).transacting(trx);
  await trx.commit();
  return Promise.resolve(data);
} catch (e) {
  await trx.rollback();
  return Promise.reject(e);
}

任何想法都会非常有用.

Any idea would be really useful.

推荐答案

在mysql中，所有更改DDL查询的架构都会隐式提交，因此无法回滚.因此，您需要更改实现，以其他方式保证db的完整性.

With mysql all schema altering DDL queries does implicit commit and they cannot be rolled back. So you need to change your implementation to guarantee integrity of db in some other way.

https://dev.mysql.com /doc/refman/8.0/en/cannot-roll-back.html https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

这篇关于knex.js，typescript和mariadb的交易问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！