Tech Off Thread

11 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Exceptions for control flow vs. extra data access

Back to Forum: Tech Off
  • User profile image
    Sven Groot

    I, like everyone here, am aware of the mantra that is so often repeated: use exceptions only for exceptional situations, not for normal control flow. What this means is that often you need to check for conditions that might cause exceptions before you perform the actual operation. So far, no problem.

    But I am creating a simple UI that allows the user to modify some data in a database (Access in this case). One of the values the user can change is the primary key. So obviously, it's not unthinkable that the user changes it to a value that already exists, thus violating the unique index constraint.

    So how to deal with this? One of the alternatives is to just perform the query, and catch the exception if the key is duplicate. This borders on using exceptions for control flow (since the user typing an invalid value isn't an "exceptional condition" mostly), and the error message from the exception also isn't terribly nice. I don't off-hand know if I can catch this exception, recognize it as a duplicate key exception and give a nicer message. The other option is to query the database for the existence of this value. However, the reason you should avoid exceptions for control flow is said to be because exceptions are expensive. Yes, well so is querying the database. Plus, since this is a multi user system, there's always the possibility of a race condition where someone else inserts that exact key value between my check and INSERT statement, which means the exception would still occur. I don't think OleDb supports the transaction isolation level to prevent this, and even if it did, using it would make the check even more expensive.

    What do you think is the best solution?

  • User profile image
    TommyCarlier

    You could do the test and the insert in 1 command, like the (untested) code below:

    using(IDataCommand command = connection.CreateCommand(
    @"IF EXISTS(SELECT * FROM [Table] WHERE [PK]=@PK)
          SELECT 1
    ELSE INSERT INTO [Table]([PK], [OtherField])
          VALUES(@PK, @OtherField)"))
    {
       command.Parameters.Add("@PK", pk);
       command.Parameters.Add("@OtherField", otherField);
       if (command.ExecuteScalar() != null)
       {
          // insert not succeeded
       }
    }
  • User profile image
    Sven Groot

    I can't, because I'm using Jet (Access) and it doesn't support IF statements in queries.

  • User profile image
    TommyCarlier

    If the performance is GOOD ENOUGH, I think it's best to check first, and then insert. To counter the race condition with multiple users, you can still catch the exception, as a secondary, EXCEPTIONAL, mechanism. It depends on how many simultaneous users there are. I think performance is about being FAST ENOUGH, and not necessarily about being AS FAST AS POSSIBLE.

  • User profile image
    Matthew van Eerde

    Why does the user need to change the primary key?

    I think in this case exceptions are a perfectly reasonable solution.

  • User profile image
    DoomBringer

    Yeah, check first, then do the insert.  As noted already, a condition could happen where one user grabs the previously free key and another user tries to use the same one.

    If the application is single user, then no worries.

  • User profile image
    Sven Groot

    Matthew van Eerde wrote:
    Why does the user need to change the primary key?

    Because it's what is later used to retrieve the data, and has to be user configurable due to the application requirements. I could use something else as the primary key, sure, but this value would still be needed and would still have to be unique.

    I decided to go with the exceptions because as it turns out the only sensible thing I could do if I detect it some other way is... throw an exception. Smiley

  • User profile image
    Lee_Dale

    if your app has the data on the client side can you check the client side data for a duplicate key first! then catch an exception if duplicate data has been added since the records were fecthed?

  • User profile image
    zhuo

    TommyCarlier wrote:
    If the performance is GOOD ENOUGH, I think it's best to check first, and then insert. To counter the race condition with multiple users, you can still catch the exception, as a secondary, EXCEPTIONAL, mechanism. It depends on how many simultaneous users there are. I think performance is about being FAST ENOUGH, and not necessarily about being AS FAST AS POSSIBLE.


    I concur.

    IMO, exception is there to catch condition for which you cannot anticipate, in this case the race condition would be one such scenario.

    I also agree that performance is about being FAST ENOUGH and not necessarily about being AS FAST AS POSSIBLE, in real time application the latter would apply but certainly not in most general business applications.

  • User profile image
    figuerres

    Sven Groot wrote:
    
    Matthew van Eerde wrote: Why does the user need to change the primary key?

    Because it's what is later used to retrieve the data, and has to be user configurable due to the application requirements. I could use something else as the primary key, sure, but this value would still be needed and would still have to be unique.

    I decided to go with the exceptions because as it turns out the only sensible thing I could do if I detect it some other way is... throw an exception. Smiley


    WTF?  then that's just EVIL in my book!

    I guess there is a reason but....

    the PK should be a non-changed value.

    in a few apps I have two coulmns that are each a "Canidate" one is never altered. the other could be if it had to.

    the one that is the PK in the database is never altered cause it's the relational link to other tables / rows.

  • User profile image
    Sven Groot

    figuerres wrote:
    WTF?  then that's just EVIL in my book!

    I guess there is a reason but....

    the PK should be a non-changed value.

    in a few apps I have two coulmns that are each a "Canidate" one is never altered. the other could be if it had to.

    the one that is the PK in the database is never altered cause it's the relational link to other tables / rows.

    Actually, I did end up using a different primary key field. Of course, the matter here still exists only now it applies to a field with a unique index constraint instead of the primary key.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.