update-records-from-instance and thread safety

Please forgive any ignorance or misunderstandings in what follows, and any presumption in offering
solutions to problems I might not fully understand ...

First, I'll describe my understanding of what happens with updates ...

When a STANDARD-DB-OBJECT is read from a database, its VIEW-DATABASE	
slot contains the database.

This allows UPDATE-RECORDS-FROM-INSTANCE to work.  When this method
sees the VIEW-DATABASE slot set, it does an SQL update; otherwise it uses an SQL insert.

You cannot override the VIEW-DATABASE slot using the :DATABASE keyword in
UPDATE-RECORDS-FROM-INSTANCE.  The VIEW-DATABASE takes precedence.

However, this presents a problem for threads.   If you read an object in one thread
and pass it to another and update it, then you could have two threads using the same
connection, which is very bad, perhaps fatal.

And if you use pooled connections, you can read an object in thread A, return the database to the pool,
where it gets picked up by thread B.  Then you update the object in thread A, and threads
A and B fight over the same connection.

So I was motivated to try several workarounds - 

1) not use pools - this used up connection resources and gave mysql errors.  I think there is some
     latency in closing a connection (port number) and having it be usable again.

2) wrote a safe version of UPDATE-RECORDS-FROM-INSTANCE that switched out
     VIEW-DATABASE  for fresh-from-the-pool database object in an UNWIND-PROTECT, and then calls
     the regular UPDATE-RECORDS-FROM-INSTANCE.  This is kludgy, but it works, if you use it everywhere.


Other better solutions might be 

3) put a lock inside every DATABASE object.  This would work, but when a database connection gets returned
  to the pool, you'd have mysterious locking up of thread A (the thread that owns an object being updated with
  VIEW-DATABASE in it) by thread B (who innocently acquired the database connection from the pool).

4) my suggestion - don't put the database in the VIEW-DATABASE slot, but instead put
     the CONNECTION-SPEC, DATABASE-TYPE, and maybe ENCODING of the database.     
     This would allow UPDATE-RECORDS-FROM-INSTANCE
     to use WITH-DATABASE to draw a database from the already-thread-safe database pool. 
     Problem: what about :IF-EXISTS?


5) Other solutions like http://russ.unwashedmeme.com/blog/?p=218  and http://common-lisp.net/project/clsql-fluid/ .
    I guess what I don't like about the first is that it defines a special thread-safe database class, when it would be nice
    to solve this problem everywhere.   I'm not sure about the status of the last one.  At any rate, it seems that the thread
    safety problem ought to be addressed, because it seems likely to hit anyone who uses clsql with threads.  

Finally, apologies in advance if I just barged in and spouted ignorance.

J.Klein

_______________________________________________
CLSQL mailing list
[email protected]
http://lists.b9.com/cgi-bin/mailman/listinfo/clsql