Re: Where's my Derby?

On Wed, 07 Apr 2010 21:11:01 +0000, Andreas Leitgeb wrote:
 


That was me being cautious since we don't know what DBMS the OP has in
mind. For the following code to work the DBMS *must* support explicit
transaction control, serialised transaction mode and (probably) the
ability to set the lock timeout period. If we have all that the code is
straight forward:

set_session_conditions
{
   set auto-commit off;
   set transaction serialisation;
   set lock timeout = 5 seconds; // needs to be at least 2x the max
                                  // transaction execution time.
}

add_entry
{
   start transaction // may be implied
   select count(*) from table where key='?'
   if (count == zero)
      insert into table (key, value) values ('?', '?');
   commit
}

If there are no explicit transactions, then all bets are off: it can't
be done without serialising the database access, i.e. you'll need to
queue updates for execution by a single worker thread - or something like
that.

If you can't serialise transactions, then you *must* make the key unique
and be prepared for inserts to fail with a duplicate key exception. You
must also be prepared to rollback and retry if the transaction times out.


Depends what locks you're using - table, page or row. A decent DBMS will
let you specify at least table or row locking and some may allow page
locking as well. It doesn't actually matter in this case since there's
only a single row accessed during a transaction. However, the bigger the
scope of the lock the more chance that a concurrent transaction will fail
due to a lock conflict and need to be retried

Ideally you'd use row locking: then the select will lock zero or 1 rows
and the insert, if run, will lock one row. Page locking extends this to
the page containing the row and table locking prevents any concurrency
for the duration of the transaction.

--
martin@ | Martin Gregorie
gregorie. | Essex, UK
org |