|
|
1 | NAME |
|
|
2 | Coro::Mysql - let other threads run while doing mysql requests |
|
|
3 | |
|
|
4 | SYNOPSIS |
|
|
5 | use Coro::Mysql; |
|
|
6 | |
|
|
7 | my $DBH = Coro::Mysql::unblock DBI->connect (...); |
|
|
8 | |
|
|
9 | DESCRIPTION |
|
|
10 | (Note that in this manual, "thread" refers to real threads as |
|
|
11 | implemented by the Coro module, not to the built-in windows process |
|
|
12 | emulation which unfortunately is also called "threads") |
|
|
13 | |
|
|
14 | This module "patches" DBD::mysql database handles so that they do not |
|
|
15 | block the whole process, but only the thread that they are used in. |
|
|
16 | |
|
|
17 | This can be used to make parallel sql requests using Coro, or to do |
|
|
18 | other stuff while mysql is rumbling in the background. |
|
|
19 | |
|
|
20 | CAVEAT |
|
|
21 | Note that this module must be linked against exactly the same |
|
|
22 | libmysqlclient library as DBD::mysql, otherwise it will not work. |
|
|
23 | |
|
|
24 | Also, while this module makes database handles non-blocking, you still |
|
|
25 | cannot run multiple requests in parallel on the same database handle. If |
|
|
26 | you want to run multiple queries in parallel, you have to create |
|
|
27 | multiple database connections, one for each thread that runs queries. |
|
|
28 | Not doing so can corrupt your data - use a Coro::Semaphore when in |
|
|
29 | doubt. |
|
|
30 | |
|
|
31 | If you make sure that you never run two or more requests in parallel, |
|
|
32 | you can freely share the database handles between threads, of course. |
|
|
33 | |
|
|
34 | Also, this module uses a number of "unclean" techniques (patching an |
|
|
35 | internal libmysql structure for one thing) and was hacked within a few |
|
|
36 | hours on a long flight to Malaysia. |
|
|
37 | |
|
|
38 | It does, however, check whether it indeed got the structure layout |
|
|
39 | correct, so you should expect perl exceptions or early crashes as |
|
|
40 | opposed to data corruption when something goes wrong during patching. |
|
|
41 | |
|
|
42 | SPEED |
|
|
43 | This module is implemented in XS, and as long as mysqld replies quickly |
|
|
44 | enough, it adds no overhead to the standard libmysql communication |
|
|
45 | routines (which are very badly written, btw.). |
|
|
46 | |
|
|
47 | For very fast queries ("select 0"), this module can add noticable |
|
|
48 | overhead (around 15%) as it tries to switch to other coroutines when |
|
|
49 | mysqld doesn't deliver the data instantly. |
|
|
50 | |
|
|
51 | For most types of queries, there will be no overhead, especially on |
|
|
52 | multicore systems where your perl process can do other things while |
|
|
53 | mysqld does its stuff. |
|
|
54 | |
|
|
55 | LIMITATIONS |
|
|
56 | This module only supports "standard" mysql connection handles - this |
|
|
57 | means unix domain or TCP sockets, and excludes SSL/TLS connections, |
|
|
58 | named pipes (windows) and shared memory (also windows). No support for |
|
|
59 | these connection types is planned, either. |
|
|
60 | |
|
|
61 | FUNCTIONS |
|
|
62 | Coro::Mysql offers a single user-accessible function: |
|
|
63 | |
|
|
64 | $DBH = Coro::Mysql::unblock $DBH |
|
|
65 | This function takes a DBI database handles and "patches" it so it |
|
|
66 | becomes compatible to Coro threads. |
|
|
67 | |
|
|
68 | After that, it returns the patched handle - you should always use |
|
|
69 | the newly returned database handle. |
|
|
70 | |
|
|
71 | It is safe to call this function on any database handle (or just |
|
|
72 | about any value), but it will only do anything to DBD::mysql |
|
|
73 | handles, others are returned unchanged. That means it is harmless |
|
|
74 | when applied to database handles of other databases. |
|
|
75 | |
|
|
76 | USAGE EXAMPLE |
|
|
77 | This example uses PApp::SQL and Coro::on_enter to implement a function |
|
|
78 | "with_db", that connects to a database, uses "unblock" on the resulting |
|
|
79 | handle and then makes sure that $PApp::SQL::DBH is set to the |
|
|
80 | (per-thread) database handle when the given thread is running (it does |
|
|
81 | not restore any previous value of $PApp::SQL::DBH, however): |
|
|
82 | |
|
|
83 | use Coro; |
|
|
84 | use Coro::Mysql; |
|
|
85 | use PApp::SQL; |
|
|
86 | |
|
|
87 | sub with_db($$$&) { |
|
|
88 | my ($database, $user, $pass, $cb) = @_; |
|
|
89 | |
|
|
90 | my $dbh = Coro::Mysql::unblock DBI->connect ($database, $user, $pass) |
|
|
91 | or die $DBI::errstr; |
|
|
92 | |
|
|
93 | Coro::on_enter { $PApp::SQL::DBH = $dbh }; |
|
|
94 | |
|
|
95 | $cb->(); |
|
|
96 | } |
|
|
97 | |
|
|
98 | This function makes it possible to easily use PApp::SQL with |
|
|
99 | Coro::Mysql, without worrying about database handles. |
|
|
100 | |
|
|
101 | # now start 10 threads doing stuff |
|
|
102 | async { |
|
|
103 | |
|
|
104 | with_db "DBI:mysql:test", "", "", sub { |
|
|
105 | sql_exec "update table set col = 5 where id = 7"; |
|
|
106 | |
|
|
107 | my $st = sql_exec \my ($id, $name), |
|
|
108 | "select id, name from table where name like ?", |
|
|
109 | "a%"; |
|
|
110 | |
|
|
111 | while ($st->fetch) { |
|
|
112 | ... |
|
|
113 | } |
|
|
114 | |
|
|
115 | my $id = sql_insertid sql_exec "insert into table values (1,2,3)"; |
|
|
116 | # etc. |
|
|
117 | }; |
|
|
118 | |
|
|
119 | } for 1..10; |
|
|
120 | |
|
|
121 | SEE ALSO |
|
|
122 | Coro, PApp::SQL (a user friendly but efficient wrapper around DBI). |
|
|
123 | |
|
|
124 | AUTHOR |
|
|
125 | Marc Lehmann <schmorp@schmorp.de> |
|
|
126 | http://home.schmorp.de/ |
|
|
127 | |