an attempt to make clsql more thread safe

As discussed earlier, I had a stab at making clsql more thread safe.

In summary:

1. fix some mysql issues; restore sigpipe in SBCL; initialize mysql library and threads properly.

2. put a new DATABASE-DESC object into the VIEW-DATABASE slot.  This is
used by UPDATE-XXX  methods to find or open an appropriate database. 

3. acquire/release databases to their threads, so that other threads won't
try to use a database in use by a thread.   Any database in use must be
owned by a thread and this is compared to the current thread.

The code is here:      https://sites.google.com/site/lithpthtuff/home/clsql-threads
in the form of a tarball of Nathan's git tree.

The tests all pass for mysql.  Some additional thread parallelism has been tested.

There is a longer description of changes in the text file.  

Hope this is of use to someone.

J.K.

On Apr 26, 2012, at 4:40 AM, Nathan Bird wrote:

> On Wed 25 Apr 2012 06:14:34 PM EDT, JTK wrote:
>> 
>> 
>> On Apr 25, 2012, at 5:22 AM, Nathan Bird wrote:
>> 
>>> 
>>> On 04/24/2012 05:46 PM, JTK wrote:
>>>> 
>>>>> 
>>>>> 
>>>> 
>>>> 
>>>> Thanks for the tips. Is there a reference to the the sigpipe fixes?
>>> 
>>> 
>>> The best I've got for you is just the IRC chatlogs: we dropped it and took a different tack when we saw that rabbit hole getting much deeper.
>> 
>> 
>> 
>> Thanks. I think I managed to understand the IRC thread, though it was a bit difficult to follow.
>> 
>> Reading this: http://dev.mysql.com/doc/refman/5.0/en/threaded-clients.html the mysql docs say:
>> 
>> ====
>> To avoid aborting the program when a connection terminates, MySQL blocks SIGPIPE on the first call tomysql_library_init(), mysql_init(), or mysql_connect().
>> If you want to use your own SIGPIPE handler, you should first call mysql_library_init() and then install your handler.
>> ====
>> 
>> So it seems that the whole signal problem could be fixed by having something like this in the clsql C
>> code for mysql, and calling it at the first use of connect, and never calling it again. Or calling it upon load
>> of the mysql back end.
>> 
>> 
>> /* not tested */
>> #include<signal.h>
>> 
>> /* initialize clsql, and restore old SIGPIPE handler, returning 0 on success, and 1,2,3 if failure
>> took place at 1) getting old signal 2) initializing mysql and 3) resetting old handler
>> 
>> The errno returned by sigaction (cases 1,3) or msql_library_init() (case 2) is returned in *nerror* */
>> 
>> int clsql_mysql_init(int *nerror)
>> {
>> struct sigaction old_sigpipe_sigaction;
>> 
>> /* save old SIGPIPE handler */
>> if (sigaction(SIGPIPE, NULL,&old_sigpipe_sigaction)) {
>> *nerror=errno;
>> return 1;
>> }
>> 
>> /* call mysql_library_init(), which changes the handler */
>> if (*nerror=mysql_library_init(0,NULL, NULL)) return 2;
>> 
>> /* and restore the old handler */
>> if (sigaction(SIGPIPE,&old_sigpipe_sigaction, NULL)) {
>> *nerror=errno;
>> return 3;
>> }
>> 
>> return 0;
>> }
>> 
>> 
>> And the per-thread problem could be called by having a threadwrapping macro
>> (with-clsql-thread ….) that will call mysql_init_thread() for the first call to
>> connect in this thread, and unwind-protecting a corresponding call to mysql_thread_end().
>> This (with-clsql-thread …) macro could also do stuff for the other back-ends too, if they need it.
>> And it could establish per-thread pooling, if desired, using per-thread special variables.
>> 
>> So unless I'm missing something, the solution is pretty simple. I'll try to get around to testing
>> it sometime soon.
> 
> 
> I'm hardly an expert here but that's all looking pretty good--  A few thoughts:
> 
> * Mysql blocks sigpipe b/c mysql expects that signal to be raised when a connection terminates and is trying to prevent that from aborting the process. What effect is this going to have on SBCL (and any other lisp that is using sigpipe) to receive this signal?  My very fuzzy knowledge of SBCL here is that it has a queue of stuff that is done by that handler when it receives the signal-- empty queue means everything works?
> 
> * It looks like mysql_thread_init is called automatically from various places: http://dev.mysql.com/doc/refman/5.0/en/mysql-thread-init.html  I suspect it already has some logic for "This thread is already initialized." We don't need to worry about the initial mysql_connect (automically calls all the necessary inits); we might should be concerned about when a connection is then used in another thread because of pooling. Perhaps just call mysql_thead_init every time we return a connection from the pool?  (Yes I'm ignoring thread_end right now).
> 
> * I'm a bit nervous that the with-clsql-thread macro is going to be problematic to use. In some scenarios (e.g. a webapp) there is a threadpool created below 'user code' and there is not a convenient place lexically to place this macro. In that case the options are:
>    * use this macro in an inner handler (i.e. a webrequest): is repeated calls to mysql_{thread_init,thread_end} take much time/resouces?
>    * Some sort of thread exit callback -- it doesn't look like pthread has this (if so mysql would probably use it), SBCL doesn't appear to have one, and I don't know about other lisps. At that point we're introducing a hook in CLSQL that its users are expected to call from their threads or use the macro.
>    * leaking memory at thread exit might not be problematic if threads last the lifetime of a process; e.g. fixed size threadpool or the lisp's main thread.
> 
> 
> === development notes ===
> The C snippet you put above probably can be put into db-mysql/clsql_mysql.c and then arrange to call that from db-mysql/mysql-loader.lisp clsql-sys:database-type-load-foreign.
> 
> If you don't have a public git clsql repo to work on this in then forking my github clsql repo is a good option: https://github.com/UnwashedMeme/clsql   I can then pull patches from you there to help run test suite and push up to the main git.b9.com repo when ready.
> 
> 
> Cheers,
> Nathan Bird

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