Re: updating before insert

Thinking again, perhaps using a UNIQUE INDEX and INSERT OR REPLACE will work the same as my proposed method. I'll have to try it out later.

> I was afraid you were going to say that because that's what I am doing
> already. [...]
> Except I just noticed its primary key instead of unique index.   reading
> up on
> table creation apparently you can describe keys as unique.  Is there
> anyway to
> retrofit uniqueness to a key?  Can I define  UNIQUE PRIMARY KEY?

Primary keys are always UNIQUE. Or they wouldn't be keys ;-)

The purpose of a UNIQUE INDEX is to make sure other column (combinations) are unique. For example:

create table foo (
  foo_id integer primary key,
  abbrev varchar(10) unique,
  description varchar(80)
);

Here, the UNIQUE in the table definition creates an implicit UNIQUE INDEX on the abbrev column.

> [...]

> okay.  I think I will lock in both cases because there is a race condition
> in
> each case.  In the first case (update versus insert) the race condition is
> the
> between the insert and the update.  Admittedly, it's a small window but
> one
> nevertheless.  I should just grab an exclusive lock before the insert and
> release after the commit. 

Or catch the error and retry.

> The other problem where I  test first (select)I
> should also grab exclusive lock.  Then around all other queries I should
> place a
> shared lock.

Normally you don't program databases this way. Using locks or simulating them via (database specific) means kills performance and makes the code more complicated.

Catching exceptions in exceptional cases is ok and in most cases works just as well. Plus no performance is lost in the non-exception case. Something like this pseudocode should work for you:

counter = 0
while 1:
  try:
    con.execute("insert or replace ...")
    break
  except sqlite.IntegrityError, e:
    pass

> After all of Dijkstra's work with semaphores, one would think that the
> bright
> people that create SQL could have been a little smarter about locking. 
> This
> whole transaction/retries stuff is a tad fuzzy.

Database programming is a slightly different world (declarative vs. imperativ/object-oriented) and has sometimes a different cultur than the application programming world.

Every database has specific means of locking (SELECT FOR UPDATE, LOCK TABLE, etc.) but AFAIK they are all not in the SQL standard. I personally hardly ever needed them. In my job, ca. 70 % of the time I do Oracle database programming and performance tuning the last 3 years. And I've only needed SELECT FOR UPDATE once for thousands of lines of PL/SQL code.

It's a near-realtime OLTP system that's processing 150+ transactions per second, so I have some experience keeping and improving performance acceptable despite implementing new features ;-)