Re: updating before insert

Dennis Lee Bieber wrote:
> On Sun, 12 Aug 2007 10:06:59 -0400, "Eric S. Johansson"
> <[email protected]> declaimed the following in
> gmane.comp.python.db.pysqlite.user:
> 
> 
>> I think that's my point.  The whole concept of a transaction is kind of fuzzy. 
>> Like you say, you either complete successfully (which raises the question how 
>> can you tell) or you need to roll back.  But if you need to have the transaction 
>> succeeds, you need to retry repeatedly until you succeed.  Conceptually, it 
>> would've been easier to have a lock on a record.  But transaction does let 
>> design requests that have multiple updates or inserts which increases the 
>> probability of failure and increases the number of retries which means I'm 
>> feeling very confused about why anyone would do such a thing in the first place. 
>>   :-)
>>
> 	The classic example of transaction is the bank account transfer,
> where one is subtracting a value from account 1, and adding it to
> account 2.
> 
> 	update account
> 	set total = total - amount
> 	where account = 1;
> 	update account
> 	set total = total + amount
> 	where account = 2;

  I would approach this:

While not nonblocking lock 1 and nonblocking lock 2
	#didn't get both locks. unlock to prevent deadlock.
	Sleep,
         release

update account
set total = total - amount
where account = 1;
update account
set total = total + amount
here account = 2;

release lock 1
release lock 2

If I was doing this as a transaction, it would be something like:

While True
  	update account
  	set total = total - amount
  	where account = 1;
  	update account
  	set total = total + amount
  	where account = 2;
	if transaction succeeded: break
         unwind transaction
	
effectively creating a busy loop trying and retrying until it succeeds.

> 	I don't know your application design, but... if you are doing
> something like:
> 
> select something
> fetch & display to user
> wait for user responses
> update (or insert) based on responses
> commit
> 
> you should break it up... Transactions work best when the entire
> transaction is "available" at once, or needs very little time.
> 
> select something
> fetch & display
> commit				#unlock database... I don't know the behavior
> 					#of cursors enough to know if it could be
> 					#select/commit/fetch
> 
> wait for user responses
> update (using primary key from select) or insert
> commit
> 
> 	If you need to ensure that updates are going in on unchanged
> records, then the second transaction may become
> 
> select on primary key of updates
> fetch and compare records to original fetched record
> if identical
> 	update using primary key and modified fields
> commit

and what if you need to go to the user yet again to confirm that it's okay to 
change something that has changed while they were working?  How many times can 
you get away with that before totally pissing off the user?  I suspect not very 
many.  :-) fortunately I don't have to deal with that problem.  It's a simple 
"does this record exists, if so, update a timestamp or some other field and then 
write out again.

> 
> 	The locking mechanism is somewhat complex in one way... It permits
> multiple readers until a transaction requests a write operation; the
> write is delayed until all existing readers exit, new readers are
> blocked until the write completes. When all other reader transactions
> exit, the write executes, and the lock is reset to allow readers to
> enter. If one of the "active readers" also tries to write, it gets a
> failure, due to the pending active write. And yes, by that, a select
> does start a transaction in the shared read level. That's why I
> suggested above commiting the read while waiting to determine what
> changes need to be made, then starting a new transaction (possibly
> checking to ensure no other changed the data).

That's not complex.  as any OS weenie will tell you, it's a simple lock shared, 
lock exclusive sequence.  Locks are granted shared until the first exclusive 
lock.  Then, as you say, all shared locks must be released before the exclusive 
lock can run.  If you want to be complicated, you can aggregate exclusive locks 
before allowing any further release of shared locks.  Rationale being that by 
aggregating writes, you can optimize the write path and read paths versus 
treating the write path as an exception or keeping traffic too small to be 
optimized.


> 
> 	Firebird is more of a traditional client/server design, so only the
> server library opens the database file... (Confusing though as one can
> build as embedded, classic server, or superserver)

that is impressive.  just took a look at the documentation and it does look 
impressive.  Maybe next project (i.e. starting Monday or Tuesday)


-- 
Speech-recognition in use.  It makes mistakes, I correct some.