I read about Cassandra 2's lightweight transactions. Is the consistency level of such a write always at QUORUM? Would this mean that even if I have a multi data center setup with 100s of nodes, then quorum of the entire cluster (majority of the row's replicas across all data centers) is involved? Won't this be really slow and wont it affect availability?
Can we do LOCAL_QUORUM or EACH_QUORUM consistency? This would be preferred if writers for data replicated across multiple data centers would always originate from a specific data center only.
The suggested Consistency Level for lightweight transactions is SERIAL. Behind the scenes however SERIAL is even worse than QUORUM, because it's a multi-phase QUORUM. As you said the situation can get hard to handle when you have multiple DC -- Datastax estimate "effectively a degradation to one-third of normal".
There is a LOCAL_SERIAL that could be perfect for your situation where all DCs receive data from a specific DC.
Here you can find more info: