lock table after BeginTransaction MySql Transaction in c#.net

Question

How do i restrict other users to update or insert in a table after a certain transaction has begun ?

I tried this :

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();
        }

but this still allows to insert rows after BeginTransaction.

`SqlTransaction sqlTrans = new SqlTransaction("Name the Transaction")` or you can change you your Query to `Insert Select for update` which will lock as well but I personally prefer using `SqlTransaction` object that way you can explicitly call the `Commits, Updates, Rollbacks..etc..` also to use the `Commit` statement you need a Transaction Object` also I would so refactor that code to separate each step into it's own Method utilizing the `try{}catch{}` for each it's a mess and you are assuming that each one will run / pass..which if it fails the rest of the code will never execute — MethodMan, Apr 01 '13 at 19:00
instead of writing the answer here is a previous link with how you can use this effectively and it's much cleaner. unless all the inserts are dependent on one another's transactions being completed and committed then I would follow the `using(){}` construct shown in the answer here http://stackoverflow.com/questions/2912112/c-sharp-rollback-sqltransaction-in-catch-block-problem-with-object-accessabi — MethodMan, Apr 01 '13 at 19:06

score 0 · Answer 1 · answered Apr 14 '13 at 06:11

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.

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.

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.

Now to answer your question:

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.

Hope this helps.

lock table after BeginTransaction MySql Transaction in c#.net

1 Answers1

Linked