Isolation

The third reason listed for using transactions was isolation. Consider an application suite in which multiple threads of control (multiple processes or threads in one or more processes) are changing the values associated with a key in one or more databases. Specifically, they are taking the current value, incrementing it, and then storing it back into the database.

Such an application requires isolation. Because we want to change a value in the database, we must make sure that after we read it, no other thread of control modifies it. For example, assume that both thread #1 and thread #2 are doing similar operations in the database, where thread #1 is incrementing records by 3, and thread #2 is incrementing records by 5. We want to increment the record by a total of 8. If the operations interleave in the right (well, wrong) order, that is not what will happen:

  1. thread #1  read record: the value is 2
  2. thread #2  read record: the value is 2
  3. thread #2  write record + 5 back into the database (new value 7)
  4. thread #1  write record + 3 back into the database (new value 5)

As you can see, instead of incrementing the record by a total of 8, we’ve incremented it only by 3 because thread #1 overwrote thread #2’s change. By wrapping the operations in transactions, we ensure that this cannot happen. In a transaction, when the first thread reads the record, locks are acquired that will not be released until the transaction finishes, guaranteeing that all writers will block, waiting for the first thread’s transaction to complete (or to be aborted).

Here is an example function that does transaction-protected increments on database records to ensure isolation:

  1. int
  2. main(int argc, char *argv)
  3. {
  4.     extern int optind;
  5.     DB *db_cats, *db_color, *db_fruit;
  6.     DB_ENV *dbenv;
  7.     int ch;
  9.     while ((ch = getopt(argc, argv, "")) != EOF)
  10.         switch (ch) {
  11.         case '?':
  12.         default:
  13.             usage();
  14.         }
  15.     argc -= optind;
  16.     argv += optind;
  18.     env_dir_create();
  19.     env_open(&dbenv);
  21.     /* Open database: Key is fruit class; Data is specific type. */
  22.     db_open(dbenv, &db_fruit, "fruit", 0);
  24.     /* Open database: Key is a color; Data is an integer. */
  25.     db_open(dbenv, &db_color, "color", 0);
  27.     /*
  28.      * Open database:
  29.      *    Key is a name; Data is: company name, cat breeds.
  30.      */
  31.     db_open(dbenv, &db_cats, "cats", 1);
  33.     add_fruit(dbenv, db_fruit, "apple", "yellow delicious");
  35.     add_color(dbenv, db_color, "blue", 0);
  36.     add_color(dbenv, db_color, "blue", 3);
  38.     return (0);
  39. }
  41. int
  42. add_color(DB_ENV *dbenv, DB *dbp, char *color, int increment)
  43. {
  44.     DBT key, data;
  45.     DB_TXN *tid;
  46.     int fail, original, ret, t_ret;
  47.     char buf[64];
  49.     /* Initialization. */
  50.     memset(&key, 0, sizeof(key));
  51.     key.data = color;
  52.     key.size = strlen(color);
  53.     memset(&data, 0, sizeof(data));
  54.     data.flags = DB_DBT_MALLOC;
  56.     for (fail = 0;;) {
  57.         /* Begin the transaction. */
  58.         if ((ret = dbenv->txn_begin(dbenv, NULL, &tid, 0)) != 0) {
  59.             dbenv->err(dbenv, ret, "DB_ENV->txn_begin");
  60.             exit (1);
  61.         }
  63.         /*
  64.          * Get the key.  If it exists, we increment the value.  If it
  65.          * doesn't exist, we create it.
  66.          */
  67.         switch (ret = dbp->get(dbp, tid, &key, &data, DB_RMW)) {
  68.         case 0:
  69.             original = atoi(data.data);
  70.             break;
  71.         case DB_LOCK_DEADLOCK:
  72.         default:
  73.             /* Retry the operation. */
  74.             if ((t_ret = tid->abort(tid)) != 0) {
  75.                 dbenv->err(dbenv, t_ret, "DB_TXN->abort");
  76.                 exit (1);
  77.             }
  78.             if (fail++ == MAXIMUM_RETRY)
  79.                 return (ret);
  80.             continue;
  81.         case DB_NOTFOUND:
  82.             original = 0;
  83.             break;
  84.         }
  85.         if (data.data != NULL)
  86.             free(data.data);
  88.         /* Create the new data item. */
  89.         (void)snprintf(buf, sizeof(buf), "%d", original + increment);
  90.         data.data = buf;
  91.         data.size = strlen(buf) + 1;
  93.         /* Store the new value. */
  94.         switch (ret = dbp->put(dbp, tid, &key, &data, 0)) {
  95.         case 0:
  96.             /* Success: commit the change. */
  97.             if ((ret = tid->commit(tid, 0)) != 0) {
  98.                 dbenv->err(dbenv, ret, "DB_TXN->commit");
  99.                 exit (1);
  100.             }
  101.             return (0);
  102.         case DB_LOCK_DEADLOCK:
  103.         default:
  104.             /* Retry the operation. */
  105.             if ((t_ret = tid->abort(tid)) != 0) {
  106.                 dbenv->err(dbenv, t_ret, "DB_TXN->abort");
  107.                 exit (1);
  108.             }
  109.             if (fail++ == MAXIMUM_RETRY)
  110.                 return (ret);
  111.             break;
  112.         }
  113.     }
  114. }

The DB_RMW flag in the DB->get() call specifies a write lock should be acquired on the key/data pair, instead of the more obvious read lock. We do this because the application expects to write the key/data pair in a subsequent operation, and the transaction is much more likely to deadlock if we first obtain a read lock and subsequently a write lock, than if we obtain the write lock initially.