事务

事务是关系数据库的一个重要功能,因为它们允许从故障中正确恢复并即使在系统发生故障时也能保持数据库的一致性。 事务中的所有查询都在同一数据库连接上执行,并将整组查询作为单个工作单元运行。 任何失败都意味着数据库会将在该连接上执行的任何查询回滚到事务前状态。

Transactions are an important feature of relational databases, as they allow correct recovery from failures and keep a database consistent even in cases of system failure. All queries within a transaction are executed on the same database connection, and run the entire set of queries as a single unit of work. Any failure will mean the database will rollback any queries executed on that connection to the pre-transaction state.

通过将处理程序函数传递到 knex.transaction 来处理事务。 处理函数接受一个参数,一个可以通过两种方式使用的对象:

Transactions are handled by passing a handler function into knex.transaction. The handler function accepts a single argument, an object which may be used in two ways:

  1. 作为 "promise 意识" knex 连接
  2. 作为对象传递到带有 transacting 的查询并最终调用提交或回滚。

考虑这两个例子:

Consider these two examples:

// Using trx as a query builder:
knex.transaction(function(trx) {

  const books = [
    {title: 'Canterbury Tales'},
    {title: 'Moby Dick'},
    {title: 'Hamlet'}
  ];

  return trx
    .insert({name: 'Old Books'}, 'id')
    .into('catalogues')
    .then(function(ids) {
      books.forEach((book) => book.catalogue_id = ids[0]);
      return trx('books').insert(books);
    });
})
.then(function(inserts) {
  console.log(inserts.length + ' new books saved.');
})
.catch(function(error) {
  // If we get here, that means that 
  // neither the 'Old Books' catalogues insert,
  // nor any of the books inserts will have taken place.
  console.error(error);
});

然后这个例子:

And then this example:

// Using trx as a transaction object:
knex.transaction(function(trx) {

  const books = [
    {title: 'Canterbury Tales'},
    {title: 'Moby Dick'},
    {title: 'Hamlet'}
  ];

  knex.insert({name: 'Old Books'}, 'id')
    .into('catalogues')
    .transacting(trx)
    .then(function(ids) {
      books.forEach((book) => book.catalogue_id = ids[0]);
      return knex('books').insert(books).transacting(trx);
    })
    .then(trx.commit)
    .catch(trx.rollback);
})
.then(function(inserts) {
  console.log(inserts.length + ' new books saved.');
})
.catch(function(error) {
  // If we get here, that means that 
  // neither the 'Old Books' catalogues insert,
  // nor any of the books inserts will have taken place.
  console.error(error);
});

与上面使用等待/异步的示例相同:

Same example as above using await/async:

try {
  await knex.transaction(async trx => {

    const books = [
      {title: 'Canterbury Tales'},
      {title: 'Moby Dick'},
      {title: 'Hamlet'}
    ];
    
    const ids = await trx('catalogues')
      .insert({
        name: 'Old Books'
      }, 'id')

    books.forEach((book) => book.catalogue_id = ids[0])
    const inserts = await trx('books').insert(books)
    
    console.log(inserts.length + ' new books saved.')
  })
} catch (error) {
  // If we get here, that means that neither the 'Old Books' catalogues insert,
  // nor any of the books inserts will have taken place.
  console.error(error);
}

使用另一种等待/异步方法的与上面相同的示例:

Same example as above using another await/async approach:

try {
  await knex.transaction(async trx => {

    const books = [
      {title: 'Canterbury Tales'},
      {title: 'Moby Dick'},
      {title: 'Hamlet'}
    ];

    const ids = await knex('catalogues')
      .insert({
        name: 'Old Books'
      }, 'id')
      .transacting(trx)

    books.forEach(book => book.catalogue_id = ids[0])
    await knex('books')
      .insert(books)
      .transacting(trx)

    console.log(inserts.length + ' new books saved.')
  })
} catch (error) {
  console.error(error);
}

直接从事务处理函数抛出错误会自动回滚事务,与返回被拒绝的 Promise 相同。

Throwing an error directly from the transaction handler function automatically rolls back the transaction, same as returning a rejected promise.

请注意,如果处理程序内未返回 Promise,则你需要确保调用 trx.committrx.rollback,否则事务连接将挂起。

Notice that if a promise is not returned within the handler, it is up to you to ensure trx.commit, or trx.rollback are called, otherwise the transaction connection will hang.

调用 trx.rollback 将返回被拒绝的 Promise。 如果你不向 trx.rollback 传递任何参数,则会创建并传入一个通用 Error 对象,以确保 Promise 始终拒绝某些内容。

Calling trx.rollback will return a rejected Promise. If you don't pass any argument to trx.rollback, a generic Error object will be created and passed in to ensure the Promise always rejects with something.

请注意,Amazon Redshift 不支持事务中的保存点。

Note that Amazon Redshift does not support savepoints in transactions.

在某些情况下,你可能更喜欢创建事务,但稍后只执行其中的语句。 在这种情况下,调用不带处理函数的方法 transaction

In some cases you may prefer to create transaction but only execute statements in it later. In such case call method transaction without a handler function:

// Using trx as a transaction object:
const trx = await knex.transaction();

const books = [
  {title: 'Canterbury Tales'},
  {title: 'Moby Dick'},
  {title: 'Hamlet'}
];

trx('catalogues')
  .insert({name: 'Old Books'}, 'id')
  .then(function(ids) {
    books.forEach((book) => book.catalogue_id = ids[0]);
    return trx('books').insert(books);
  })
  .then(trx.commit)
  .catch(trx.rollback);

如果你想要创建一个可重用的事务实例,但不想在使用它之前实际启动它,则可以创建一个事务提供程序实例。 第一次调用后会启动事务,并在后续调用时返回相同的事务:

If you want to create a reusable transaction instance, but do not want to actually start it until it is used, you can create a transaction provider instance. It will start transaction after being called for the first time, and return same transaction on subsequent calls:

// Does not start a transaction yet
const trxProvider = knex.transactionProvider();

const books = [
  {title: 'Canterbury Tales'},
  {title: 'Moby Dick'},
  {title: 'Hamlet'}
];

// Starts a transaction
const trx = await trxProvider();
const ids = await trx('catalogues')
  .insert({name: 'Old Books'}, 'id')
books.forEach((book) => book.catalogue_id = ids[0]);
await trx('books').insert(books);

// Reuses same transaction
const sameTrx = await trxProvider();
const ids2 = await sameTrx('catalogues')
  .insert({name: 'New Books'}, 'id')
books.forEach((book) => book.catalogue_id = ids2[0]);
await sameTrx('books').insert(books);

你可以在使用任一创建事务的方式时,从字段 executionPromise 访问在用户显式回滚或提交事务后解决的 promise,或者如果数据库本身回滚事务则拒绝该 promise:

You can access the promise that gets resolved after transaction is rolled back explicitly by user or committed, or rejected if it gets rolled back by DB itself, when using either way of creating transaction, from field executionPromise:

const trxProvider = knex.transactionProvider();
const trx = await trxProvider();
const trxPromise = trx.executionPromise;

const trx2 = await knex.transaction();
const trx2Promise = trx2.executionPromise;

const trxInitPromise = new Promise(async (resolve, reject) => {
  knex.transaction((transaction) => {
    resolve(transaction);
  });
});
const trx3 = await trxInitPromise;
const trx3Promise = trx3.executionPromise;

可以通过方法 isCompleted 检查事务是否已提交或回滚:

You can check if a transaction has been committed or rolled back with the method isCompleted:

const trx = await knex.transaction();
trx.isCompleted(); // false
await trx.commit();
trx.isCompleted(); // true

const trx2 = knex.transactionProvider();
await trx2.rollback();
trx2.isCompleted(); // true

你可以检查属性 knex.isTransaction 以查看当前正在使用的 knex 实例是否是事务。

You can check the property knex.isTransaction to see if the current knex instance you are working with is a transaction.

事务模式

如果你需要为事务指定隔离级别,可以使用配置参数 isolationLevel。 oracle 和 sqlite 不支持,选项有 read uncommittedread committedrepeatable readsnapshot(仅限 mssql)、serializable

In case you need to specify an isolation level for your transaction, you can use a config parameter isolationLevel. Not supported by oracle and sqlite, options are read uncommitted, read committed, repeatable read, snapshot (mssql only), serializable.

// Simple read skew example
const isolationLevel = 'read committed';
const trx = await knex.transaction({isolationLevel});
const result1 = await trx(tableName).select();
await knex(tableName).insert({ id: 1, value: 1 });
const result2 = await trx(tableName).select();
await trx.commit();
// result1 may or may not deep equal result2 depending on isolation level

你还可以使用 readOnly 配置参数将事务模式设置为 read only。 目前仅在 mysql、postgres 和 redshift 上支持。

You may also set the transaction mode as read only using the readOnly config parameter. It is currently only supported on mysql, postgres, and redshift.

const trx = await knex.transaction({ readOnly: true });
// 💥 Cannot `INSERT` while inside a `READ ONLY` transaction
const result = await trx(tableName).insert({ id: 1, foo: 'bar' });