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> </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 ∗ 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 }