1 |
=head1 NAME |
2 |
|
3 |
Coro::Mysql - let other threads run while doing mysql requests |
4 |
|
5 |
=head1 SYNOPSIS |
6 |
|
7 |
use Coro::Mysql; |
8 |
|
9 |
my $DBH = Coro::Mysql::unblock DBI->connect (...); |
10 |
|
11 |
=head1 DESCRIPTION |
12 |
|
13 |
(Note that in this manual, "thread" refers to real threads as implemented |
14 |
by the Coro module, not to the built-in windows process emulation which |
15 |
unfortunately is also called "threads") |
16 |
|
17 |
This module replaces the I/O handlers for a database connection, with the |
18 |
effect that "patched" database handles no longer block the all threads of |
19 |
a process, but only the thread that does the request. |
20 |
|
21 |
This can be used to make parallel sql requests using Coro, or to do other |
22 |
stuff while mysql is rumbling in the background. |
23 |
|
24 |
=head2 CAVEAT |
25 |
|
26 |
Note that this module must be linked against exactly the same (shared, |
27 |
possibly not working with all OSes) F<libmysqlclient> library as |
28 |
DBD::mysql, otherwise it will not work. |
29 |
|
30 |
Also, this module requires a header file that apparently isn't installed |
31 |
everywhere (F<violite.h>), and therefore comes with it's own copy, which |
32 |
might or might not be compatible to the F<violite.h> of your library - |
33 |
when in doubt, make sure all the libmysqlclient header files are installed |
34 |
and delete the F<violite.h> header that comes with this module. |
35 |
|
36 |
On the good side, this module does a multitude of checks to ensure that |
37 |
the libray versions match on the binary level, so on incompatibilities you |
38 |
should expect an exception when trying to unblock a handle, rather than |
39 |
data corruption. |
40 |
|
41 |
Also, while this module makes database handles non-blocking, you still |
42 |
cannot run multiple requests in parallel on the same database handle. If |
43 |
you want to run multiple queries in parallel, you have to create multiple |
44 |
database connections, one for each thread that runs queries. Not doing |
45 |
so can corrupt your data - use a Coro::Semaphore to protetc access to a |
46 |
shared database handle when in doubt. |
47 |
|
48 |
If you make sure that you never run two or more requests in parallel, you |
49 |
can freely share the database handles between threads, of course. |
50 |
|
51 |
=head2 SPEED |
52 |
|
53 |
This module is implemented in XS, and as long as mysqld replies quickly |
54 |
enough, it adds no overhead to the standard libmysql communication |
55 |
routines (which are very badly written, btw.). In fact, since it has a |
56 |
more efficient buffering and allows requests to run in parallel, it often |
57 |
decreases the actual time to run many queries considerably. |
58 |
|
59 |
For very fast queries ("select 0"), this module can add noticable overhead |
60 |
(around 15%, 7% when EV can be used) as it tries to switch to other |
61 |
coroutines when mysqld doesn't deliver the data immediately, although, |
62 |
again, when running queries in parallel, they will usually execute faster. |
63 |
|
64 |
For most types of queries, there will be no extra latency, especially on |
65 |
multicore systems where your perl process can do other things while mysqld |
66 |
does its stuff. |
67 |
|
68 |
=head2 LIMITATIONS |
69 |
|
70 |
This module only supports "standard" mysql connection handles - this |
71 |
means unix domain or TCP sockets, and excludes SSL/TLS connections, named |
72 |
pipes (windows) and shared memory (also windows). No support for these |
73 |
connection types is planned, either. |
74 |
|
75 |
=head1 CANCELLATION |
76 |
|
77 |
Cancelling a thread that is within a mysql query will likely make the |
78 |
handle unusable. As far as Coro::Mysql is concerned, the handle can be |
79 |
safely destroyed, but it's not clear how mysql itself will react to a |
80 |
cancellation. |
81 |
|
82 |
=head1 FUNCTIONS |
83 |
|
84 |
Coro::Mysql offers a single user-accessible function: |
85 |
|
86 |
=over 4 |
87 |
|
88 |
=cut |
89 |
|
90 |
package Coro::Mysql; |
91 |
|
92 |
use strict qw(vars subs); |
93 |
no warnings; |
94 |
|
95 |
use Scalar::Util (); |
96 |
use Carp qw(croak); |
97 |
|
98 |
use Guard; |
99 |
use AnyEvent (); |
100 |
use Coro (); |
101 |
use Coro::AnyEvent (); # not necessary with newer Coro versions |
102 |
|
103 |
# we need this extra indirection, as Coro doesn't support |
104 |
# calling SLF-like functions via call_sv. |
105 |
|
106 |
sub readable { &Coro::Handle::FH::readable } |
107 |
sub writable { &Coro::Handle::FH::writable } |
108 |
|
109 |
BEGIN { |
110 |
our $VERSION = 1.25; |
111 |
|
112 |
require XSLoader; |
113 |
XSLoader::load Coro::Mysql::, $VERSION; |
114 |
} |
115 |
|
116 |
=item $DBH = Coro::Mysql::unblock $DBH |
117 |
|
118 |
This function takes a DBI database handles and "patches" it |
119 |
so it becomes compatible to Coro threads. |
120 |
|
121 |
After that, it returns the patched handle - you should always use the |
122 |
newly returned database handle. |
123 |
|
124 |
It is safe to call this function on any database handle (or just about any |
125 |
value), but it will only do anything to L<DBD::mysql> handles, others are |
126 |
returned unchanged. That means it is harmless when applied to database |
127 |
handles of other databases. |
128 |
|
129 |
It is also safe to pass C<undef>, so code like this is works as expected: |
130 |
|
131 |
my $dbh = DBI->connect ($database, $user, $pass)->Coro::Mysql::unblock |
132 |
or die $DBI::errstr; |
133 |
|
134 |
=cut |
135 |
|
136 |
sub unblock { |
137 |
my ($DBH) = @_; |
138 |
|
139 |
if ($DBH && $DBH->{Driver}{Name} eq "mysql") { |
140 |
my $sock = $DBH->{sock}; |
141 |
|
142 |
open my $fh, "+>&" . $DBH->{sockfd} |
143 |
or croak "Coro::Mysql unable to clone mysql fd"; |
144 |
|
145 |
if (AnyEvent::detect ne "AnyEvent::Impl::EV" || !_use_ev) { |
146 |
require Coro::Handle; |
147 |
$fh = Coro::Handle::unblock ($fh); |
148 |
} |
149 |
|
150 |
_patch $sock, $DBH->{sockfd}, $DBH->{mysql_clientversion}, $fh, tied *$$fh; |
151 |
} |
152 |
|
153 |
$DBH |
154 |
} |
155 |
|
156 |
1; |
157 |
|
158 |
=back |
159 |
|
160 |
=head1 USAGE EXAMPLE |
161 |
|
162 |
This example uses L<PApp::SQL> and L<Coro::on_enter> to implement a |
163 |
function C<with_db>, that connects to a database, uses C<unblock> on the |
164 |
resulting handle and then makes sure that C<$PApp::SQL::DBH> is set to the |
165 |
(per-thread) database handle when the given thread is running (it does not |
166 |
restore any previous value of $PApp::SQL::DBH, however): |
167 |
|
168 |
use Coro; |
169 |
use Coro::Mysql; |
170 |
use PApp::SQL; |
171 |
|
172 |
sub with_db($$$&) { |
173 |
my ($database, $user, $pass, $cb) = @_; |
174 |
|
175 |
my $dbh = DBI->connect ($database, $user, $pass)->Coro::Mysql::unblock |
176 |
or die $DBI::errstr; |
177 |
|
178 |
Coro::on_enter { $PApp::SQL::DBH = $dbh }; |
179 |
|
180 |
$cb->(); |
181 |
} |
182 |
|
183 |
This function makes it possible to easily use L<PApp::SQL> with |
184 |
L<Coro::Mysql>, without worrying about database handles. |
185 |
|
186 |
# now start 10 threads doing stuff |
187 |
async { |
188 |
|
189 |
with_db "DBI:mysql:test", "", "", sub { |
190 |
sql_exec "update table set col = 5 where id = 7"; |
191 |
|
192 |
my $st = sql_exec \my ($id, $name), |
193 |
"select id, name from table where name like ?", |
194 |
"a%"; |
195 |
|
196 |
while ($st->fetch) { |
197 |
... |
198 |
} |
199 |
|
200 |
my $id = sql_insertid sql_exec "insert into table values (1,2,3)"; |
201 |
# etc. |
202 |
}; |
203 |
|
204 |
} for 1..10; |
205 |
|
206 |
=head1 SEE ALSO |
207 |
|
208 |
L<Coro>, L<PApp::SQL> (a user friendly but efficient wrapper around DBI). |
209 |
|
210 |
=head1 HISTORY |
211 |
|
212 |
This module was initially hacked together within a few hours on a long |
213 |
flight to Malaysia, and seems to have worked ever since, with minor |
214 |
adjustments for newer libmysqlclient libraries. |
215 |
|
216 |
=head1 AUTHOR |
217 |
|
218 |
Marc Lehmann <schmorp@schmorp.de> |
219 |
http://home.schmorp.de/ |
220 |
|
221 |
=cut |
222 |
|