001    package hirondelle.web4j.database;
002    
003    import java.util.logging.*;
004    import java.sql.Connection;
005    import java.sql.SQLException;
006    
007    import hirondelle.web4j.util.Util;
008    
009    /**
010     Type-safe enumeration for transaction isolation levels.
011     
012     <P>For more information on transaction isolation levels, see {@link Connection} and the
013     <a href="http://en.wikipedia.org/wiki/Isolation_%28computer_science%29">wikipedia<a/> 
014     article.
015    
016     <P>See {@link TxTemplate}, which is closely related to this class. 
017    
018     <a name="PermittedValues"></a><h3>Permitted Values</h3>
019     <P>In order of decreasing strictness (and increasing performance), the levels are :
020    <ul>
021     <li><tt>SERIALIZABLE</tt> (most strict, slowest)
022     <li><tt>REPEATABLE_READ</tt>
023     <li><tt>READ_COMMITTED</tt>
024     <li><tt>READ_UNCOMMITTED</tt> (least strict, fastest)
025    </ul>
026    
027    <P>In addition, this class includes another item called <tt>DATABASE_DEFAULT</tt>. It 
028     indicates to the WEB4J data layer that, unless instructed otherwise,
029     the default isolation level defined by the database instance is to be used. 
030    
031     <h3>Differences In The Top 3 Levels</h3>
032     It is important to understand that the top 3 levels 
033     listed above differ only in one principal respect : behavior for any 
034     <em>re</em>-SELECTs performed in a transaction.  <span class="highlight">If no re-SELECT is 
035     performed in a given transaction, then the there is no difference in the 
036     behavior of the top three levels</span> (except for performance).
037    
038    <P>If a SELECT is repeated in a given transaction, it may see a different 
039     <tt>ResultSet</tt>, since some second transaction may have committed changes 
040     to the underlying data. Three questions can be asked of the second <tt>ResultSet</tt>, 
041     and each isolation level responds to these three questions in a different way :
042    <P><table border=1 cellspacing="0" cellpadding="3"  width="75%">
043     <tr valign="top">
044      <th>Level</th>
045      <th>1. Can a new record appear?</th>
046      <th>2. Can an old record disappear?</th>
047      <th>3. Can an old record change?</th>
048     </tr>
049     <tr><td><tt>SERIALIZABLE</tt></td><td>Never</td><td>Never</td><td>Never</td></tr>
050     <tr><td><tt>REPEATABLE_READ</tt></td><td>Possibly</td><td>Never</td><td>Never</td></tr>
051     <tr><td><tt>READ_COMMITTED</tt></td><td>Possibly</td><td>Possibly</td><td>Possibly</td></tr>
052    </table>
053     <P>(Note : 1 is called a <em>phantom read</em>, while both 2 and 3 are called a 
054     <em>non-repeatable read</em>.)
055     
056     <h3>Configuration In <tt>web.xml</tt></h3>
057     <em>When no external <tt>Connection</tt> is passed by the application</em>, then  
058     the WEB4J data layer will use an internal <tt>Connection</tt>
059     set to the isolation level configured in <tt>web.xml</tt>. 
060    
061    <h3>General Guidelines</h3>
062    <ul>
063     <li>consult both your database administrator and your database documentation 
064     for guidance regarding these levels
065     <li><span class="highlight">since support for these levels is highly variable, 
066     setting the transaction isolation level explicitly has low portability</span> 
067     (see {@link #set} for some help in this regard). The <tt>DATABASE_DEFAULT</tt> 
068     setting is an attempt to hide these variations in support
069     <li>for a WEB4J application, it is likely a good choice to use the 
070     <tt>DATABASE_DEFAULT</tt>, and to alter that level only under special circumstances 
071     <li>selecting a specific level is always a trade-off between level of data 
072     integrity and execution speed
073    </ul>
074    
075    <h3>Support For Some Popular Databases</h3>
076     (Taken from <em>
077     <a href="http://www.amazon.com/exec/obidos/ASIN/0596004818/ref=nosim/javapractices-20">SQL
078     in a Nutshell</a></em>, by Kline, 2004. <span class="highlight">Please confirm with 
079     your database documentation</span>).<P>
080    <table border=1 cellspacing="0" cellpadding="3"  width="60%">
081    <tr valign="top">
082     <td>&nbsp;</td>
083     <td>DB2</td>
084     <td>MySQL</td>
085     <td>Oracle</td>
086     <td>PostgreSQL</td>
087     <td>SQL Server</td>
088    </tr>
089    <tr>
090     <td><tt>SERIALIZABLE</tt></td>
091     <td>Y</td>
092     <td>Y</td>
093     <td>Y</td>
094     <td>Y</td>
095     <td>Y</td>
096    </tr>
097    <tr>
098     <td><tt>REPEATABLE_READ</tt></td>
099     <td>Y</td>
100     <td>Y*</td>
101     <td>N</td>
102     <td>N</td>
103     <td>Y</td>
104    </tr>
105    <tr>
106     <td><tt>READ_COMMITTED</tt></td>
107     <td>Y</td>
108     <td>Y</td>
109     <td>Y*</td>
110     <td>Y*</td>
111     <td>Y*</td>
112    </tr>
113    <tr>
114     <td><tt>READ_UNCOMMITTED</tt></td>
115     <td>Y</td>
116     <td>Y</td>
117     <td>N</td>
118     <td>N</td>
119     <td>Y</td>
120    </tr>
121    </table>
122     &#8727; Database Default<br>
123    */
124    public enum TxIsolationLevel {
125      
126      SERIALIZABLE("SERIALIZABLE", Connection.TRANSACTION_SERIALIZABLE),
127      REPEATABLE_READ("REPEATABLE_READ", Connection.TRANSACTION_REPEATABLE_READ),
128      READ_COMMITTED("READ_COMMITTED", Connection.TRANSACTION_READ_COMMITTED),
129      READ_UNCOMMITTED("READ_UNCOMMITTED", Connection.TRANSACTION_READ_UNCOMMITTED),
130      DATABASE_DEFAULT ("DATABASE_DEFAULT", -1);
131      
132      /**
133       Return the same underlying <tt>int</tt> value used by {@link Connection} to identify the isolation level.
134      
135       <P>For {@link #DATABASE_DEFAULT}, return <tt>-1</tt>.
136      */
137      public int getInt(){
138        return fIntValue;
139      }
140      
141      /**  Return one of the <a href="#PermittedValues">permitted values</a>, including <tt>'DATABASE_DEFAULT'</tt>.  */
142      public String toString(){
143        return fText;
144      }
145      
146      /**
147       Set a particular isolation level for <tt>aConnection</tt>.
148      
149       <P>This method exists because database support for isolation levels varies 
150       widely.<span class="highlight"> If any error occurs because <tt>aLevel</tt> is not supported, then 
151       the error will be logged at a <tt>SEVERE</tt> level, but 
152       the application will continue to run</span>. This policy treats isolation levels 
153       as important, but non-critical. Porting an application to a database which 
154       does not support all levels will not cause an application to fail. The transaction 
155       will simply execute at the database's default isolation level.
156      
157       <P>Passing in the special value {@link #DATABASE_DEFAULT} will cause a no-operation.
158      */
159      public static void set(TxIsolationLevel aLevel, Connection aConnection){
160        if( aLevel != DATABASE_DEFAULT ) {
161          try {
162              aConnection.setTransactionIsolation(aLevel.getInt());
163          }
164          catch (SQLException ex) {
165            fLogger.severe(
166              "Cannot set transaction isolation level. Database does " + 
167              "not apparently support '" + aLevel + 
168              "'. You will likely need to choose a different isolation level. " + 
169              "Please see your database documentation, and the javadoc for TxIsolationLevel."
170            );
171          }
172        }
173      }
174      
175      // PRIVATE //
176      private TxIsolationLevel(String aText, int aIntValue){
177        fText = aText;
178        fIntValue = aIntValue;
179      }
180      private String fText;
181      private int fIntValue;
182      private static final Logger fLogger = Util.getLogger(TxIsolationLevel.class);  
183    }