Lazy Loading

Lazy Loading will automatically create a separate Transaction to perform the query.

I realise that this approach is different from that of Hibernate, and likely to be different from JPA vendors.

In this regard it is worth describing how Transaction Isolation and Transaction Demarcation effect lazy loading and the issue of "Read Consistency".

Introduction to Lazy Loading

Go here for some Lazy Loading code examples.

 

 

Lazy Loading and Transaction Isolation

ANSI Isolation Levels - DBMS Vendors don't 100% agree

Before we get into this we need to note there is some debate over relating ANSI isolation levels with databases that support multi-level concurrency control (MCC).

Quoting from wikipedia: http://en.wikipedia.org/wiki/Snapshot_isolation

"Unfortunately, the ANSI SQL-92 standard was written with a lock-based database in mind, and hence is rather vague when applied to MCC systems. Berenson et al. wrote a paper in 1995 [3] critiquing the SQL standard, and cited snapshot isolation as an example of an isolation level that did not exhibit the standard anomalies described in the ANSI SQL-92 standard, yet still had anomalous behaviour when compared with serializable transactions."

Basically ANSI Isolation levels are not the be all and end all for some DBMS Vendors. ANSI SQL 1999 doesn't mention Lost Updates, Read Consistency or Snapshot Isolation for example.

Summary

At READ_COMMITTED Isolation level you will lazy load the "Most Recent" data rather than "Read Consistent" data wrt the original query.

Transaction demarcation has no effect on the lazy loading queries at READ_COMMITTED Isolation level.

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

Introduction User Guide (pdf) Install/Configure Public JavaDoc Whitepapers
General Database Specific Byte Code Deployment Annotations Features
Top Bugs Top Enhancements
woResponse