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();
}
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
What I was hoping is like this:
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
Is it possible like this?
