Search code examples
databaseodbcread-writetransaction-isolationtable-locking

how to lock a DB table or a range of rows for writing?

I have a simple table with a primary key. Most of the read operations fetch one row by the exact value of the key.

The data in each row maintains some relationship with rows before and after it in the key order. So when I insert a new row I need to read the 2 rows between which it is going to enter, make some computation and then to insert.

The concern, clearly, is that at the same time another connection may add a row with a key value in the same interval. I am covered if it is exactly the same value of the key as the second insert would fail, but if the key value is different but in the same interval the relationship may be broken.

The solution seems to be to lock the whole table for writing when I decide to add a new row, or (if possible, which I doubt) to lock an interval of key values. Yet I'd prefer that read-only transactions would not be blocked at that time.

I am using ODBC with libodbc++ wrapper for C++ in the client program and IBM DB2 free edition (although the DB choice may still change). This is what I thought of doing:

  • start the connection in the auto-commit and default isolation mode
  • when need to add a new row, set auto-commit to false and isolation mode to serialized
  • read the rows before and after the new key value
  • compute and insert the new row
  • commit
  • return back to the auto-commit and default isolation mode

Will this do the job? Will other transactions be allowed to read at the same time? Are there other/better ways to do it?

BTW, I don't see in the libodbc++ i/f a way to specify a read-only transaction. Is it possible in odbc?

EDIT: thanks for the very useful answers, I had trouble selecting one.

Solution

  • If your database is in SERIALIZABLE mode, you won't have any issues at all. Given a key K, to get the previous and next keys you have to run the following queries:

    select key from keys where key > K order by key limit 1;      # M?
select key from keys where key < K order by key desc limit 1; # I?

    The above works in MySQL. This equivalent query works in DB2 (from the comments):

    select key from keys where key = (select min(key) from keys where key > K);
select key from keys where key = (select max(key) from keys where key < K);

    The first query sets up a range lock that prevents other transactions from inserting a key greater than K and less than or equal to M.

    The second query sets up a range lock that prevents other transactions from inserting a key less than K and greater than or equal to I.

    The unique index on the primary key prevents K from being inserted twice. So you're completely covered.

    This is what transactions are about; so you can write your code as if the entire database is locked.

    Note: This requires a database that supports true serializability. Fortunately, DB2 does. Other DBMS's that support true serializability: SQLServer, and MySQL/InnoDB. DBMS's that don't: Oracle, PostgreSQL!