Good afternoon.
Common entty bean contain a number of set and get methods. Here we have a Customer bean with field named credits.
Also we have a Session bean named Cashier with deposit method. In general this method looks like:
public void deposit(Integer customerId, int value) throws RemoteException {
try {
CustomerLocal customer = this.customerHome.findByPrimaryKey(customerId);
int credits = customer.getCredits()+value;
if(credits < 0) {
throw new OutOfCreditsBuisenessException("....");
}
customer.setCredits(credits);
} catch .... {
///.... bla bla. catch some common exceptions
}
}
Invocation of this method results in two queries
1) SELECT credits FROM customer WHERE id=?
2) UPDATE customer SET credits=? WHERE id=?
That is good enoght for those who are using SERIALIZABLE tx isolation level. We can not afford it with mysql here, the level leads to locks on customer table (i.e. customer spends credits and deposits it a same time) and we have to use REPETABLE_READ instead.
Is that possible somehow to merge two queries to one like:
UPDATE customer SET credits=credits+? WHERE id=?
I know that it is possible by using direct connection with db. But this will mean new transaction per each deposit. Is that avoidable?
Could it be that SERIALIZABLE tx isolation level is a weak point of mysql and we should consider migration to another db?
Thanks for attention.
P.s. Don't know if this forum best place for such a question. Hits where shold-I-place-the-question are welcome too :).
