001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (license2)
004: * Initial Developer: H2 Group
005: */
006: package org.h2.test.mvcc;
007:
008: import java.sql.Connection;
009: import java.sql.DriverManager;
010: import java.sql.ResultSet;
011: import java.sql.SQLException;
012: import java.sql.Statement;
013: import java.util.Random;
014:
015: import org.h2.constant.ErrorCode;
016: import org.h2.test.TestBase;
017: import org.h2.tools.DeleteDbFiles;
018:
019: /**
020: * Basic MVCC (multi version concurrency) test cases.
021: */
022: public class TestMvcc1 extends TestBase {
023:
024: Connection c1, c2;
025: Statement s1, s2;
026:
027: public void test() throws Exception {
028: testSetMode();
029: testCases();
030: }
031:
032: private void testSetMode() throws Exception {
033: DeleteDbFiles.execute(null, "test", true);
034: Class.forName("org.h2.Driver");
035: c1 = DriverManager.getConnection("jdbc:h2:test", "sa", "sa");
036: Statement stat = c1.createStatement();
037: ResultSet rs = stat
038: .executeQuery("select * from information_schema.settings where name='MVCC'");
039: rs.next();
040: check("FALSE", rs.getString("VALUE"));
041: try {
042: stat.execute("SET MVCC TRUE");
043: error();
044: } catch (SQLException e) {
045: check(ErrorCode.CANNOT_CHANGE_SETTING_WHEN_OPEN_1, e
046: .getErrorCode());
047: }
048: rs = stat
049: .executeQuery("select * from information_schema.settings where name='MVCC'");
050: rs.next();
051: check("FALSE", rs.getString("VALUE"));
052: c1.close();
053: }
054:
055: private void testCases() throws Exception {
056: if (!config.mvcc) {
057: return;
058: }
059: // TODO Prio 1: make unit test work (remaining problem: optimization for
060: // select min/max)
061: // TODO Prio 1: document: exclusive table lock still used when altering
062: // tables, adding indexes, select ... for update; table level locks are
063: // checked
064: // TODO Prio 1: free up disk space (for deleted rows and old versions of
065: // updated rows) on commit
066: // TODO Prio 1: ScanIndex: never remove uncommitted data from cache
067: // (lost sessionId)
068: // TODO Prio 1: Test with Hibernate
069: // TODO Prio 2: if MVCC is used, rows of transactions need to fit in
070: // memory
071: // TODO Prio 2: write the log only when committed; remove restriction at
072: // Record.canRemove
073: // TODO Prio 2: getRowCount: different row count for different indexes
074: // (MultiVersionIndex)
075: // TODO Prio 2: getRowCount: different row count for different sessions:
076: // TableLink (use different connections?)
077: // TODO Prio 2: getFirst / getLast in MultiVersionIndex
078: // TODO Prio 2: snapshot isolation (currently read-committed, not
079: // repeatable read)
080:
081: // TODO test: one thread appends, the other
082: // selects new data (select * from test where id > ?) and deletes
083:
084: DeleteDbFiles.execute(null, "test", true);
085: Class.forName("org.h2.Driver");
086: c1 = DriverManager.getConnection(
087: "jdbc:h2:test;MVCC=TRUE;LOCK_TIMEOUT=10", "sa", "sa");
088: s1 = c1.createStatement();
089: c2 = DriverManager.getConnection(
090: "jdbc:h2:test;MVCC=TRUE;LOCK_TIMEOUT=10", "sa", "sa");
091: s2 = c2.createStatement();
092: c1.setAutoCommit(false);
093: c2.setAutoCommit(false);
094:
095: // it should not be possible to drop a table
096: // when an uncommitted transaction changed something
097: s1.execute("create table test(id int primary key)");
098: s1.execute("insert into test values(1)");
099: try {
100: s2.execute("drop table test");
101: error();
102: } catch (SQLException e) {
103: // lock timeout expected
104: checkNotGeneralException(e);
105: }
106: c1.rollback();
107: s2.execute("drop table test");
108: c2.rollback();
109:
110: // table scan problem
111: s1.execute("create table test(id int, name varchar)");
112: s1.execute("insert into test values(1, 'A'), (2, 'B')");
113: c1.commit();
114: test(s1, "select count(*) from test where name<>'C'", "2");
115: s2.execute("update test set name='B2' where id=2");
116: test(s1, "select count(*) from test where name<>'C'", "2");
117: c2.commit();
118: s2.execute("drop table test");
119: c2.rollback();
120:
121: // referential integrity problem
122: s1
123: .execute("create table a (id integer identity not null, code varchar(10) not null, primary key(id))");
124: s1
125: .execute("create table b (name varchar(100) not null, a integer, primary key(name), foreign key(a) references a(id))");
126: s1.execute("insert into a(code) values('one')");
127: try {
128: s2.execute("insert into b values('un B', 1)");
129: error();
130: } catch (SQLException e) {
131: checkNotGeneralException(e);
132: }
133: c2.commit();
134: c1.rollback();
135: s1.execute("drop table a, b");
136: c2.commit();
137:
138: // select for update should do an exclusive lock, even with mvcc
139: s1
140: .execute("create table test(id int primary key, name varchar(255))");
141: s1.execute("insert into test values(1, 'y')");
142: c1.commit();
143: s2.execute("select * from test for update");
144: try {
145: s1.execute("insert into test values(2, 'x')");
146: error();
147: } catch (SQLException e) {
148: // lock timeout expected
149: checkNotGeneralException(e);
150: }
151: c2.rollback();
152: s1.execute("drop table test");
153: c1.commit();
154: c2.commit();
155:
156: s1
157: .execute("create table test(id int primary key, name varchar(255))");
158: s2.execute("insert into test values(4, 'Hello')");
159: c2.rollback();
160: test(s1, "select count(*) from test where name = 'Hello'", "0");
161: test(s2, "select count(*) from test where name = 'Hello'", "0");
162: c1.commit();
163: c2.commit();
164: s1.execute("DROP TABLE TEST");
165:
166: s1
167: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
168: s1.execute("INSERT INTO TEST VALUES(1, 'Test')");
169: c1.commit();
170: test(s1, "select max(id) from test", "1");
171: s1.execute("INSERT INTO TEST VALUES(2, 'World')");
172: c1.rollback();
173: test(s1, "select max(id) from test", "1");
174: c1.commit();
175: c2.commit();
176: s1.execute("DROP TABLE TEST");
177:
178: s1
179: .execute("create table test as select * from table(id int=(1, 2))");
180: s1.execute("update test set id=1 where id=1");
181: s1.execute("select max(id) from test");
182: test(s1, "select max(id) from test", "2");
183: c1.commit();
184: c2.commit();
185: s1.execute("DROP TABLE TEST");
186:
187: s1.execute("CREATE TABLE TEST(ID INT)");
188: s1.execute("INSERT INTO TEST VALUES(1)");
189: c1.commit();
190: test(s2, "SELECT COUNT(*) FROM TEST", "1");
191: s1.executeUpdate("DELETE FROM TEST");
192: test(s2, "SELECT COUNT(*) FROM TEST", "1");
193: test(s1, "SELECT COUNT(*) FROM TEST", "0");
194: c1.commit();
195: test(s2, "SELECT COUNT(*) FROM TEST", "0");
196: c1.commit();
197: c2.commit();
198: s1.execute("DROP TABLE TEST");
199:
200: s1.execute("CREATE TABLE TEST(ID INT)");
201: s1.execute("INSERT INTO TEST VALUES(1)");
202: c1.commit();
203: s1.execute("DELETE FROM TEST");
204: test(s1, "SELECT COUNT(*) FROM TEST", "0");
205: c1.commit();
206: test(s1, "SELECT COUNT(*) FROM TEST", "0");
207: s1.execute("INSERT INTO TEST VALUES(1)");
208: s1.execute("DELETE FROM TEST");
209: c1.commit();
210: test(s1, "SELECT COUNT(*) FROM TEST", "0");
211: s1.execute("DROP TABLE TEST");
212:
213: s1
214: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
215: s1.execute("INSERT INTO TEST VALUES(1, 'Hello'), (2, 'World')");
216: test(s2, "SELECT COUNT(*) FROM TEST", "0");
217: c1.commit();
218: test(s2, "SELECT COUNT(*) FROM TEST", "2");
219: s1.execute("INSERT INTO TEST VALUES(3, '!')");
220: c1.rollback();
221: test(s2, "SELECT COUNT(*) FROM TEST", "2");
222: s1.execute("DROP TABLE TEST");
223: c1.commit();
224:
225: s1
226: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
227: s1.execute("INSERT INTO TEST VALUES(1, 'Hello')");
228: s1.execute("DELETE FROM TEST");
229: test(s2, "SELECT COUNT(*) FROM TEST", "0");
230: c1.commit();
231: test(s2, "SELECT COUNT(*) FROM TEST", "0");
232: s1.execute("DROP TABLE TEST");
233: c1.commit();
234:
235: s1.execute("CREATE TABLE TEST(ID INT IDENTITY, NAME VARCHAR)");
236: s1.execute("INSERT INTO TEST(NAME) VALUES('Ruebezahl')");
237: test(s2, "SELECT COUNT(*) FROM TEST", "0");
238: test(s1, "SELECT COUNT(*) FROM TEST", "1");
239: s1.execute("DROP TABLE TEST");
240: c1.commit();
241:
242: s1.execute("CREATE TABLE TEST(ID INT IDENTITY, NAME VARCHAR)");
243: s1.execute("INSERT INTO TEST(NAME) VALUES('Ruebezahl')");
244: s1.execute("INSERT INTO TEST(NAME) VALUES('Ruebezahl')");
245: s1.execute("DROP TABLE TEST");
246: c1.commit();
247:
248: s1
249: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
250: s1.execute("INSERT INTO TEST VALUES(1, 'Hello')");
251: c1.commit();
252: s1.execute("DELETE FROM TEST WHERE ID=1");
253: c1.rollback();
254: s1.execute("DROP TABLE TEST");
255: c1.commit();
256:
257: Random random = new Random(1);
258: s1.execute("CREATE TABLE TEST(ID INT IDENTITY, NAME VARCHAR)");
259: Statement s;
260: Connection c;
261: for (int i = 0; i < 1000; i++) {
262: if (random.nextBoolean()) {
263: s = s1;
264: c = c1;
265: } else {
266: s = s2;
267: c = c2;
268: }
269: switch (random.nextInt(5)) {
270: case 0:
271: s.execute("INSERT INTO TEST(NAME) VALUES('Hello')");
272: break;
273: case 1:
274: s.execute("UPDATE TEST SET NAME=" + i + " WHERE ID="
275: + random.nextInt(i));
276: break;
277: case 2:
278: s.execute("DELETE FROM TEST WHERE ID="
279: + random.nextInt(i));
280: break;
281: case 3:
282: c.commit();
283: break;
284: case 4:
285: c.rollback();
286: break;
287: default:
288: }
289: s1.execute("SELECT * FROM TEST ORDER BY ID");
290: s2.execute("SELECT * FROM TEST ORDER BY ID");
291: }
292: c2.rollback();
293: s1.execute("DROP TABLE TEST");
294: c1.commit();
295: c2.commit();
296:
297: random = new Random(1);
298: s1
299: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
300: for (int i = 0; i < 1000; i++) {
301: if (random.nextBoolean()) {
302: s = s1;
303: c = c1;
304: } else {
305: s = s2;
306: c = c2;
307: }
308: switch (random.nextInt(5)) {
309: case 0:
310: s
311: .execute("INSERT INTO TEST VALUES(" + i
312: + ", 'Hello')");
313: break;
314: case 1:
315: try {
316: s.execute("UPDATE TEST SET NAME=" + i
317: + " WHERE ID=" + random.nextInt(i));
318: } catch (SQLException e) {
319: check(e.getErrorCode(),
320: ErrorCode.CONCURRENT_UPDATE_1);
321: }
322: break;
323: case 2:
324: s.execute("DELETE FROM TEST WHERE ID="
325: + random.nextInt(i));
326: break;
327: case 3:
328: c.commit();
329: break;
330: case 4:
331: c.rollback();
332: break;
333: default:
334: }
335: s1.execute("SELECT * FROM TEST ORDER BY ID");
336: s2.execute("SELECT * FROM TEST ORDER BY ID");
337: }
338: c2.rollback();
339: s1.execute("DROP TABLE TEST");
340: c1.commit();
341: c2.commit();
342:
343: s1.execute("CREATE TABLE TEST(ID INT, NAME VARCHAR)");
344: s1.execute("INSERT INTO TEST VALUES(1, 'Hello')");
345: test(s2, "SELECT COUNT(*) FROM TEST WHERE NAME!='X'", "0");
346: test(s1, "SELECT COUNT(*) FROM TEST WHERE NAME!='X'", "1");
347: c1.commit();
348: test(s2, "SELECT COUNT(*) FROM TEST WHERE NAME!='X'", "1");
349: test(s2, "SELECT COUNT(*) FROM TEST WHERE NAME!='X'", "1");
350: s1.execute("DROP TABLE TEST");
351: c1.commit();
352: c2.commit();
353:
354: s1
355: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
356: s1.execute("INSERT INTO TEST VALUES(1, 'Hello')");
357: test(s2, "SELECT COUNT(*) FROM TEST WHERE ID<100", "0");
358: test(s1, "SELECT COUNT(*) FROM TEST WHERE ID<100", "1");
359: c1.commit();
360: test(s2, "SELECT COUNT(*) FROM TEST WHERE ID<100", "1");
361: test(s2, "SELECT COUNT(*) FROM TEST WHERE ID<100", "1");
362: s1.execute("DROP TABLE TEST");
363: c1.commit();
364: c2.commit();
365:
366: s1
367: .execute("CREATE TABLE TEST(ID INT, NAME VARCHAR, PRIMARY KEY(ID, NAME))");
368: s1.execute("INSERT INTO TEST VALUES(1, 'Hello')");
369: c1.commit();
370: test(s2, "SELECT NAME FROM TEST WHERE ID=1", "Hello");
371: s1.execute("UPDATE TEST SET NAME = 'Hallo' WHERE ID=1");
372: test(s2, "SELECT NAME FROM TEST WHERE ID=1", "Hello");
373: test(s1, "SELECT NAME FROM TEST WHERE ID=1", "Hallo");
374: s1.execute("DROP TABLE TEST");
375: c1.commit();
376: c2.commit();
377:
378: s1
379: .execute("create table test(id int primary key, name varchar(255))");
380: s1.execute("insert into test values(1, 'Hello'), (2, 'World')");
381: c1.commit();
382: try {
383: s1.execute("update test set id=2 where id=1");
384: error();
385: } catch (SQLException e) {
386: checkNotGeneralException(e);
387: }
388: ResultSet rs = s1
389: .executeQuery("select * from test order by id");
390: check(rs.next());
391: check(rs.getInt(1), 1);
392: check(rs.getString(2), "Hello");
393: check(rs.next());
394: check(rs.getInt(1), 2);
395: check(rs.getString(2), "World");
396: checkFalse(rs.next());
397:
398: rs = s2.executeQuery("select * from test order by id");
399: check(rs.next());
400: check(rs.getInt(1), 1);
401: check(rs.getString(2), "Hello");
402: check(rs.next());
403: check(rs.getInt(1), 2);
404: check(rs.getString(2), "World");
405: checkFalse(rs.next());
406: s1.execute("drop table test");
407: c1.commit();
408: c2.commit();
409:
410: c1.close();
411: c2.close();
412:
413: }
414:
415: private void test(Statement stat, String sql, String expected)
416: throws Exception {
417: ResultSet rs = stat.executeQuery(sql);
418: if (rs.next()) {
419: String s = rs.getString(1);
420: if (expected == null) {
421: throw new Error("expected: no rows, got: " + s);
422: } else if (!expected.equals(s)) {
423: throw new Error("expected: " + expected + ", got: " + s);
424: }
425: } else {
426: if (expected != null) {
427: throw new Error("expected: " + expected
428: + ", got: no rows");
429: }
430: }
431: }
432: }
|