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 }