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