Read Consistency
With the SERIALIZABLE scenarios below we are effectively talking about
"Transaction Level Read Consistency" in Oracle terminology. I'll omit the
issue of "Statement Level Read Consistency" for the moment.
Note: MS SqlServer 2005 has a READ_COMMITTED_SNAPSHOT option that if turned on
supports "Statement Level Read Consistency". I am not sure about other databases.
Can you predict the result?
Rather than attempt to explain the how Transaction Isolation levels and Transaction Demarcation
effect Lazy Loading I have gone for a little test. Below is some code which can be run with
4 different scenarios and I want you to try and predict the results.
The four scenarios are:
- READ_COMMITED Isolation Level and No setInclude()
-
- SERIALIZABLE Isolation Level and No setInclude()
-
- READ_COMMITED Isolation Level and setInclude("userLogged; status");
-
- SERIALIZABLE Isolation Level andsetInclude("userLogged; status");
-
Overview of the Code
The query will fetch bug1 and potentially lazy load the userLogged (getting the name) and the status code (getting the title).
At various times (time0 through to time4) the user name and status code title are updated
by other separate transactions simulating another user changing the data.
Your task is to determine what the value of the userLogged name and status code title
is going to be for each of the four scenarios. For example: Is the result of Scenario 1 this?
loggedBy: [Name at time 1] statusTitle: [Title @time 1]
Notes
If setInclude("userLogged; status"); is used on the FindById then the query will include
joins to the user table and the bug status code table. Both userLogged and the status object
are fully populated (Not a reference) and no lazy loading actually occurs.
When setInclude() is not used String loggedBy = bug.getUserLogged().getName(); will
lazy load the userLogged object. This will result in a query that occurs BEFORE the commit.
When setInclude() is not used String statusTitle = bug.getStatus().getTitle(); will
lazy load the status object. This will result in a query that occurs AFTER the commit.
The Code - set for scenario one
The code is set up to run in the first scenario at READ_COMMITTED Isolation level without
the userLogged or status included by setInclude(). That is, my configured default Isolation level
is set to READ_COMMITTED.
This code will run fine on Oracle but on my MySql the SERIALIZABLE scenarios will not work with
a "Lock wait timeout exceeded;" exception. MySql implicitly changes the
SELECT's with LOCK IN SHARE MODE. I have not yet figured out if I can run a
similar test in MySql for the SERIALIZABLE scenarios.
package unitext.ex;
import app.data.Bug;
import com.avaje.ebean.Ebean;
import com.avaje.ebean.EbeanServer;
import com.avaje.ebean.FindById;
import com.avaje.ebean.Transaction;
import com.avaje.ebean.UpdateSql;
public class ExOne {
public static void main(String[] args) {
FindById find = new FindById(Bug.class, bugId);
//find.setInclude("userLogged; status");
setUserAndStatus(5); // time0
Ebean.beginTransaction(); // READ_COMMITED
//Ebean.beginTransaction(Transaction.SERIALIZABLE);
try {
setUserAndStatus(1); // time1
Bug bug = (Bug)Ebean.find(find);
setUserAndStatus(2); // time2
String loggedBy = bug.getUserLogged().getName();
setUserAndStatus(3); // time3
Ebean.commitTransaction();
setUserAndStatus(4); // time4
String statusTitle = bug.getStatus().getTitle();
System.out.println("-------------------------");
System.out.println("loggedBy: ["+loggedBy+"] ");
System.out.println("statusTitle: ["+statusTitle+"] ");
} finally {
Ebean.endTransaction();
}
}
/**
* Hardcoded: Looking at bug1.
*/
private static Integer bugId = new Integer(1);
/**
* Hardcoded: I know the userLoggedId for bug1 is 1.
*/
private static Integer userId = new Integer(1);
/**
* Hardcoded: I know the status code for bug1 is TEST.
*/
private static String statusCode = "TEST";
private static String dmlStatus
= "update b_bug_status set title = :title where code = :code";
private static String dmlUser
= "update s_user set name = :userName where id = :userId";
private static UpdateSql updateUserName = new UpdateSql(dmlUser);
private static UpdateSql updateStatusCode = new UpdateSql(dmlStatus);
/**
* Simulate another user with a separate transaction updating the
* user name and status title.
*/
private static void setUserAndStatus(Integer time) {
String userName = "Name at time "+time;
updateUserName.setParameter("userName", userName);
updateUserName.setParameter("userId", userId);
String statusTitle = "Title @time "+time;
updateStatusCode.setParameter("title", statusTitle);
updateStatusCode.setParameter("code", statusCode);
EbeanServer server = Ebean.getServer(null);
// create a Transaction outside of the ThreadLocal
// rather than actually have a second Thread
Transaction externalTransaction = server.createTransaction();
try {
int rc1 = server.execute(updateUserName, externalTransaction);
int rc2 = server.execute(updateStatusCode, externalTransaction);
if (rc1 != 1 || rc2 != 1){
String error = "Expecting to update 1 row? "+rc1+" "+rc2;
throw new RuntimeException(error);
}
externalTransaction.commit();
} finally {
externalTransaction.end();
}
String m = "Update set UserName["+userName+"] "
+" statusTitle["+statusTitle+"] at time ["+time+"]";
System.err.println(m);
}
}
I realise the code above is a bit crap and a very artifical test but I am hoping that
it is reasonably transparent. The logs in the results may help if it is not clear what
is going on.
See the results for each scenario
|