Kevin Brown wrote:
On Saturday 18 March 2006 12:31, Florian G. Pflug wrote:
I've been creating a databased application with PostgreSQL for a while
now and have loved it, though we just recently stumbled on an interesting
bug.
I have a particular dialog which can do all kinds of incredibly
complicated things to the database. Of course I didn't want to have to
write the "undo it to the database" code, nor did I want to force the
user into being screwed with a stray click.
So when the dialog opens, it executes a BEGIN, and then based on whether
they clicked ok or cancel on the dialog, it'd COMMIT or ROLLBACK. This
worked fine for me in testing, but now that I have two people using this
app simulatneously, if they both click on ok at the same time, postgres
seemingly deadlocks. My setup is such that I have n ruby clients talking
with DRb to another ruby process on the server, which then does all the
communication to postgres. Each user has his/her own database
connection.
What ruby database library are you using?
The C extension.
Since ruby doesn't use native threads, but instead implements it's own
threads (purely in user-space), one thread can block your whole app - e.g,
if the thread tries to read from a socket where not data is available, and
therefor the kernel puts the process to sleep until data becomes available.
For the "normal" read() and write() operations exposed to the ruby
programming this is solved internally by ruby (it checks the
filedescriptions state, and only reads if the read won't block). But
extensions, especially the ones talking over the network, like the postgres
client, can still cause this problem. In the "raw" postgres client
extension for ruby, you'll find two methods that can execute a query -
query() and query_async(). query() will block the _whole_ ruby interpreter
until query results are available, while query_async() will block only the
thread that executed the query.
Yes, but my question is why is the query unable to complete.
Well, see below ;-)
This one-thread-blocks-all behaviour can lead to "interesting" dead-locks.
Assume, for example, the following situation, with A and B being two ruby
threads. A1) update table where id=1
B1) update table where id=1
-----
B2) commit
A2) commit
Now, the query B1 will block until A2 is executed (because the record is
already locked). But if B1 blocks, and you used query() instead of
query_async(), then the _whole_ ruby interpreter is blocked, and therefor
A2 will never be executed. Bingo! you got a deadlock, and postgres has no
chance to ever detect this.
So what you're saying is that my commits and rollbacks should be async,
correct?
No, you should always use _async(), and never, ever use query() or exec()
in a multi-threaded app.
Of course, not using threads makes the problem even worse - the only
solution is to use the _async() functions. DBI lets you set a flag (forgot
it's name) that forces DBD::Pg to use the _async() functions - if you don't
use DBI just replace all calls to query() and exec() with query_async() and
exec_async().
I don't quite understand how a select can be an async call.... I need the
data to proceed. If you're saying on my inserts, deletes, etc, then that I
can understand.
Don't be fooled by the word async()... It's still synchronous for the
thread that calls it - the _thread_ is blocked until the query is done,
and query_async() returns the result, just as query() does. The difference
is only for all _other_ threads. All other threads can continue to run
while the one thread waits for query results _only_ if the one thread
used query_async(). If it used query(), then you _whole_ interpreter is
blocked until the query is finished.
The naming of these function is quite strange - I was fooled by this myself,
and so was whoever wrote the DBD-module for pg, because he used the non-async
functions too ;-)
To rephrase - you don't usually need to change anything in your program when
using query_async() instead of query(). It'll just work better ;-).
greetings, Florian Pflug
