[pysqlite] problems grokking SQL

this probably belongs on the SQLite list proper but, I can only handle so many
negative responses a day.  :-)

First I want to say that it wasn't for pysqite, I never would have gotten as far
as I have.  Thank you for developing it.

I'm running into fundamental problems with my understanding of SQL.  To me,
simple things are really hard to do.  For example, the two most common problems
I run into are the create/read/modify/write idiom and random locking failures.

On create/read/modify/write, if you can do it all in SQL (i.e. a simple
increment or setting a value, it's not horribly hard.  It's just painful.  But
if you're doing something which requires modifying a value based on what you
read, then it become significantly more difficult.  Things get even worse if you
need to insert if the record doesn't exist and modify if it does.

I'm trying to collect what I do into a set of idioms I can use by cut and paste
inheritance.  I can use some help trying to create idioms for the above.

The second problem is random locking.  I know this is a constant challenge for
SQL light and I am getting ready to put simple OS level locking around all SQL
light calls just to make the problem go away.   for the time being, I am trying
very hard to keep the SQL calls very short and light.  I also commit in a lot of
places as a superstitious attempt to keep the global locks from hanging around

Any insight on how to solve these problems would be welcome and they would be
followed by a bunch of questions as I try to understand what you're trying to
explain.

---eric