/// <summary>
/// Locks matching rows against updates.
/// </summary>
/// <param name="lockOption">Optional row <see cref="LockContention">lock option</see> to use.</param>
/// <returns>This same <see cref="FindStatement"/> object set with the lock shared option.</returns>
/// <exception cref="MySqlException">The server version is lower than 8.0.3.</exception>
public FindStatement LockShared(LockContention lockOption = LockContention.Default)
{
if (!this.Session.InternalSession.GetServerVersion().isAtLeast(8, 0, 3))
{
throw new MySqlException(string.Format(ResourcesX.FunctionalityNotSupported, "8.0.3"));
}
findParams.Locking = Protocol.X.RowLock.SharedLock;
findParams.LockingOption = lockOption;
SetChanged();
return(this);
}
public void LockExclusiveAndSharedWithWaitingOptions(LockContention lockOption, LockMode lockMode)
{
if (!session.XSession.GetServerVersion().isAtLeast(8, 0, 3))
{
return;
}
CreateBooksTable();
string tableName = "books";
string schemaName = "test";
// first session locks the row
using (Session s1 = MySQLX.GetSession(ConnectionString))
{
var t1 = s1.GetSchema(schemaName).GetTable(tableName);
s1.StartTransaction();
RowResult r1 = ExecuteSelectStatement(t1.Select().Where("id = :id").Bind("id", 1).LockExclusive());
var rows1 = r1.FetchAll();
Assert.That(rows1, Has.One.Items);
Assert.AreEqual(1, rows1[0]["id"]);
// second session tries to read the locked row
using (Session s2 = MySQLX.GetSession(ConnectionString))
{
var t2 = s2.GetSchema(schemaName).GetTable(tableName);
ExecuteSQLStatement(s2.SQL("SET innodb_lock_wait_timeout = 1"));
s2.StartTransaction();
var stmt2 = t2.Select();
if (lockMode == LockMode.Exclusive)
{
stmt2.LockExclusive(lockOption);
}
else
{
stmt2.LockShared(lockOption);
}
switch (lockOption)
{
case LockContention.Default:
// error 1205 Lock wait timeout exceeded; try restarting transaction
Assert.AreEqual(1205u, Assert.Throws <MySqlException>(() => ExecuteSelectStatement(stmt2).FetchAll()).Code);
break;
case LockContention.NoWait:
// error 1205 Lock wait timeout exceeded; try restarting transaction
uint expectedError = 1205;
if (session.XSession.GetServerVersion().isAtLeast(8, 0, 5))
{
// error 3572 Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set
expectedError = 3572;
}
Assert.AreEqual(expectedError, Assert.Throws <MySqlException>(() => ExecuteSelectStatement(stmt2).FetchAll()).Code);
break;
case LockContention.SkipLocked:
if (!session.XSession.GetServerVersion().isAtLeast(8, 0, 5))
{
// error 1205 Lock wait timeout exceeded; try restarting transaction
Assert.AreEqual(1205u, Assert.Throws <MySqlException>(() => ExecuteSelectStatement(stmt2).FetchAll()).Code);
break;
}
var rows2 = ExecuteSelectStatement(stmt2).FetchAll();
Assert.That(rows2, Has.One.Items);
Assert.AreEqual(2, rows2[0]["id"]);
break;
default:
throw new NotImplementedException(lockOption.ToString());
}
}
// first session frees the lock
s1.Commit();
}
}
