问题描述
在某笔交易开始后,如何限制其他用户更新或插入表中?
How do i restrict other users to update or insert in a table after a certain transaction has begun ?
我尝试了此操作:
MySqlConnection con = new MySqlConnection("server=localhost;database=data;user=root;pwd=;");
con.Open();
MySqlTransaction trans = con.BeginTransaction();
try
{
string sql = "insert INTO transaction_ledger (trans_id,voucher_id,voucher_number,trans_date,ledger_code,company_code,trans_type, trans_amount,primary_ledger,narration,ledger_parent,trans_type_name,ledger_ref_code,r_trans_id,IsSync) VALUES (0, 'EReceipt-4',4,'2013-04-01','483', '870d7d83-05ec-4fbb-8e9d-801150bd3ed1', 'EReceipt',-233.22,1,'asadfsaf','Bank OD A/c','Receipt','4274',1173,'N')";
new MySqlCommand(sql, con, trans).ExecuteNonQuery();
sql = "insert INTO transaction_ledger (trans_id,voucher_id,voucher_number,trans_date,ledger_code,company_code,trans_type, trans_amount,primary_ledger,narration,ledger_parent,trans_type_name,ledger_ref_code,r_trans_id,IsSync) VALUES (0, 'EReceipt-4',4,'2013-04-01','4274', '870d7d83-05ec-4fbb-8e9d-801150bd3ed1', 'EReceipt',100,0,'asadfsaf','Sundry Creditors','Receipt','483',1173,'N')";
new MySqlCommand(sql, con, trans).ExecuteNonQuery();
sql = "insert INTO transaction_ledger (trans_id,voucher_id,voucher_number,trans_date,ledger_code,company_code,trans_type, trans_amount,primary_ledger,narration,ledger_parent,trans_type_name,ledger_ref_code,r_trans_id,IsSync) VALUES (0, 'EReceipt-4',4,'2013-04-01','427', '870d7d83-05ec-4fbb-8e9d-801150bd3ed1', 'EReceipt',133.22,0,'asadfsaf','Sundry Creditors','Receipt','483',1173,'N')";
new MySqlCommand(sql, con, trans).ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
}
finally
{
con.Close();
}
但这仍然允许在BeginTransaction之后插入行。
but this still allows to insert rows after BeginTransaction.
推荐答案
BeginTransaction并不意味着您的事务已启动并且所有内容都已锁定。它只是通知RDBMS有关您启动事务的意图,并且从现在起您应该做的所有事情都应该而且必须被视为原子性的。
BeginTransaction does not mean that "your transaction has started and everything is locked". It just informs the RDBMS regarding your intent of initiating a transaction and that everything that you should do from now on should and must be considered atomic.
这意味着您可以调用有了BeingTransaction,我可以从数据库中所有表中删除所有数据,RDBMS会很乐意让我这样做。希望它不应该让我删除数据库,因为您已经与它建立了开放连接,但是,这些天您永远都不知道。
This means that you could call BeingTransaction and I could delete all data from all tables in your database and the RDBMS will happily let me do that. Hopefully, it should not let me drop the DB because you have an open connection to it, however, you never know these days. There might be some undocumented features I am not aware of.
原子表示任何动作或一组动作必须作为一个动作执行。如果其中任何一个失败,则它们全部都会失败。
Atomic means any action or set of actions must be performed as one. If any one of them fails that all of them fail. It is an everything or nothing concept.
看起来您要在表中插入三行。如果您的表为空或行数很少,则它可能会锁定整个表,具体取决于RDBMS的LOCK ESCALATION规则。但是,如果它是一个大表,非常大表或分区表,则LOCK升级规则可能无法保证表锁。因此,多个事务仍可能同时向表中插入行。一切都取决于RDBMS如何处理这种情况以及数据模型的结构。
Looks like you are inserting three rows into a table. If your table is empty or has very low number of rows, it might lock the whole table depending on the LOCK ESCALATION rules of your RDBMS. However, if it is a large or very large or partitioned table then the LOCK escalation rules might not guarantee a table lock. So, it might still be possible for multiple transactions to insert rows into your table at the same time. It all depends on how the RDBMS handles this situation and how your data model is structured.
现在要回答您的问题:
提示-在开始插入数据之前寻找一种锁定整个表的方法。
HINT - Look for a way to lock the entire table before you start inserting data.
但是,这通常不好,但我假设您已经一个合理的理由。
However, this is usually not good but I am assuming that you have a reasonable reason to do it.
希望这会有所帮助。
这篇关于c#.net中BeginTransaction MySql事务后的锁定表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!