Re: Transactions

On Saturday 18 March 2006 12:31, Florian G. Pflug wrote:
> Kevin Brown 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.

> 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?

> 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.

> greetings, Florian Pflug