locked
How to use transaction? RRS feed

  • Question

  • Hi all,

    I am trying to use sqlite transaction, but somewhere is mistake. I have no idea where. 

    using (SQLiteConnection con = new SQLiteConnection(conString))

    con.Open();

    using (SQLiteTransaction trans = con.BeginTransaction()) { try { using (SQLiteCommand cmd = new SQLiteCommand("delete from cars;", con, trans)) { cmd.ExecuteNonQuery(); } using (SQLiteCommand cmd = new SQLiteCommand("insert into cars (name) values ("peugeot");", con, trans)) { cmd.ExecuteNonQuery(); } trans.Commit(); } catch (Exception ex) { MessageBox.Show("Commit Exception Type: " + ex.GetType() + "\n" + "Message: " + ex.Message); try { trans.Rollback(); } catch (Exception ex2) { MessageBox.Show("Rollback Exception Type: " + ex2.GetType() + "\n" + "Message: " + ex2.Message); } } }



    Monday, July 20, 2020 2:23 PM

Answers

  • I agree with Karen. This error indicates that the DB cannot get the necessary locks because somebody else already has a lock on the required tables. Most DBs lock as queries are run (especially inserts) and will wait a little bit before giving up.

    I believe one problem with your code is the MessageBox. You open a connection to the DB and then delete everything from a table. That means you've locked that entire table. This really isn't a good idea at all. Nevertheless you then attempt to insert new data. Since you're in the same transaction the DB should allow the call. However if something goes wrong you are going to wait on the user to confirm the error. But during this time you have locked the table and so nobody else can do anything with it. If somebody else tries this exact same call they'll get the error you're reporting.

    General rule of thumb is to not do any blocking calls while you have a DB lock. Waiting on the user is definitely a blocking call. Personally I recommend you get rid of the try-catch altogether as you really don't need it here. If the transaction is not committed by the time it is disposed (leaves the using) then it rolls back automatically. If you really want to tell the user something went wrong then do so after the rollback so the DB is freed.

    try
    {
       using (var conn = new SqliteConnection(...))
       using (var trans = conn.BeginTranscation())
       {
          ...
       };
    } catch (Exception e)
    {
       MessageBox.Show(...);
    };

    However I don't think your data layer should be doing anything in the UI. Your try-catch and user update should happen at the higher level UI where the data access is actually triggered. But that is just me.

    Wiping a table is almost always going to cause problems if this is a multi-user system. So I'd also recommend you handle this better. Perhaps instead of wiping the entire table only remove the cars that the particular user is working with. With your current code every user has the same car row so your table would only ever have 1 car in it. That seems wrong to me.

    Also ensure that all your other database calls are properly cleaning up the connections otherwise you can get into locks. Finally note that most DB systems have a management tool that allows you to see connections and locks. This can be very useful for debugging deadlocks.


    Michael Taylor http://www.michaeltaylorp3.net

    • Proposed as answer by Naomi N Monday, July 20, 2020 5:20 PM
    • Marked as answer by TakeshiKitano Tuesday, July 21, 2020 1:40 PM
    Monday, July 20, 2020 5:07 PM

All replies

  • You didn't tell us what is wrong so how are we supposed to help? Are you getting an error? Is it not updating the DB? Does your code not compile?

    Michael Taylor http://www.michaeltaylorp3.net

    Monday, July 20, 2020 3:10 PM
  • You forgot to open { after the very first using con statement and close it at the very end.

    I would probably just sent both commands as one command, but perhaps there is a reason you need to do 2 separate commands this way.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Monday, July 20, 2020 3:14 PM
    Monday, July 20, 2020 3:12 PM
  • ... yes ... I forgot put here con.open(), but in my code I have it .... 

    in my program i have a table of data (dataGrid) so first I delete all and then I insert new data in a for loop  ... so better is write more sqlite commands for me ... and ok ... I remove one catch ...

    what is the problem ... sometime it shows me an error message ... database is locked and that error red line stopped on the line ... 

    using (SQLiteTransaction trans = con.BeginTransaction())
    .... 

    Monday, July 20, 2020 3:19 PM
  • ... yes ... I forgot put here con.open(), but in my code I have it .... 

    in my program i have a table of data (dataGrid) so first I delete all and then I insert new data in a for loop  ... so better is write more sqlite commands for me ... and ok ... I remove one catch ...

    what is the problem ... sometime it shows me an error message ... database is locked and that error red line stopped on the line ... 

    using (SQLiteTransaction trans = con.BeginTransaction())
    .... 

    This can be caused by another connection actively open or from a external source having the database open.

    On a side note, use multiple catches, one SqliteException then followed by Exception. Using SqliteException you can work with the SqliteErrorCode 


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Proposed as answer by CoolDadTx Monday, July 20, 2020 4:59 PM
    Monday, July 20, 2020 3:23 PM
  • I agree with Karen. This error indicates that the DB cannot get the necessary locks because somebody else already has a lock on the required tables. Most DBs lock as queries are run (especially inserts) and will wait a little bit before giving up.

    I believe one problem with your code is the MessageBox. You open a connection to the DB and then delete everything from a table. That means you've locked that entire table. This really isn't a good idea at all. Nevertheless you then attempt to insert new data. Since you're in the same transaction the DB should allow the call. However if something goes wrong you are going to wait on the user to confirm the error. But during this time you have locked the table and so nobody else can do anything with it. If somebody else tries this exact same call they'll get the error you're reporting.

    General rule of thumb is to not do any blocking calls while you have a DB lock. Waiting on the user is definitely a blocking call. Personally I recommend you get rid of the try-catch altogether as you really don't need it here. If the transaction is not committed by the time it is disposed (leaves the using) then it rolls back automatically. If you really want to tell the user something went wrong then do so after the rollback so the DB is freed.

    try
    {
       using (var conn = new SqliteConnection(...))
       using (var trans = conn.BeginTranscation())
       {
          ...
       };
    } catch (Exception e)
    {
       MessageBox.Show(...);
    };

    However I don't think your data layer should be doing anything in the UI. Your try-catch and user update should happen at the higher level UI where the data access is actually triggered. But that is just me.

    Wiping a table is almost always going to cause problems if this is a multi-user system. So I'd also recommend you handle this better. Perhaps instead of wiping the entire table only remove the cars that the particular user is working with. With your current code every user has the same car row so your table would only ever have 1 car in it. That seems wrong to me.

    Also ensure that all your other database calls are properly cleaning up the connections otherwise you can get into locks. Finally note that most DB systems have a management tool that allows you to see connections and locks. This can be very useful for debugging deadlocks.


    Michael Taylor http://www.michaeltaylorp3.net

    • Proposed as answer by Naomi N Monday, July 20, 2020 5:20 PM
    • Marked as answer by TakeshiKitano Tuesday, July 21, 2020 1:40 PM
    Monday, July 20, 2020 5:07 PM