Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > jdbcapi > ConcurrencyTest


1 /*
2  *
3  * Derby - Class ConcurrencyTest
4  *
5  * Licensed to the Apache Software Foundation (ASF) under one or more
6  * contributor license agreements. See the NOTICE file distributed with
7  * this work for additional information regarding copyright ownership.
8  * The ASF licenses this file to You under the Apache License, Version 2.0
9  * (the "License"); you may not use this file except in compliance with
10  * the License. You may obtain a copy of the License at
11  *
12  * http://www.apache.org/licenses/LICENSE-2.0
13  *
14  * Unless required by applicable law or agreed to in writing,
15  * software distributed under the License is distributed on an
16  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
17  * either express or implied. See the License for the specific
18  * language governing permissions and limitations under the License.
19  */
20 package org.apache.derbyTesting.functionTests.tests.jdbcapi;
21 import java.sql.Connection ;
22 import java.sql.PreparedStatement ;
23 import java.sql.ResultSet ;
24 import java.sql.SQLException ;
25 import java.sql.Statement ;
26 import java.util.Properties ;
27
28 import junit.framework.Test;
29 import junit.framework.TestSuite;
30
31 import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
32 import org.apache.derbyTesting.junit.JDBC;
33
34 /**
35  * Testing concurrency behaviour in derby when creating the resultsets with
36  * different parameters.
37  * @author Andreas Korneliussen
38  */
39 public class ConcurrencyTest extends SURBaseTest {
40     
41     /** Creates a new instance of ConcurrencyTest */
42     public ConcurrencyTest(String name) {
43         super(name);
44     }
45
46     /**
47      * Sets up the connection, then create the data model
48      */
49     public void setUp()
50         throws Exception
51     {
52         // For the concurrency tests, we recreate the model
53 // for each testcase (since we do commits)
54 SURDataModelSetup.createDataModel
55             (SURDataModelSetup.SURDataModel.MODEL_WITH_PK, getConnection());
56         commit();
57     }
58     
59     public void tearDown() throws Exception
60     {
61         try {
62             rollback();
63             Statement dropStatement = createStatement();
64             dropStatement.execute("drop table t1");
65             dropStatement.close();
66         } catch (SQLException e) {
67             printStackTrace(e); // Want to propagate the real exception.
68 }
69         super.tearDown();
70     }
71     
72     /**
73      * Test that update locks are downgraded to shared locks
74      * after repositioning.
75      * This test fails with Derby
76      */
77     public void testUpdateLockDownGrade1()
78         throws SQLException
79     {
80         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
81                                           ResultSet.CONCUR_UPDATABLE);
82         ResultSet rs = s.executeQuery("select * from t1 for update");
83         
84         // After navigating through the resultset,
85 // presumably all rows are locked with shared locks
86 while (rs.next());
87         
88         // Now open up a connection
89 Connection con2 = openDefaultConnection();
90         Statement s2 = con2.createStatement(ResultSet.TYPE_FORWARD_ONLY,
91                                             ResultSet.CONCUR_UPDATABLE);
92         
93         ResultSet rs2 = s2.executeQuery("select * from t1 for update");
94         try {
95             rs2.next(); // We should be able to get a update lock here.
96 } catch (SQLException e) {
97             assertEquals("Unexpected SQL state", LOCK_TIMEOUT_SQL_STATE,
98                          e.getSQLState());
99             return;
100         } finally {
101             con2.rollback();
102         }
103         assertTrue("Expected Derby to hold updatelocks in RR mode", false);
104         
105         s2.close();
106         con2.close();
107         
108         s.close();
109     }
110     
111     /**
112      * Test that we can aquire a update lock even if the row is locked with
113      * a shared lock.
114      */
115     public void testAquireUpdateLock1()
116         throws SQLException
117     {
118         Statement s = createStatement();
119         ResultSet rs = s.executeQuery("select * from t1");
120         
121         // After navigating through the resultset,
122 // presumably all rows are locked with shared locks
123 while (rs.next());
124         
125         // Now open up a connection
126 Connection con2 = openDefaultConnection();
127         Statement s2 = con2.createStatement(ResultSet.TYPE_FORWARD_ONLY,
128                                             ResultSet.CONCUR_UPDATABLE);
129         
130         ResultSet rs2 = s2.executeQuery("select * from t1 for update");
131         try {
132             rs2.next(); // We should be able to get a update lock here.
133 } finally {
134             con2.rollback();
135         }
136         
137         s2.close();
138         con2.close();
139         s.close();
140     }
141     
142     /*
143      * Test that we do not get a concurrency problem when opening two cursors
144      * as readonly.
145      **/
146     public void testSharedLocks1()
147         throws SQLException
148     {
149         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
150                                           ResultSet.CONCUR_READ_ONLY);
151         final ResultSet rs = s.executeQuery("select * from t1");
152         scrollForward(rs);
153         Connection con2 = openDefaultConnection();
154         Statement s2 = con2.createStatement(ResultSet.TYPE_FORWARD_ONLY,
155                                             ResultSet.CONCUR_READ_ONLY);
156         try {
157             final ResultSet rs2 = s2.executeQuery("select * from t1");
158             scrollForward(rs2);
159         } finally {
160             rs.close();
161             con2.rollback();
162             con2.close();
163         }
164         
165         s.close();
166     }
167     
168     /*
169      * Test that we do not get a concurrency problem when opening two cursors
170      * reading the same data (no parameters specified to create statement).
171      **/
172     public void testSharedLocks2()
173         throws SQLException
174     {
175         Statement s = createStatement();
176         ResultSet rs = s.executeQuery("select * from t1");
177         scrollForward(rs);
178         Connection con2 = openDefaultConnection();
179         Statement s2 = con2.createStatement();
180         try {
181             final ResultSet rs2 = s2.executeQuery("select * from t1");
182             scrollForward(rs2);
183         } finally {
184             rs.close();
185             con2.rollback();
186             con2.close();
187         }
188         s.close();
189     }
190     
191     /*
192      * Test that we do not get a concurrency problem when opening one cursor
193      * as updatable (not using "for update"), and another cursor as read only
194      **/
195     public void testSharedAndUpdateLocks1()
196         throws SQLException {
197         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
198                                           ResultSet.CONCUR_UPDATABLE);
199         
200         ResultSet rs = s.executeQuery("select * from t1");
201         scrollForward(rs);
202         Connection con2 = openDefaultConnection();
203         Statement s2 = con2.createStatement(ResultSet.TYPE_FORWARD_ONLY,
204                                             ResultSet.CONCUR_READ_ONLY);
205         try {
206             final ResultSet rs2 = s2.executeQuery("select * from t1");
207             scrollForward(rs2);
208         } finally {
209             rs.close();
210             con2.rollback();
211             con2.close();
212         }
213         s.close();
214     }
215     
216     /*
217      * Test that we do no get a concurrency problem when opening one cursor
218      * as updatable (using "for update"), and another cursor as read only.
219      *
220      **/
221     public void testSharedAndUpdateLocks2()
222         throws SQLException
223     {
224         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
225                                           ResultSet.CONCUR_UPDATABLE);
226         ResultSet rs = s.executeQuery("select * from t1 for update");
227         scrollForward(rs);
228         Connection con2 = openDefaultConnection();
229         Statement s2 = con2.createStatement(ResultSet.TYPE_FORWARD_ONLY,
230                                             ResultSet.CONCUR_READ_ONLY);
231         try {
232             final ResultSet rs2 = s2.executeQuery("select * from t1");
233             scrollForward(rs2);
234         } finally {
235             rs.close();
236             con2.rollback();
237             con2.close();
238         }
239         s.close();
240     }
241     
242     /**
243      * Test what happens if you update a deleted + purged tuple.
244      * The transaction which deletes the tuple, will also
245      * ensure that the tuple is purged from the table, not only marked
246      * as deleted.
247      **/
248     public void testUpdatePurgedTuple1()
249         throws SQLException
250     {
251         getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
252         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
253                                           ResultSet.CONCUR_UPDATABLE);
254         ResultSet rs = s.executeQuery("select * from t1");
255         rs.next();
256         int firstKey = rs.getInt(1);
257         println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
258                 rs.getInt(2) + "," +
259                 rs.getInt(3) + ")");
260         int lastKey = firstKey;
261         while (rs.next()) {
262             lastKey = rs.getInt(1);
263             println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
264                     rs.getInt(2) + "," +
265                     rs.getInt(3) + ")");
266         }
267         
268         Connection con2 = openDefaultConnection();
269         con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
270         try {
271             PreparedStatement ps2 = con2.prepareStatement
272                 ("delete from t1 where id=? or id=?");
273             ps2.setInt(1, firstKey);
274             ps2.setInt(2, lastKey);
275             assertEquals("Expected two records to be deleted",
276                          2, ps2.executeUpdate());
277             println("T2: Deleted records with id=" + firstKey + " and id=" +
278                     lastKey);
279             con2.commit();
280             println("T2: commit");
281             ps2 = con2.prepareStatement
282                 ("call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?,?,?,?,?)");
283             ps2.setString(1, "APP"); // schema
284 ps2.setString(2, "T1"); // table name
285 ps2.setInt(3, 1); // purge
286 ps2.setInt(4, 0); // defragment rows
287 ps2.setInt(5, 0); // truncate end
288 println("T3: call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE");
289             println("T3: purges deleted records");
290             ps2.executeUpdate();
291             con2.commit();
292             println("T3: commit");
293         } catch (SQLException e) {
294             con2.rollback();
295             throw e;
296         }
297         rs.first(); // Go to first tuple
298 println("T1: Read first Tuple:(" + rs.getInt(1) + "," +
299                 rs.getInt(2) + "," +
300                 rs.getInt(3) + ")");
301         rs.updateInt(2, 3);
302         println("T1: updateInt(2, 3);");
303         rs.updateRow();
304         println("T1: updateRow()");
305         rs.last(); // Go to last tuple
306 println("T1: Read last Tuple:(" + rs.getInt(1) + "," +
307                 rs.getInt(2) + "," +
308                 rs.getInt(3) + ")");
309         rs.updateInt(2, 3);
310         println("T1: updateInt(2, 3);");
311         rs.updateRow();
312         println("T1: updateRow()");
313         commit();
314         println("T1: commit");
315         rs = s.executeQuery("select * from t1");
316         println("T3: select * from table");
317         while (rs.next()) {
318             println("T3: Read next Tuple:(" + rs.getInt(1) + "," +
319                     rs.getInt(2) + "," +
320                     rs.getInt(3) + ")");
321             
322         }
323         
324         con2.close();
325         s.close();
326     }
327     
328     /**
329      * Test what happens if you update a deleted tuple using positioned update
330      * (same as testUpdatePurgedTuple1, except here we use positioned updates)
331      **/
332     public void testUpdatePurgedTuple2()
333         throws SQLException
334     {
335         getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
336         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
337                                           ResultSet.CONCUR_UPDATABLE);
338         ResultSet rs = s.executeQuery("select * from t1");
339         rs.next(); // Point to first tuple
340 println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
341                 rs.getInt(2) + "," +
342                 rs.getInt(3) + ")");
343         int firstKey = rs.getInt(1);
344         rs.next(); // Go to next
345 println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
346                 rs.getInt(2) + "," +
347                 rs.getInt(3) + ")");
348         Connection con2 = openDefaultConnection();
349         con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
350         try {
351             PreparedStatement ps2 = con2.prepareStatement
352                 ("delete from t1 where id=?");
353             ps2.setInt(1, firstKey);
354             assertEquals("Expected one record to be deleted", 1,
355                          ps2.executeUpdate());
356             println("T2: Deleted record with id=" + firstKey);
357             con2.commit();
358             println("T2: commit");
359         } catch (SQLException e) {
360             con2.rollback();
361             throw e;
362         }
363         rs.previous(); // Go back to first tuple
364 println("T1: Read previous Tuple:(" + rs.getInt(1) + "," +
365                 rs.getInt(2) + "," +
366                 rs.getInt(3) + ")");
367         
368         PreparedStatement ps = prepareStatement
369             ("update T1 set a=? where current of " + rs.getCursorName());
370         ps.setInt(1, 3);
371         int updateCount = ps.executeUpdate();
372         println("T1: update table, set a=3 where current of " +
373                 rs.getCursorName());
374         println("T1: commit");
375         commit();
376         rs = s.executeQuery("select * from t1");
377         while (rs.next()) {
378             println("T3: Tuple:(" + rs.getInt(1) + "," +
379                     rs.getInt(2) + "," +
380                     rs.getInt(3) + ")");
381             
382         }
383         
384         con2.close();
385     }
386     
387     /**
388      * Test what happens if you update a tuple which is deleted, purged and
389      * reinserted
390      **/
391     public void testUpdatePurgedTuple3()
392         throws SQLException
393     {
394         getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
395         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
396                                           ResultSet.CONCUR_UPDATABLE);
397         ResultSet rs = s.executeQuery("select * from t1");
398         rs.next(); // Point to first tuple
399 int firstKey = rs.getInt(1);
400         println("T1: read tuple with key " + firstKey);
401         rs.next(); // Go to next
402 println("T1: read next tuple");
403         Connection con2 = openDefaultConnection();
404         con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
405         try {
406             PreparedStatement ps2 = con2.prepareStatement
407                 ("delete from t1 where id=?");
408             ps2.setInt(1, firstKey);
409             assertEquals("Expected one record to be deleted", 1,
410                          ps2.executeUpdate());
411             println("T2: Deleted record with id=" + firstKey);
412             con2.commit();
413             println("T2: commit");
414             
415             // Now purge the table
416 ps2 = con2.prepareStatement
417                 ("call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?,?,?,?,?)");
418             ps2.setString(1, "APP"); // schema
419 ps2.setString(2, "T1"); // table name
420 ps2.setInt(3, 1); // purge
421 ps2.setInt(4, 0); // defragment rows
422 ps2.setInt(5, 0); // truncate end
423 println("T3: call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE");
424             println("T3: purges deleted records");
425             ps2.executeUpdate();
426             con2.commit();
427             
428             ps2 = con2.prepareStatement("insert into t1 values(?,?,?,?)");
429             ps2.setInt(1, firstKey);
430             ps2.setInt(2, -1);
431             ps2.setInt(3, -1);
432             ps2.setString(4, "UPDATED TUPLE");
433             assertEquals("Expected one record to be inserted", 1,
434                          ps2.executeUpdate());
435             println("T4: Inserted record (" + firstKey + ",-1,-1)" );
436             con2.commit();
437             println("T4: commit");
438         } catch (SQLException e) {
439             con2.rollback();
440             throw e;
441         }
442         println("T1: read previous tuple");
443         rs.previous(); // Go back to first tuple
444 println("T1: id=" + rs.getInt(1));
445         rs.updateInt(2, 3);
446         println("T1: updateInt(2, 3);");
447         rs.updateRow();
448         println("T1: updated column 2, to value=3");
449         println("T1: commit");
450         commit();
451         rs = s.executeQuery("select * from t1");
452         while (rs.next()) {
453             println("T5: Read Tuple:(" + rs.getInt(1) + "," +
454                     rs.getInt(2) + "," +
455                     rs.getInt(3) + ")");
456             
457         }
458         
459         con2.close();
460     }
461     
462     /**
463      * Test what happens if you update a tuple which is deleted, purged and
464      * then reinserted with the exact same values
465      **/
466     public void testUpdatePurgedTuple4()
467         throws SQLException
468     {
469         getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
470         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
471                                           ResultSet.CONCUR_UPDATABLE);
472         ResultSet rs = s.executeQuery("select * from t1");
473         rs.next(); // Point to first tuple
474 int firstKey = rs.getInt(1);
475         int valA = rs.getInt(2);
476         int valB = rs.getInt(3);
477         
478         println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
479                 rs.getInt(2) + "," +
480                 rs.getInt(3) + ")");
481         
482         rs.next(); // Go to next
483 println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
484                 rs.getInt(2) + "," +
485                 rs.getInt(3) + ")");
486         Connection con2 = openDefaultConnection();
487         con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
488         try {
489             PreparedStatement ps2 = con2.prepareStatement
490                 ("delete from t1 where id=?");
491             ps2.setInt(1, firstKey);
492             assertEquals("Expected one record to be deleted", 1,
493                          ps2.executeUpdate());
494             println("T2: Deleted record with id=" + firstKey);
495             con2.commit();
496             println("T2: commit");
497             
498             // Now purge the table
499 ps2 = con2.prepareStatement
500                 ("call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?,?,?,?,?)");
501             ps2.setString(1, "APP"); // schema
502 ps2.setString(2, "T1"); // table name
503 ps2.setInt(3, 1); // purge
504 ps2.setInt(4, 0); // defragment rows
505 ps2.setInt(5, 0); // truncate end
506 println("T3: call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE");
507             println("T3: purges deleted records");
508             ps2.executeUpdate();
509             con2.commit();
510             println("T3: commit");
511             
512             ps2 = con2.prepareStatement("insert into t1 values(?,?,?,?)");
513             ps2.setInt(1, firstKey);
514             ps2.setInt(2, valA);
515             ps2.setInt(3, valB);
516             ps2.setString(4, "UPDATE TUPLE " + firstKey);
517             assertEquals("Expected one record to be inserted", 1,
518                          ps2.executeUpdate());
519             println("T4: Inserted record (" + firstKey + "," + valA + "," +
520                     valB + ")" );
521             con2.commit();
522             println("T4: commit");
523         } catch (SQLException e) {
524             con2.rollback();
525             throw e;
526         }
527         rs.previous(); // Go back to first tuple
528 println("T1: Read previous Tuple:(" + rs.getInt(1) + "," +
529                 rs.getInt(2) + "," +
530                 rs.getInt(3) + ")");
531         
532         println("T1: id=" + rs.getInt(1));
533         rs.updateInt(2, 3);
534         rs.updateRow();
535         println("T1: updated column 2, to value=3");
536         println("T1: commit");
537         commit();
538         rs = s.executeQuery("select * from t1");
539         while (rs.next()) {
540             println("T4: Read next Tuple:(" + rs.getInt(1) + "," +
541                     rs.getInt(2) + "," +
542                     rs.getInt(3) + ")");
543             
544         }
545     }
546     
547     /**
548      * Test what happens if you update a tuple which has been modified by
549      * another transaction.
550      **/
551     public void testUpdateModifiedTuple1()
552         throws SQLException
553     {
554         getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
555         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
556                                           ResultSet.CONCUR_UPDATABLE);
557         ResultSet rs = s.executeQuery("select * from t1");
558         rs.next(); // Point to first tuple
559 println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
560                 rs.getInt(2) + "," +
561                 rs.getInt(3) + ")");
562         int firstKey = rs.getInt(1);
563         rs.next(); // Go to next
564 println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
565                 rs.getInt(2) + "," +
566                 rs.getInt(3) + ")");
567         Connection con2 = openDefaultConnection();
568         con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
569         try {
570             PreparedStatement ps2 = con2.prepareStatement
571                 ("update t1 set b=? where id=?");
572             ps2.setInt(1, 999);
573             ps2.setInt(2, firstKey);
574             assertEquals("Expected one record to be updated", 1,
575                          ps2.executeUpdate());
576             println("T2: Updated b=999 where id=" + firstKey);
577             con2.commit();
578             println("T2: commit");
579         } catch (SQLException e) {
580             con2.rollback();
581             throw e;
582         }
583         rs.previous(); // Go back to first tuple
584 println("T1: Read previous Tuple:(" + rs.getInt(1) + "," +
585                 rs.getInt(2) + "," +
586                 rs.getInt(3) + ")");
587         rs.updateInt(2, 3);
588         rs.updateRow();
589         println("T1: updated column 2, to value=3");
590         commit();
591         println("T1: commit");
592         rs = s.executeQuery("select * from t1");
593         while (rs.next()) {
594             println("T3: Read next Tuple:(" + rs.getInt(1) + "," +
595                     rs.getInt(2) + "," +
596                     rs.getInt(3) + ")");
597             
598         }
599     }
600     
601     /**
602      * Test what happens if you update a tuple which has been modified by
603      * another transaction (in this case the same column)
604      **/
605     public void testUpdateModifiedTuple2()
606         throws SQLException
607     {
608         getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
609         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
610                                           ResultSet.CONCUR_UPDATABLE);
611         ResultSet rs = s.executeQuery("select * from t1");
612         rs.next(); // Point to first tuple
613 println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
614                 rs.getInt(2) + "," +
615                 rs.getInt(3) + ")");
616         int firstKey = rs.getInt(1);
617         rs.next(); // Go to next
618 println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
619                 rs.getInt(2) + "," +
620                 rs.getInt(3) + ")");
621         Connection con2 = openDefaultConnection();
622         con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
623         try {
624             PreparedStatement ps2 = con2.prepareStatement
625                 ("update t1 set b=? where id=?");
626             ps2.setInt(1, 999);
627             ps2.setInt(2, firstKey);
628             assertEquals("Expected one record to be updated", 1,
629                          ps2.executeUpdate());
630             println("T2: Updated b=999 where id=" + firstKey);
631             con2.commit();
632             println("T2: commit");
633         } catch (SQLException e) {
634             con2.rollback();
635             throw e;
636         }
637         rs.previous(); // Go back to first tuple
638 println("T1: Read previous Tuple:(" + rs.getInt(1) + "," +
639                 rs.getInt(2) + "," +
640                 rs.getInt(3) + ")");
641         rs.updateInt(3, 9999);
642         rs.updateRow();
643         println("T1: updated column 3, to value=9999");
644         commit();
645         println("T1: commit");
646         rs = s.executeQuery("select * from t1");
647         while (rs.next()) {
648             println("T3: Read next Tuple:(" + rs.getInt(1) + "," +
649                     rs.getInt(2) + "," +
650                     rs.getInt(3) + ")");
651             
652         }
653     }
654     
655     /**
656      * Tests that a ResultSet opened even in read uncommitted, gets a
657      * table intent lock, and that another transaction then cannot compress
658      * the table while the ResultSet is open.
659      **/
660     public void testTableIntentLock1()
661         throws SQLException
662     {
663         getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
664         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
665                                           ResultSet.CONCUR_UPDATABLE);
666         println("T1: select * from t1");
667         ResultSet rs = s.executeQuery("select * from t1 for update");
668         while (rs.next()) {
669             println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
670                     rs.getInt(2) + "," +
671                     rs.getInt(3) + ")");
672         } // Now the cursor does not point to any tuples
673
674         // Compressing the table in another transaction:
675 Connection con2 = openDefaultConnection();
676         try {
677             con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
678             PreparedStatement ps2 = con2.prepareStatement
679                 ("call SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?)");
680             ps2.setString(1, "APP");
681             ps2.setString(2, "T1");
682             ps2.setInt(3, 0);
683             println("T2: call SYSCS_UTIL.SYSCS_COMPRESS_TABLE(APP, T1, 0)");
684             ps2.executeUpdate(); // This will hang
685 assertTrue("Expected T2 to hang", false);
686         } catch (SQLException e) {
687             println("T2: Got exception:" + e.getMessage());
688             
689             assertEquals("Unexpected SQL state",
690                          LOCK_TIMEOUT_EXPRESSION_SQL_STATE,
691                          e.getSQLState());
692         } finally {
693             con2.rollback();
694         }
695     }
696     
697     /**
698      * Test that Derby set updatelock on current row when using
699      * read-uncommitted
700      **/
701     public void testUpdateLockInReadUncommitted()
702         throws SQLException
703     {
704         getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
705         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
706                                           ResultSet.CONCUR_UPDATABLE);
707         ResultSet rs = s.executeQuery("select * from t1");
708         rs.next();
709         int firstKey = rs.getInt(1);
710         println("T1: Read next Tuple:(" + rs.getInt(1) + "," +
711                 rs.getInt(2) + "," +
712                 rs.getInt(3) + ")");
713         Connection con2 = openDefaultConnection();
714         con2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
715         try {
716             PreparedStatement ps2 = con2.prepareStatement
717                 ("delete from t1 where id=?");
718             ps2.setInt(1, firstKey);
719             ps2.executeUpdate();
720             assertTrue("expected record with id=" + firstKey +
721                        " to be locked", false);
722         } catch (SQLException e) {
723             assertEquals("Unexpected SQL state", LOCK_TIMEOUT_SQL_STATE,
724                          e.getSQLState());
725         } finally {
726             con2.rollback();
727         }
728         con2.close();
729         s.close();
730     }
731     
732     /**
733      * Test that the system cannot defragment any records
734      * as long as an updatable result set is open against the table.
735      **/
736     public void testDefragmentDuringScan()
737         throws SQLException
738     {
739         testCompressDuringScan(true, false);
740     }
741     /**
742      * Test that the system cannot truncate any records
743      * as long as an updatable result set is open against the table.
744      **/
745     public void testTruncateDuringScan()
746         throws SQLException
747     {
748         testCompressDuringScan(false, true);
749     }
750     
751     /**
752      * Test that the system does not purge any records
753      * as long as we do either a defragment, or truncate
754      **/
755     private void testCompressDuringScan(boolean testDefragment,
756                                         boolean testTruncate)
757         throws SQLException
758     {
759         getConnection().setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
760         Statement delStatement = createStatement();
761         // First delete all records except the last and first
762 int deleted = delStatement.executeUpdate
763             ("delete from T1 where id>0 and id<" + (recordCount-1));
764         int expectedDeleted = recordCount-2;
765         println("T1: delete records");
766         assertEquals("Invalid number of records deleted", expectedDeleted,
767                      deleted);
768         commit();
769         println("T1: commit");
770         
771         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
772                                           ResultSet.CONCUR_UPDATABLE);
773         ResultSet rs = s.executeQuery("select * from t1");
774         rs.next();
775         int firstKey = rs.getInt(1);
776         println("T2: Read next Tuple:(" + rs.getInt(1) + "," +
777                 rs.getInt(2) + "," +
778                 rs.getInt(3) + ")");
779         int lastKey = firstKey;
780         while (rs.next()) {
781             lastKey = rs.getInt(1);
782             println("T2: Read next Tuple:(" + rs.getInt(1) + "," +
783                     rs.getInt(2) + "," +
784                     rs.getInt(3) + ")");
785         }
786         
787         final Connection con2 = openDefaultConnection();
788         con2.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
789         final PreparedStatement ps2 = con2.prepareStatement
790             ("call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?,?,?,?,?)");
791         ps2.setString(1, "APP"); // schema
792 ps2.setString(2, "T1"); // table name
793 ps2.setInt(3, 0); // purge
794 int defragment = testDefragment ? 1 : 0;
795         int truncate = testTruncate ? 1 : 0;
796         ps2.setInt(4, defragment); // defragment rows
797 ps2.setInt(5, truncate); // truncate end
798
799         println("T3: call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE");
800         println("T3: defragmenting rows");
801         try {
802             ps2.executeUpdate();
803             con2.commit();
804             println("T3: commit");
805             assertTrue("Expected T3 to hang waiting for Table lock", false);
806         } catch (SQLException e) {
807             println("T3: got expected exception");
808             con2.rollback();
809         }
810         rs.first(); // Go to first tuple
811 println("T1: Read first Tuple:(" + rs.getInt(1) + "," +
812                 rs.getInt(2) + "," +
813                 rs.getInt(3) + ")");
814         rs.updateInt(2, 3);
815         println("T1: updateInt(2, 3);");
816         rs.updateRow();
817         println("T1: updateRow()");
818         rs.last(); // Go to last tuple
819 println("T1: Read last Tuple:(" + rs.getInt(1) + "," +
820                 rs.getInt(2) + "," +
821                 rs.getInt(3) + ")");
822         rs.updateInt(2, 3);
823         println("T1: updateInt(2, 3);");
824         rs.updateRow();
825         println("T1: updateRow()");
826         commit();
827         println("T1: commit");
828         rs = s.executeQuery("select * from t1");
829         println("T4: select * from table");
830         while (rs.next()) {
831             println("T4: Read next Tuple:(" + rs.getInt(1) + "," +
832                     rs.getInt(2) + "," +
833                     rs.getInt(3) + ")");
834         }
835     }
836     
837     // By providing a static suite(), you can customize which tests to run.
838 // The default is to run all tests in the TestCase subclass.
839
840     public static Test suite() {
841         final TestSuite suite = new TestSuite();
842         
843         // This testcase does not require JDBC3/JSR169, since it does not
844 // specify result set concurrency) in Connection.createStatement().
845 suite.addTest(new ConcurrencyTest("testSharedLocks2"));
846         
847         // The following testcases requires JDBC3/JSR169:
848 if ((JDBC.vmSupportsJDBC3() || JDBC.vmSupportsJSR169())) {
849             
850             // The following testcases do not use updatable result sets:
851 suite.addTest(new ConcurrencyTest("testUpdateLockDownGrade1"));
852             suite.addTest(new ConcurrencyTest("testAquireUpdateLock1"));
853             suite.addTest(new ConcurrencyTest("testSharedLocks1"));
854             suite.addTest(new ConcurrencyTest("testSharedAndUpdateLocks1"));
855             suite.addTest(new ConcurrencyTest("testSharedAndUpdateLocks2"));
856             
857             // The following testcases do use updatable result sets.
858 if (!usingDerbyNet()) { // DB2 client does not support UR with Derby
859 suite.addTest(new ConcurrencyTest ("testUpdatePurgedTuple2"));
860                 suite.addTest(new ConcurrencyTest("testUpdatePurgedTuple3"));
861                 suite.addTest(new ConcurrencyTest("testUpdatePurgedTuple4"));
862                 suite.addTest(new ConcurrencyTest("testUpdateModifiedTuple1"));
863                 suite.addTest(new ConcurrencyTest("testUpdateModifiedTuple2"));
864                 suite.addTest(new ConcurrencyTest("testTableIntentLock1"));
865                 suite.addTest
866                     (new ConcurrencyTest("testUpdateLockInReadUncommitted"));
867                 suite.addTest(new ConcurrencyTest("testDefragmentDuringScan"));
868                 suite.addTest(new ConcurrencyTest("testTruncateDuringScan"));
869                 
870                 // This testcase fails in DerbyNetClient framework due to
871 // DERBY-1696
872 if (usingEmbedded()) {
873                     suite.addTest
874                         (new ConcurrencyTest("testUpdatePurgedTuple1"));
875                 }
876                 
877             }
878         }
879         
880         // Since this test relies on lock waiting, setting this property will
881 // make it go a lot faster:
882 final Properties properties = new Properties ();
883         properties.setProperty("derby.locks.waitTimeout", "4");
884         
885         return new DatabasePropertyTestSetup(suite, properties);
886     }
887     
888 }
889

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags