等待其他事务提交/回滚，然后再开始其他事务? [英] Wait for other transaction to commit/rollback before beginning another transaction?

问题描述

如果MySQL中仍然存在未提交/未回滚的事务，如何使我的事务等待?

How can I make my transactions to wait if there is still an un-committed/un-roll-backed transaction in MySQL?

目前，我是在代码端而不是在数据库存储过程端进行事务，像这样:

Currently I do my transactions on the code side, not on the DB Stored Procedure side, like this:

cmd.Connection.BeginTransaction();
try {
    // db codes here
    cmd.Transaction.Commit();
} catch {
    cmd.Transaction.Rollback();
    throw;
} finally {
    cmd.Connection.Close();
}

我希望其他事务等待上一个事务完成.因为我在某些存储的proc中插入外键时会得到MAX(id)，其中id是也在同一事务上创建的自动递增的列.但是，当我同时进行2个事务时，在上面的当前设置中不起作用，发生的事情是这样的:

I want for other transactions to wait until the previous transaction is finished. Since I have, in some of my stored proc, that gets the MAX(id) when inserting for a foreign keys, where id is an auto-incremented column that is also created on the same transaction. But it doesn't work on my current setup above when 2 transactions occur at the same time, what happened is like this:

Begin Trans1
Begin Trans2
Trans1: insert a values(); --id = 1
Trans2: insert a values(); --id = 1
Trans1: insert b(aid, col1) values((select MAX(id) from a), 'test'); --aid = 1
Trans2: insert b(aid, col1) values((select MAX(id) from a), 'test'); --aid = 1
Commit Trans1
Commit Trans2 --id = 2, aid = 1

我希望是这样的:

Begin Trans1
Wait Trans2
Trans1: insert a values(); --id = 1
Trans1: insert b(aid, col1) values((select MAX(id) from a), 'test'); --aid = 1
Commit Trans1
Begin Trans2
Trans2: insert a values(); --id = 2
Trans2: insert b(aid, col1) values((select MAX(id) from a), 'test'); --aid = 2
Commit Trans2

有可能这样吗?

推荐答案

通过使用 IsolationLevel.ReadCommitted ，您可以避免其他事务访问事务中涉及的表

by using IsolationLevel.ReadCommitted you can avoid the access to the table involved in a transaction by other transaction

cmd.Connection.BeginTransaction(IsolationLevel.ReadCommitted);

或

您可以使用TransactionScope类. TransactionScope使Code块具有事务性，直到未提交代码为止，表将被锁定，如果其他一些请求请求同一张表，则它必须等待直到正在运行的事务被提交或回滚为止.

you can use TransactionScope class. TransactionScope makes the Code block transactional,till the code is not committed the tables will be locked and if some other requests the same table then it will have to wait until the running transaction is committed or rolled back.

参阅

这篇关于等待其他事务提交/回滚，然后再开始其他事务?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！