use of com.mysql.cj.xdevapi.Statement in project aws-mysql-jdbc by awslabs.
the class CollectionFindTest method testCollectionRowLockOptions.
@Test
public void testCollectionRowLockOptions() throws Exception {
assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.5")), "MySQL 8.0.5+ is required to run this test.");
Function<DocResult, List<String>> asStringList = rr -> rr.fetchAll().stream().map(d -> ((JsonString) d.get("_id")).getString()).collect(Collectors.toList());
this.collection.add("{\"_id\":\"1\", \"a\":1}").add("{\"_id\":\"2\", \"a\":1}").add("{\"_id\":\"3\", \"a\":1}").execute();
Session session1 = null;
Session session2 = null;
try {
session1 = new SessionFactory().getSession(this.testProperties);
Collection col1 = session1.getDefaultSchema().getCollection(this.collectionName);
session2 = new SessionFactory().getSession(this.testProperties);
Collection col2 = session2.getDefaultSchema().getCollection(this.collectionName);
DocResult res;
CompletableFuture<DocResult> futRes;
/*
* 1. Shared Lock in both sessions.
*/
// session2.lockShared() returns data immediately.
session1.startTransaction();
col1.find("_id = '1'").lockShared().execute();
session2.startTransaction();
res = col2.find("_id < '3'").lockShared().execute();
assertEquals(2, asStringList.apply(res).size());
assertThat(asStringList.apply(res), hasItems("1", "2"));
session2.rollback();
session2.startTransaction();
futRes = col2.find("_id < '3'").lockShared().executeAsync();
res = futRes.get(3, TimeUnit.SECONDS);
assertTrue(futRes.isDone());
assertEquals(2, asStringList.apply(res).size());
assertThat(asStringList.apply(res), CoreMatchers.hasItems("1", "2"));
session2.rollback();
session1.rollback();
// session2.lockShared(NOWAIT) returns data immediately.
session1.startTransaction();
col1.find("_id = '1'").lockShared().execute();
session2.startTransaction();
res = col2.find("_id < '3'").lockShared(Statement.LockContention.NOWAIT).execute();
assertEquals(2, asStringList.apply(res).size());
assertThat(asStringList.apply(res), CoreMatchers.hasItems("1", "2"));
session2.rollback();
session2.startTransaction();
futRes = col2.find("_id < '3'").lockShared(Statement.LockContention.NOWAIT).executeAsync();
res = futRes.get(3, TimeUnit.SECONDS);
assertTrue(futRes.isDone());
assertEquals(2, asStringList.apply(res).size());
assertThat(asStringList.apply(res), CoreMatchers.hasItems("1", "2"));
session2.rollback();
session1.rollback();
// session2.lockShared(SKIP_LOCK) returns data immediately.
session1.startTransaction();
col1.find("_id = '1'").lockShared().execute();
session2.startTransaction();
res = col2.find("_id < '3'").lockShared(Statement.LockContention.SKIP_LOCKED).execute();
assertEquals(2, asStringList.apply(res).size());
assertThat(asStringList.apply(res), CoreMatchers.hasItems("1", "2"));
session2.rollback();
session2.startTransaction();
futRes = col2.find("_id < '3'").lockShared(Statement.LockContention.SKIP_LOCKED).executeAsync();
res = futRes.get(3, TimeUnit.SECONDS);
assertTrue(futRes.isDone());
assertEquals(2, asStringList.apply(res).size());
assertThat(asStringList.apply(res), CoreMatchers.hasItems("1", "2"));
session2.rollback();
session1.rollback();
/*
* 2. Shared Lock in first session and exclusive lock in second.
*/
// session2.lockExclusive() blocks until session1 ends.
session1.startTransaction();
col1.find("_id = '1'").lockShared().execute();
// session2.startTransaction();
// res = col2.find("_id < '3'").lockExclusive().execute(); (Can't test)
// session2.rollback();
session2.startTransaction();
futRes = col2.find("_id < '3'").lockExclusive().executeAsync();
final CompletableFuture<DocResult> fr1 = futRes;
assertThrows(TimeoutException.class, () -> fr1.get(3, TimeUnit.SECONDS));
// Unlocks session2.
session1.rollback();
res = futRes.get(3, TimeUnit.SECONDS);
assertTrue(futRes.isDone());
assertEquals(2, asStringList.apply(res).size());
assertThat(asStringList.apply(res), CoreMatchers.hasItems("1", "2"));
session2.rollback();
// session2.lockExclusive(NOWAIT) should return locking error.
session1.startTransaction();
col1.find("_id = '1'").lockShared().execute();
session2.startTransaction();
assertThrows(XProtocolError.class, "ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.", () -> col2.find("_id < '3'").lockExclusive(Statement.LockContention.NOWAIT).execute());
session2.rollback();
session2.startTransaction();
futRes = col2.find("_id < '3'").lockExclusive(Statement.LockContention.NOWAIT).executeAsync();
final CompletableFuture<DocResult> fr2 = futRes;
assertThrows(ExecutionException.class, ".*XProtocolError: ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.", () -> fr2.get(3, TimeUnit.SECONDS));
session2.rollback();
session1.rollback();
// session2.lockExclusive(SKIP_LOCK) should return (unlocked) data immediately.
session1.startTransaction();
col1.find("_id = '1'").lockShared().execute();
session2.startTransaction();
res = col2.find("_id < '3'").lockExclusive(Statement.LockContention.SKIP_LOCKED).execute();
assertEquals(1, asStringList.apply(res).size());
assertThat(asStringList.apply(res), CoreMatchers.hasItems("2"));
session2.rollback();
session2.startTransaction();
futRes = col2.find("_id < '3'").lockExclusive(Statement.LockContention.SKIP_LOCKED).executeAsync();
res = futRes.get(3, TimeUnit.SECONDS);
assertTrue(futRes.isDone());
assertEquals(1, asStringList.apply(res).size());
assertThat(asStringList.apply(res), CoreMatchers.hasItems("2"));
session2.rollback();
session1.rollback();
/*
* 3. Exclusive Lock in first session and shared lock in second.
*/
// session2.lockShared() blocks until session1 ends.
session1.startTransaction();
col1.find("_id = '1'").lockExclusive().execute();
// session2.startTransaction();
// res = col2.find("_id < '3'").lockShared().execute(); (Can't test)
// session2.rollback();
session2.startTransaction();
futRes = col2.find("_id < '3'").lockShared().executeAsync();
final CompletableFuture<DocResult> fr3 = futRes;
assertThrows(TimeoutException.class, () -> fr3.get(3, TimeUnit.SECONDS));
// Unlocks session2.
session1.rollback();
res = futRes.get(3, TimeUnit.SECONDS);
assertTrue(futRes.isDone());
assertEquals(2, asStringList.apply(res).size());
assertThat(asStringList.apply(res), CoreMatchers.hasItems("1", "2"));
session2.rollback();
// session2.lockShared(NOWAIT) should return locking error.
session1.startTransaction();
col1.find("_id = '1'").lockExclusive().execute();
session2.startTransaction();
assertThrows(XProtocolError.class, "ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.", () -> col2.find("_id < '3'").lockShared(Statement.LockContention.NOWAIT).execute());
session2.rollback();
session2.startTransaction();
futRes = col2.find("_id < '3'").lockShared(Statement.LockContention.NOWAIT).executeAsync();
final CompletableFuture<DocResult> fr4 = futRes;
assertThrows(ExecutionException.class, ".*XProtocolError: ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.", () -> fr4.get(3, TimeUnit.SECONDS));
session2.rollback();
session1.rollback();
// session2.lockShared(SKIP_LOCK) should return (unlocked) data immediately.
session1.startTransaction();
col1.find("_id = '1'").lockExclusive().execute();
session2.startTransaction();
res = col2.find("_id < '3'").lockShared(Statement.LockContention.SKIP_LOCKED).execute();
assertEquals(1, asStringList.apply(res).size());
assertThat(asStringList.apply(res), CoreMatchers.hasItems("2"));
session2.rollback();
session2.startTransaction();
futRes = col2.find("_id < '3'").lockShared(Statement.LockContention.SKIP_LOCKED).executeAsync();
res = futRes.get(3, TimeUnit.SECONDS);
assertTrue(futRes.isDone());
assertEquals(1, asStringList.apply(res).size());
assertThat(asStringList.apply(res), CoreMatchers.hasItems("2"));
session2.rollback();
session1.rollback();
/*
* 4. Exclusive Lock in both sessions.
*/
// session2.lockExclusive() blocks until session1 ends.
session1.startTransaction();
col1.find("_id = '1'").lockExclusive().execute();
// session2.startTransaction();
// res = col2.find("_id < '3'").lockExclusive().execute(); (Can't test)
// session2.rollback();
session2.startTransaction();
futRes = col2.find("_id < '3'").lockExclusive().executeAsync();
final CompletableFuture<DocResult> fr5 = futRes;
assertThrows(TimeoutException.class, () -> fr5.get(3, TimeUnit.SECONDS));
// Unlocks session2.
session1.rollback();
res = futRes.get(3, TimeUnit.SECONDS);
assertTrue(futRes.isDone());
assertEquals(2, asStringList.apply(res).size());
assertThat(asStringList.apply(res), CoreMatchers.hasItems("1", "2"));
session2.rollback();
// session2.lockExclusive(NOWAIT) should return locking error.
session1.startTransaction();
col1.find("_id = '1'").lockExclusive().execute();
session2.startTransaction();
assertThrows(XProtocolError.class, "ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.", () -> col2.find("_id < '3'").lockExclusive(Statement.LockContention.NOWAIT).execute());
session2.rollback();
session2.startTransaction();
futRes = col2.find("_id < '3'").lockExclusive(Statement.LockContention.NOWAIT).executeAsync();
final CompletableFuture<DocResult> fr6 = futRes;
assertThrows(ExecutionException.class, ".*XProtocolError: ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.", () -> fr6.get(3, TimeUnit.SECONDS));
session2.rollback();
session1.rollback();
// session2.lockExclusive(SKIP_LOCK) should return (unlocked) data immediately.
session1.startTransaction();
col1.find("_id = '1'").lockExclusive().execute();
session2.startTransaction();
res = col2.find("_id < '3'").lockExclusive(Statement.LockContention.SKIP_LOCKED).execute();
assertEquals(1, asStringList.apply(res).size());
assertThat(asStringList.apply(res), CoreMatchers.hasItems("2"));
session2.rollback();
session2.startTransaction();
futRes = col2.find("_id < '3'").lockExclusive(Statement.LockContention.SKIP_LOCKED).executeAsync();
res = futRes.get(3, TimeUnit.SECONDS);
assertTrue(futRes.isDone());
assertEquals(1, asStringList.apply(res).size());
assertThat(asStringList.apply(res), CoreMatchers.hasItems("2"));
session2.rollback();
session1.rollback();
} finally {
if (session1 != null) {
session1.close();
}
if (session2 != null) {
session2.close();
}
}
}
use of com.mysql.cj.xdevapi.Statement in project aws-mysql-jdbc by awslabs.
the class TableSelectTest method testTableRowLockOptions.
@Test
public void testTableRowLockOptions() throws Exception {
assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.5")), "MySQL 8.0.5+ is required to run this test.");
Function<RowResult, List<String>> asStringList = rr -> rr.fetchAll().stream().map(r -> r.getString(0)).collect(Collectors.toList());
sqlUpdate("DROP TABLE IF EXISTS testTableRowLockOptions");
sqlUpdate("CREATE TABLE testTableRowLockOptions (_id VARCHAR(32), a VARCHAR(20))");
// index is required to enable row locking
sqlUpdate("CREATE UNIQUE INDEX myIndex ON testTableRowLockOptions (_id)");
sqlUpdate("INSERT INTO testTableRowLockOptions VALUES ('1', '1'), ('2', '1'), ('3', '1')");
Session session1 = null;
Session session2 = null;
try {
session1 = new SessionFactory().getSession(this.testProperties);
Table table1 = session1.getDefaultSchema().getTable("testTableRowLockOptions");
session2 = new SessionFactory().getSession(this.testProperties);
Table table2 = session2.getDefaultSchema().getTable("testTableRowLockOptions");
RowResult res;
CompletableFuture<RowResult> futRes;
/*
* 1. Shared Lock in both sessions.
*/
// session2.lockShared() returns data immediately.
session1.startTransaction();
table1.select("_id").where("_id = '1'").lockShared().execute();
session2.startTransaction();
res = table2.select("_id").where("_id < '3'").lockShared().execute();
assertEquals(2, asStringList.apply(res).size());
assertThat(asStringList.apply(res), hasItems("1", "2"));
session2.rollback();
session2.startTransaction();
futRes = table2.select("_id").where("_id < '3'").lockShared().executeAsync();
res = futRes.get(3, TimeUnit.SECONDS);
assertTrue(futRes.isDone());
assertEquals(2, asStringList.apply(res).size());
assertThat(asStringList.apply(res), hasItems("1", "2"));
session2.rollback();
session1.rollback();
// session2.lockShared(NOWAIT) returns data immediately.
session1.startTransaction();
table1.select("_id").where("_id = '1'").lockShared().execute();
session2.startTransaction();
res = table2.select("_id").where("_id < '3'").lockShared(Statement.LockContention.NOWAIT).execute();
assertEquals(2, asStringList.apply(res).size());
assertThat(asStringList.apply(res), hasItems("1", "2"));
session2.rollback();
session2.startTransaction();
futRes = table2.select("_id").where("_id < '3'").lockShared(Statement.LockContention.NOWAIT).executeAsync();
res = futRes.get(3, TimeUnit.SECONDS);
assertTrue(futRes.isDone());
assertEquals(2, asStringList.apply(res).size());
assertThat(asStringList.apply(res), hasItems("1", "2"));
session2.rollback();
session1.rollback();
// session2.lockShared(SKIP_LOCK) returns data immediately.
session1.startTransaction();
table1.select("_id").where("_id = '1'").lockShared().execute();
session2.startTransaction();
res = table2.select("_id").where("_id < '3'").lockShared(Statement.LockContention.SKIP_LOCKED).execute();
assertEquals(2, asStringList.apply(res).size());
assertThat(asStringList.apply(res), hasItems("1", "2"));
session2.rollback();
session2.startTransaction();
futRes = table2.select("_id").where("_id < '3'").lockShared(Statement.LockContention.SKIP_LOCKED).executeAsync();
res = futRes.get(3, TimeUnit.SECONDS);
assertTrue(futRes.isDone());
assertEquals(2, asStringList.apply(res).size());
assertThat(asStringList.apply(res), hasItems("1", "2"));
session2.rollback();
session1.rollback();
/*
* 2. Shared Lock in first session and exclusive lock in second.
*/
// session2.lockExclusive() blocks until session1 ends.
session1.startTransaction();
table1.select("_id").where("_id = '1'").lockShared().execute();
// session2.startTransaction();
// res = table2.select("_id").where("_id < '3'").lockExclusive().execute(); (Can't test)
// session2.rollback();
session2.startTransaction();
futRes = table2.select("_id").where("_id < '3'").lockExclusive().executeAsync();
final CompletableFuture<RowResult> fr1 = futRes;
assertThrows(TimeoutException.class, () -> fr1.get(3, TimeUnit.SECONDS));
// Unlocks session2.
session1.rollback();
res = futRes.get(3, TimeUnit.SECONDS);
assertTrue(futRes.isDone());
assertEquals(2, asStringList.apply(res).size());
assertThat(asStringList.apply(res), hasItems("1", "2"));
session2.rollback();
// session2.lockExclusive(NOWAIT) should return locking error.
session1.startTransaction();
table1.select("_id").where("_id = '1'").lockShared().execute();
session2.startTransaction();
assertThrows(XProtocolError.class, "ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.", () -> table2.select("_id").where("_id < '3'").lockExclusive(Statement.LockContention.NOWAIT).execute());
session2.rollback();
session2.startTransaction();
futRes = table2.select("_id").where("_id < '3'").lockExclusive(Statement.LockContention.NOWAIT).executeAsync();
final CompletableFuture<RowResult> fr2 = futRes;
assertThrows(ExecutionException.class, ".*XProtocolError: ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.", () -> fr2.get(3, TimeUnit.SECONDS));
session2.rollback();
session1.rollback();
// session2.lockExclusive(SKIP_LOCK) should return (unlocked) data immediately.
session1.startTransaction();
table1.select("_id").where("_id = '1'").lockShared().execute();
session2.startTransaction();
res = table2.select("_id").where("_id < '3'").lockExclusive(Statement.LockContention.SKIP_LOCKED).execute();
assertEquals(1, asStringList.apply(res).size());
assertThat(asStringList.apply(res), hasItems("2"));
session2.rollback();
session2.startTransaction();
futRes = table2.select("_id").where("_id < '3'").lockExclusive(Statement.LockContention.SKIP_LOCKED).executeAsync();
res = futRes.get(3, TimeUnit.SECONDS);
assertTrue(futRes.isDone());
assertEquals(1, asStringList.apply(res).size());
assertThat(asStringList.apply(res), hasItems("2"));
session2.rollback();
session1.rollback();
/*
* 3. Exclusive Lock in first session and shared lock in second.
*/
// session2.lockShared() blocks until session1 ends.
session1.startTransaction();
table1.select("_id").where("_id = '1'").lockExclusive().execute();
// session2.startTransaction();
// res = table2.select("_id").where("_id < '3'").lockShared().execute(); (Can't test)
// session2.rollback();
session2.startTransaction();
futRes = table2.select("_id").where("_id < '3'").lockShared().executeAsync();
final CompletableFuture<RowResult> fr3 = futRes;
assertThrows(TimeoutException.class, () -> fr3.get(3, TimeUnit.SECONDS));
// Unlocks session2.
session1.rollback();
res = futRes.get(3, TimeUnit.SECONDS);
assertTrue(futRes.isDone());
assertEquals(2, asStringList.apply(res).size());
assertThat(asStringList.apply(res), hasItems("1", "2"));
session2.rollback();
// session2.lockShared(NOWAIT) should return locking error.
session1.startTransaction();
table1.select("_id").where("_id = '1'").lockExclusive().execute();
session2.startTransaction();
assertThrows(XProtocolError.class, "ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.", () -> table2.select("_id").where("_id < '3'").lockShared(Statement.LockContention.NOWAIT).execute());
session2.rollback();
session2.startTransaction();
futRes = table2.select("_id").where("_id < '3'").lockShared(Statement.LockContention.NOWAIT).executeAsync();
final CompletableFuture<RowResult> fr4 = futRes;
assertThrows(ExecutionException.class, ".*XProtocolError: ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.", () -> fr4.get(3, TimeUnit.SECONDS));
session2.rollback();
session1.rollback();
// session2.lockShared(SKIP_LOCK) should return (unlocked) data immediately.
session1.startTransaction();
table1.select("_id").where("_id = '1'").lockExclusive().execute();
session2.startTransaction();
res = table2.select("_id").where("_id < '3'").lockShared(Statement.LockContention.SKIP_LOCKED).execute();
assertEquals(1, asStringList.apply(res).size());
assertThat(asStringList.apply(res), hasItems("2"));
session2.rollback();
session2.startTransaction();
futRes = table2.select("_id").where("_id < '3'").lockShared(Statement.LockContention.SKIP_LOCKED).executeAsync();
res = futRes.get(3, TimeUnit.SECONDS);
assertTrue(futRes.isDone());
assertEquals(1, asStringList.apply(res).size());
assertThat(asStringList.apply(res), hasItems("2"));
session2.rollback();
session1.rollback();
/*
* 4. Exclusive Lock in both sessions.
*/
// session2.lockExclusive() blocks until session1 ends.
session1.startTransaction();
table1.select("_id").where("_id = '1'").lockExclusive().execute();
// session2.startTransaction();
// res = table2.select("_id").where("_id < '3'").lockExclusive().execute(); (Can't test)
// session2.rollback();
session2.startTransaction();
futRes = table2.select("_id").where("_id < '3'").lockExclusive().executeAsync();
final CompletableFuture<RowResult> fr5 = futRes;
assertThrows(TimeoutException.class, () -> fr5.get(3, TimeUnit.SECONDS));
// Unlocks session2.
session1.rollback();
res = futRes.get(3, TimeUnit.SECONDS);
assertTrue(futRes.isDone());
assertEquals(2, asStringList.apply(res).size());
assertThat(asStringList.apply(res), hasItems("1", "2"));
session2.rollback();
// session2.lockExclusive(NOWAIT) should return locking error.
session1.startTransaction();
table1.select("_id").where("_id = '1'").lockExclusive().execute();
session2.startTransaction();
assertThrows(XProtocolError.class, "ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.", () -> table2.select("_id").where("_id < '3'").lockExclusive(Statement.LockContention.NOWAIT).execute());
session2.rollback();
session2.startTransaction();
futRes = table2.select("_id").where("_id < '3'").lockExclusive(Statement.LockContention.NOWAIT).executeAsync();
final CompletableFuture<RowResult> fr6 = futRes;
assertThrows(ExecutionException.class, ".*XProtocolError: ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.", () -> fr6.get(3, TimeUnit.SECONDS));
session2.rollback();
session1.rollback();
// session2.lockExclusive(SKIP_LOCK) should return (unlocked) data immediately.
session1.startTransaction();
table1.select("_id").where("_id = '1'").lockExclusive().execute();
session2.startTransaction();
res = table2.select("_id").where("_id < '3'").lockExclusive(Statement.LockContention.SKIP_LOCKED).execute();
assertEquals(1, asStringList.apply(res).size());
assertThat(asStringList.apply(res), hasItems("2"));
session2.rollback();
session2.startTransaction();
futRes = table2.select("_id").where("_id < '3'").lockExclusive(Statement.LockContention.SKIP_LOCKED).executeAsync();
res = futRes.get(3, TimeUnit.SECONDS);
assertTrue(futRes.isDone());
assertEquals(1, asStringList.apply(res).size());
assertThat(asStringList.apply(res), hasItems("2"));
session2.rollback();
session1.rollback();
} finally {
if (session1 != null) {
session1.close();
}
if (session2 != null) {
session2.close();
}
sqlUpdate("DROP TABLE IF EXISTS testTableRowLockOptions");
}
}
Aggregations