May prepared statements be shared across threads?

Question

using libmysqlclient_r.so i.e c mysql connector. as per current arch, In thread1 for connection1 prepared stmt will be created and cached . next for thread2 connection2 cached prepared stmt will be reused.

after executing, program throws segmentation fault at random location.

Note: If I keep only one thread and execute then it never throws segmentation fault.

I didn't not find anything in mysql documentation about it, even though I followed all the necessary steps as given under : http://dev.mysql.com/doc/refman/4.1/en/threaded-clients.html

Please suggest if sharing of prepare stmts across the thread is problem ??

"Please suggest if sharing of prepare stmts across the thread is problem ??" - No. Can you please add some code ? — user928204, Apr 13 '12 at 05:53
before posting code, can u please give some more explanation for your answer as 'NO' . Please consider above scenario - do u think it is workable? — user1145280, Apr 13 '12 at 06:33

score 1 · Answer 1 · answered Apr 13 '12 at 06:09

1

AFAIK , prepared statements are created/associated with the connection context. The prototype function needs the connection as the input parameter, so you can not reuse the prepared statement prepared in the context of connection_A in connection_B...

MYSQL_STMT *mysql_stmt_init(MYSQL *mysql)

answered Apr 13 '12 at 06:09

Malkocoglu

2,522
2
26
32

thanks for your reply, is there any official document available on this scenario. – user1145280 Apr 13 '12 at 07:11
1

@user1145280 : Function prototype alone tells this fact. Other databases function the same way. The definitive guide is the mysql client API documentation... – Malkocoglu Apr 13 '12 at 07:19
A more interesting question is 'can a connection context be used across threads', to which the answer is 'yes, but only one thread can use it at a time'. – Jonathan Leffler Apr 13 '12 at 07:30
@Jonathan Leffler : Yes, I think you are correct. But, I would check the mysql client source to see if internal data structures are kept as thread_local_storage or not to be sure... – Malkocoglu Apr 13 '12 at 07:53
I did not say 'I will' and I certainly won't. I am not using mysql at the moment, there is no reason for me to analyse mysql source code. – Malkocoglu Apr 16 '12 at 10:57
as per your answer, do u mean that we should not prepare the statement in one thread, then pass it for execution to another thread? – user1145280 Apr 17 '12 at 05:44
@user1145280: Do not prepare a statement in thread_A and use it in thread_B, better safe then sorry. Even if there was no problem doing that, I do not see a reason to pass a prepared statement handle from one thread to another, it seems pointless to me... – Malkocoglu Apr 17 '12 at 10:09
as per our arch, there would be lakhs of db lookup requests going to one db server. every thread will have db connection associated with it, and for every connection one prepare stmt will be created. as per max count of prepare stmt allowed are fixed and hence we maintained prepare stmt cache. prepare stmt once created will be cached, and next time onwards will be reused. currently while testing, sometimes it works for 10K requests and in next iteration it throws segmentation fault at random locations. – user1145280 Apr 17 '12 at 11:16
I do not know the inner workings of mysql. If there is an upper limit on number of prepared statements and if mysql allows sharing of prepared statements between threads, then it is OK. Better ask this on mysql forums to their developers ! – Malkocoglu Apr 17 '12 at 12:27
I asked this but there is no proper response so I have posted this question on stackoverflow – user1145280 Apr 17 '12 at 12:54

score 1 · Answer 2 · edited Oct 23 '18 at 17:26

My personal experience with these is that they may be shared as long as some kind of locking mechanism is set in place and all threads use the same connection.

I have had some strange double frees and memory corruptions that might have been originated by two threads setting parameters to prepared statements and launching them at roughly the same time. Once I used mutexes to prevent threads from doing that, the crashes disappeared. The performance penalty was not that high: very little time was spent in setting the prepared statement parameters and in the queries themselves, tough your mileage may vary if your queries take long to execute.

May prepared statements be shared across threads?

2 Answers2