Fun with Distributed Transactions II
January 23, 2013 1 Comment
Short on examples, long on words…
Last week I did a quick post about a couple of the more obscure implications of using distributed transactions, in particular:
- The current impact on subquery materialisations
- The incompatibility with MVs
- The unusual situation of transactions with locks but no sessions
Anyway, earlier today I was having a closer look at some “DFS lock handle” wait events in an 11gR1 RAC database (not because there was an obvious problem but because I saw a few of them an wanted to have a closer look).
On a DFS lock handle wait, you have to decode P1 to find out what it’s all about as discussed by Riyaj Shamsudeen.
In my case, these were mostly DX and BB enqueues which are related to my old friend the distributed transaction.
Why am I still banging on about distributed transactions?
Well, you might think that they’re not very common but in the JDBC world they seem to be everywhere.
XA transactions are used (overused) throughout the JDBC world and so they might well be very relevant to any database that you’ve got with a JDBC app sitting atop.
A common pattern seen is to take some message off a queue, do something related to that message in the database, and use an XA transaction so that both stand or fail together.
In my previous post, I mentioned an AskTom thread discussing Materialized Views and distributed transactions.
I added some thoughts to that thread and Tom’s observation was
that the java/jdbc world for some reasons wishes to use an external resource manager so they have to do two phase commits against a single database isn’t what I was talking about. when I’ve seen the XA stuff – there is typically *one* database involved and it makes everything really complex,
hard to understand and slower than it needs to be.
Which might well be a fair point but still doesn’t change the fact that if your insert/update/delete comes in on an XA, then being unable to use a fast refresh on commit MV is really quite a restriction.
Anyway, back to my observations on these enqueues.
There’s not much information related to the BB enqueue but I believe that it’s related to the coordination of global transactions on a RAC cluster.
From 11gR1 distributed transactions can be processed on any instance in the cluster and these BB enqueues seem to be part of that picture. Prior to this change, any branches of a distributed transaction had to execute on the same node.
Whilst we’re on the subject, inevitably there are some bugs around these, in particular the BB enqueue and GTX processes (processes introduced to manage these enhanced cross cluster distributed transaction features).
The DX enqueue is perhaps more interesting.
It looks after “tightly coupled distributed transactions”.
So, WTF are tightly coupled distributed transactions and how do they differ from loosely coupled distributed transactions?
Tightly coupled transactions enable other branches of the transaction – and from our database perspective we’re only talking about multiple sessions in the same database which are part of the same distributed transaction – to:
- share each others locks and
- see each others changes.
The DX enqueue helps manage this.
As part of this, what the DX enqueue does is make sure that only one transaction branch is actively executing SQL at any one time.
So that’s potentially a pretty hefty point of serialisation then.
For more information, the Oracle whitepaper “XA and Oracle controlled Distributed Transactions” linked to below is a really good resource particularly the section “Distributed Transactions and Database Locking”.
However… just to emphasise the point …
In a tightly coupled tranasction, the DX enqueue is obtained before executing any statement.
By contrast, loosely coupled tranasctions do not need to get this DX lock before executing a statement, i.e. no serialisation between different transaction branches.
As the developer’s guide below says “loosely coupled transaction branches result in greater concurrency.”
So, not only is there an overhead to XA transactions but there is an additional overhead to tightly coupled transactions.
And, how many applications really use multiple transaction branches in a single database.
Very, very few, I wager!
And if they do, how many of those applications appreciate the serialisation involved anyway?
Now depending on your version, you might not see the DX and BB enqueues in your enqueue statistics.
In 11.1.0.7 I don’t seem to see anything in V$ENQUEUE_STAT for DX or BB.
There are however some relevant session/system statistic buckets:
- DX/BB enqueue lock foreground requests
- DX/BB enqueue lock foreground wait time
- DX/BB enqueue lock background gets
- DX/BB enqueue lock background get time
For more information see:
OraStory 
Pingback: Blocking Prepared XA Transaction | OraStory