/**
* Execute the SQL select for update that will lock the proper database row.
*/
protected override void ExecuteSQL(ConnectionAndTransactionHolder conn, string lockName, string expandedSQL)
{
try {
using (IDbCommand cmd = AdoUtil.PrepareCommand(conn, expandedSQL))
{
AdoUtil.AddCommandParameter(cmd, 1, "lockName", lockName);
using (IDataReader rs = cmd.ExecuteReader())
{
if (Log.IsDebugEnabled)
{
Log.Debug("Lock '" + lockName + "' is being obtained: " + Thread.CurrentThread.Name);
}
if (!rs.Read())
{
throw new Exception(AdoJobStoreUtil.ReplaceTablePrefix("No row exists in table " + TablePrefixSubst + TableLocks + " for lock named: " + lockName, TablePrefix));
}
}
}
} catch (Exception sqle) {
if (Log.IsDebugEnabled)
{
Log.Debug(
"Lock '" + lockName + "' was not obtained by: " +
Thread.CurrentThread.Name);
}
throw new LockException("Failure obtaining db row lock: "
+ sqle.Message, sqle);
}
}
/// <summary>
/// Execute the SQL that will lock the proper database row.
/// </summary>
/// <param name="conn"></param>
/// <param name="lockName"></param>
/// <param name="expandedSQL"></param>
/// <param name="expandedInsertSQL"></param>
protected override void ExecuteSQL(ConnectionAndTransactionHolder conn, string lockName, string expandedSQL, string expandedInsertSQL)
{
try
{
using (IDbCommand cmd = AdoUtil.PrepareCommand(conn, expandedSQL))
{
AdoUtil.AddCommandParameter(cmd, "lockName", lockName);
int numUpdate = cmd.ExecuteNonQuery();
if (numUpdate < 1)
{
using (IDbCommand cmd2 = AdoUtil.PrepareCommand(conn, expandedInsertSQL))
{
AdoUtil.AddCommandParameter(cmd2, "lockName", lockName);
int res = cmd2.ExecuteNonQuery();
if (res != 1)
{
throw new Exception(AdoJobStoreUtil.ReplaceTablePrefix(
"No row exists, and one could not be inserted in table " + TablePrefixSubst + TableLocks +
" for lock named: " + lockName, TablePrefix, SchedulerNameLiteral));
}
}
}
}
}
catch (Exception sqle)
{
throw new LockException(
"Failure obtaining db row lock: " + sqle.Message, sqle);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="DBSemaphore"/> class.
/// </summary>
/// <param name="tablePrefix">The table prefix.</param>
/// <param name="sql">The SQL.</param>
/// <param name="defaultSQL">The default SQL.</param>
/// <param name="dbProvider">The db provider.</param>
public DBSemaphore(string tablePrefix, string sql, string defaultSQL, IDbProvider dbProvider) {
log = LogManager.GetLogger(GetType());
this.sql = defaultSQL;
this.tablePrefix = tablePrefix;
SQL = sql;
adoUtil = new AdoUtil(dbProvider);
}
private async Task LockViaInsert(
Guid requestorId,
ConnectionAndTransactionHolder conn,
string lockName,
string sql,
CancellationToken cancellationToken)
{
if (sql == null)
{
throw new ArgumentNullException(nameof(sql));
}
if (Log.IsDebugEnabled())
{
Log.DebugFormat("Inserting new lock row for lock: '{0}' being obtained: {1}", lockName, requestorId);
}
using var cmd = AdoUtil.PrepareCommand(conn, sql);
AdoUtil.AddCommandParameter(cmd, "schedulerName", SchedName);
AdoUtil.AddCommandParameter(cmd, "lockName", lockName);
if (await cmd.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false) != 1)
{
throw new InvalidOperationException(
AdoJobStoreUtil.ReplaceTablePrefix("No row exists, and one could not be inserted in table " + TablePrefixSubst + TableLocks + " for lock named: " + lockName, TablePrefix));
}
}
/// <summary>
/// Initializes a new instance of the <see cref="DBSemaphore"/> class.
/// </summary>
/// <param name="tablePrefix">The table prefix.</param>
/// <param name="sql">The SQL.</param>
/// <param name="defaultSQL">The default SQL.</param>
/// <param name="dbProvider">The db provider.</param>
public DBSemaphore(string tablePrefix, string sql, string defaultSQL, IDbProvider dbProvider)
{
log = LogManager.GetLogger(GetType());
this.sql = defaultSQL;
this.tablePrefix = tablePrefix;
SQL = sql;
adoUtil = new AdoUtil(dbProvider);
}
/// <summary>
/// Initializes a new instance of the <see cref="DBSemaphore"/> class.
/// </summary>
/// <param name="tablePrefix">The table prefix.</param>
/// <param name="schedName">the scheduler name</param>
/// <param name="defaultInsertSQL">The SQL.</param>
/// <param name="defaultSQL">The default SQL.</param>
/// <param name="dbProvider">The db provider.</param>
public DBSemaphore(string tablePrefix, string schedName, string defaultSQL, string defaultInsertSQL, IDbProvider dbProvider)
{
this.schedName = schedName;
this.tablePrefix = tablePrefix;
SQL = defaultSQL;
InsertSQL = defaultInsertSQL;
adoUtil = new AdoUtil(dbProvider);
}
/// <summary>
/// Initializes a new instance of the <see cref="DBSemaphore"/> class.
/// </summary>
/// <param name="tablePrefix">The table prefix.</param>
/// <param name="schedName">the scheduler name</param>
/// <param name="defaultInsertSQL">The SQL.</param>
/// <param name="defaultSQL">The default SQL.</param>
/// <param name="dbProvider">The db provider.</param>
public DBSemaphore(string tablePrefix, string schedName, string defaultSQL, string defaultInsertSQL, IDbProvider dbProvider)
{
this.schedName = schedName;
this.tablePrefix = tablePrefix;
SQL = defaultSQL;
InsertSQL = defaultInsertSQL;
adoUtil = new AdoUtil(dbProvider);
}
/// <summary>
/// Initializes a new instance of the <see cref="DBSemaphore"/> class.
/// </summary>
/// <param name="tablePrefix">The table prefix.</param>
/// <param name="schedName">the scheduler name</param>
/// <param name="defaultInsertSQL">The SQL.</param>
/// <param name="defaultSQL">The default SQL.</param>
/// <param name="dbProvider">The db provider.</param>
public DBSemaphore(string tablePrefix, string schedName, string defaultSQL, string defaultInsertSQL, IDbProvider dbProvider)
{
log = LogManager.GetLogger(GetType());
this.schedName = schedName;
this.tablePrefix = tablePrefix;
SQL = defaultSQL;
InsertSQL = defaultInsertSQL;
adoUtil = new AdoUtil(dbProvider);
}
/// <summary>
/// Initializes a new instance of the <see cref="DBSemaphore"/> class.
/// </summary>
/// <param name="tablePrefix">The table prefix.</param>
/// <param name="defaultInsertSQL">The SQL.</param>
/// <param name="defaultSQL">The default SQL.</param>
/// <param name="dbProvider">The db provider.</param>
public DBSemaphore(string tablePrefix, string schedName, string defaultSQL, string defaultInsertSQL, IDbProvider dbProvider)
{
log = LogManager.GetLogger(GetType());
this.schedName = schedName;
this.tablePrefix = tablePrefix;
SQL = defaultSQL;
InsertSQL = defaultInsertSQL;
adoUtil = new AdoUtil(dbProvider);
}
/// <summary>
/// Initializes a new instance of the <see cref="DBSemaphore"/> class.
/// </summary>
/// <param name="tablePrefix">The table prefix.</param>
/// <param name="schedName">the scheduler name</param>
/// <param name="defaultInsertSQL">The SQL.</param>
/// <param name="defaultSQL">The default SQL.</param>
/// <param name="dbProvider">The db provider.</param>
protected DBSemaphore(string tablePrefix, string schedName, string defaultSQL, string defaultInsertSQL, IDbProvider dbProvider)
{
log = NLog.LogManager.GetCurrentClassLogger();
this.schedName = schedName;
this.tablePrefix = tablePrefix;
SQL = defaultSQL;
InsertSQL = defaultInsertSQL;
adoUtil = new AdoUtil(dbProvider);
}
private bool LockViaUpdate(ConnectionAndTransactionHolder conn, string lockName, string sql)
{
using (IDbCommand cmd = AdoUtil.PrepareCommand(conn, sql))
{
AdoUtil.AddCommandParameter(cmd, "lockName", lockName);
Log.DebugFormat("Lock '{0}' is being obtained: {1}", lockName, Thread.CurrentThread.Name);
return(cmd.ExecuteNonQuery() >= 1);
}
}
private async Task <bool> LockViaUpdate(Guid requestorId, ConnectionAndTransactionHolder conn, string lockName, string sql)
{
using (DbCommand cmd = AdoUtil.PrepareCommand(conn, sql))
{
AdoUtil.AddCommandParameter(cmd, "lockName", lockName);
Log.DebugFormat("Lock '{0}' is being obtained: {1}", lockName, requestorId);
return(await cmd.ExecuteNonQueryAsync().ConfigureAwait(false) >= 1);
}
}
/// <summary>
/// Execute the SQL select for update that will lock the proper database row.
/// </summary>
protected override void ExecuteSQL(ConnectionAndTransactionHolder conn, string lockName, string expandedSQL, string expandedInsertSQL)
{
try
{
using (IDbCommand cmd = AdoUtil.PrepareCommand(conn, expandedSQL))
{
AdoUtil.AddCommandParameter(cmd, "lockName", lockName);
bool found = false;
using (IDataReader rs = cmd.ExecuteReader())
{
if (Log.IsDebugEnabled)
{
Log.Debug("Lock '" + lockName + "' is being obtained: " + Thread.CurrentThread.Name);
}
found = rs.Read();
}
if (!found)
{
Log.Debug(
"Inserting new lock row for lock: '" + lockName + "' being obtained by thread: " +
Thread.CurrentThread.Name);
using (IDbCommand cmd2 = AdoUtil.PrepareCommand(conn, expandedInsertSQL))
{
AdoUtil.AddCommandParameter(cmd2, "lockName", lockName);
int res = cmd2.ExecuteNonQuery();
if (res != 1)
{
throw new Exception(AdoJobStoreUtil.ReplaceTablePrefix(
"No row exists, and one could not be inserted in table " + TablePrefixSubst + TableLocks +
" for lock named: " + lockName, TablePrefix, SchedulerNameLiteral));
}
}
}
}
}
catch (Exception sqle)
{
if (Log.IsDebugEnabled)
{
Log.Debug(
"Lock '" + lockName + "' was not obtained by: " +
Thread.CurrentThread.Name);
}
throw new LockException("Failure obtaining db row lock: "
+ sqle.Message, sqle);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="DBSemaphore"/> class.
/// </summary>
/// <param name="tablePrefix">The table prefix.</param>
/// <param name="schedName">the scheduler name</param>
/// <param name="defaultInsertSQL">The SQL.</param>
/// <param name="defaultSQL">The default SQL.</param>
/// <param name="dbProvider">The db provider.</param>
protected DBSemaphore(
string tablePrefix,
string?schedName,
string defaultSQL,
string defaultInsertSQL,
IDbProvider dbProvider)
{
Log = LogProvider.GetLogger(GetType());
this.schedName = schedName;
this.tablePrefix = tablePrefix;
SQL = defaultSQL;
InsertSQL = defaultInsertSQL;
AdoUtil = new AdoUtil(dbProvider);
}
private void LockViaInsert(ConnectionAndTransactionHolder conn, String lockName, String sql)
{
Log.DebugFormat("Inserting new lock row for lock: '{0}' being obtained by thread: {1}", lockName, Thread.CurrentThread.Name);
using (IDbCommand cmd = AdoUtil.PrepareCommand(conn, sql))
{
AdoUtil.AddCommandParameter(cmd, "lockName", lockName);
if (cmd.ExecuteNonQuery() != 1)
{
throw new DataException(
AdoJobStoreUtil.ReplaceTablePrefix("No row exists, and one could not be inserted in table " + TablePrefixSubst + TableLocks + " for lock named: " + lockName, TablePrefix, SchedulerNameLiteral));
}
}
}
private async Task <bool> LockViaUpdate(
Guid requestorId,
ConnectionAndTransactionHolder conn,
string lockName,
string sql,
CancellationToken cancellationToken)
{
using DbCommand cmd = AdoUtil.PrepareCommand(conn, sql);
AdoUtil.AddCommandParameter(cmd, "schedulerName", SchedName);
AdoUtil.AddCommandParameter(cmd, "lockName", lockName);
if (Log.IsDebugEnabled())
{
Log.DebugFormat("Lock '{0}' is being obtained: {1}", lockName, requestorId);
}
return(await cmd.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false) >= 1);
}
private async Task LockViaInsert(ConnectionAndTransactionHolder conn, string lockName, string sql)
{
if (sql == null)
{
throw new ArgumentNullException(nameof(sql));
}
Log.DebugFormat("Inserting new lock row for lock: '{0}' being obtained by thread: {1}", lockName, Thread.CurrentThread.Name);
using (var cmd = AdoUtil.PrepareCommand(conn, sql))
{
AdoUtil.AddCommandParameter(cmd, "lockName", lockName);
if (await cmd.ExecuteNonQueryAsync().ConfigureAwait(false) != 1)
{
throw new InvalidOperationException(
AdoJobStoreUtil.ReplaceTablePrefix("No row exists, and one could not be inserted in table " + TablePrefixSubst + TableLocks + " for lock named: " + lockName, TablePrefix, SchedulerNameLiteral));
}
}
}
/// <summary>
/// Execute the SQL that will lock the proper database row.
/// </summary>
/// <param name="conn"></param>
/// <param name="lockName"></param>
/// <param name="expandedSQL"></param>
/// <param name="expandedInsertSQL"></param>
protected override void ExecuteSQL(ConnectionAndTransactionHolder conn, string lockName, string expandedSQL, string expandedInsertSQL)
{
// attempt lock two times (to work-around possible race conditions in inserting the lock row the first time running)
int count = 0;
do
{
count++;
try
{
using (IDbCommand cmd = AdoUtil.PrepareCommand(conn, expandedSQL))
{
AdoUtil.AddCommandParameter(cmd, "lockName", lockName);
if (Log.IsDebugEnabled)
{
Log.DebugFormat("Lock '{0}' is being obtained: {1}", lockName, Thread.CurrentThread.Name);
}
int numUpdate = cmd.ExecuteNonQuery();
if (numUpdate < 1)
{
if (Log.IsDebugEnabled)
{
Log.DebugFormat("Inserting new lock row for lock: '{0}' being obtained by thread: {1}", lockName, Thread.CurrentThread.Name);
}
using (IDbCommand cmd2 = AdoUtil.PrepareCommand(conn, expandedInsertSQL))
{
AdoUtil.AddCommandParameter(cmd2, "lockName", lockName);
int res = cmd2.ExecuteNonQuery();
if (res != 1)
{
if (count < 3)
{
// pause a bit to give another thread some time to commit the insert of the new lock row
try
{
Thread.Sleep(TimeSpan.FromSeconds(1));
}
catch (ThreadInterruptedException)
{
Thread.CurrentThread.Interrupt();
}
// try again ...
continue;
}
throw new Exception(AdoJobStoreUtil.ReplaceTablePrefix(
"No row exists, and one could not be inserted in table " + TablePrefixSubst + TableLocks +
" for lock named: " + lockName, TablePrefix, SchedulerNameLiteral));
}
}
break; // obtained lock, no need to retry
}
}
}
catch (Exception sqle)
{
if (Log.IsDebugEnabled)
{
Log.DebugFormat("Lock '{0}' was not obtained by: {1}{2}", lockName, Thread.CurrentThread.Name, (count < 3 ? " - will try again." : ""));
}
if (count < 3)
{
// pause a bit to give another thread some time to commit the insert of the new lock row
try
{
Thread.Sleep(TimeSpan.FromSeconds(1));
}
catch (ThreadInterruptedException)
{
Thread.CurrentThread.Interrupt();
}
// try again ...
continue;
}
throw new LockException("Failure obtaining db row lock: " + sqle.Message, sqle);
}
} while (count < 2);
}
/// <summary>
/// Execute the SQL select for update that will lock the proper database row.
/// </summary>
protected override async Task ExecuteSQL(
Guid requestorId,
ConnectionAndTransactionHolder conn,
string lockName,
string expandedSql,
string expandedInsertSql,
CancellationToken cancellationToken)
{
Exception initCause = null;
// attempt lock two times (to work-around possible race conditions in inserting the lock row the first time running)
int count = 0;
// Configurable lock retry attempts
var maxRetryLocal = MaxRetry;
var retryPeriodLocal = RetryPeriod;
do
{
count++;
try
{
using (DbCommand cmd = AdoUtil.PrepareCommand(conn, expandedSql))
{
AdoUtil.AddCommandParameter(cmd, "schedulerName", SchedName);
AdoUtil.AddCommandParameter(cmd, "lockName", lockName);
bool found;
using (var rs = await cmd.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false))
{
if (Log.IsDebugEnabled())
{
Log.DebugFormat("Lock '{0}' is being obtained: {1}", lockName, requestorId);
}
found = await rs.ReadAsync(cancellationToken).ConfigureAwait(false);
}
if (!found)
{
if (Log.IsDebugEnabled())
{
Log.DebugFormat("Inserting new lock row for lock: '{0}' being obtained by thread: {1}", lockName, requestorId);
}
using (DbCommand cmd2 = AdoUtil.PrepareCommand(conn, expandedInsertSql))
{
AdoUtil.AddCommandParameter(cmd2, "schedulerName", SchedName);
AdoUtil.AddCommandParameter(cmd2, "lockName", lockName);
int res = await cmd2.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
if (res != 1)
{
if (count < maxRetryLocal)
{
// pause a bit to give another thread some time to commit the insert of the new lock row
await Task.Delay(retryPeriodLocal, cancellationToken).ConfigureAwait(false);
// try again ...
continue;
}
throw new Exception(AdoJobStoreUtil.ReplaceTablePrefix(
"No row exists, and one could not be inserted in table " + TablePrefixSubst + TableLocks +
" for lock named: " + lockName, TablePrefix));
}
}
}
}
// obtained lock, go
return;
}
catch (Exception sqle)
{
if (initCause == null)
{
initCause = sqle;
}
if (Log.IsDebugEnabled())
{
Log.DebugFormat("Lock '{0}' was not obtained by: {1}{2}", lockName, requestorId, count < maxRetryLocal ? " - will try again." : "");
}
if (count < maxRetryLocal)
{
// pause a bit to give another thread some time to commit the insert of the new lock row
await Task.Delay(retryPeriodLocal, cancellationToken).ConfigureAwait(false);
// try again ...
continue;
}
throw new LockException("Failure obtaining db row lock: " + sqle.Message, sqle);
}
} while (count < maxRetryLocal + 1);
throw new LockException("Failure obtaining db row lock, reached maximum number of attempts. Initial exception (if any) attached as root cause.", initCause);
}
public void Initialize(string tablePrefix, string schedName, AdoUtil adoUtil)
{
this.tablePrefix = tablePrefix;
schedNameLiteral = "'" + schedName + "'";
this.adoUtil = adoUtil;
}
/// <summary>
/// Create new StdAdoDelegate instance.
/// </summary>
/// <param name="logger">the logger to use during execution</param>
/// <param name="tablePrefix">the prefix of all table names</param>
/// <param name="instanceId">The instance id.</param>
/// <param name="dbProvider">The db provider.</param>
/// <param name="useProperties">if set to <c>true</c> [use properties].</param>
public StdAdoDelegate(ILog logger, string tablePrefix, string instanceId, IDbProvider dbProvider,
bool useProperties)
{
this.logger = logger;
this.tablePrefix = tablePrefix;
this.instanceId = instanceId;
this.dbProvider = dbProvider;
adoUtil = new AdoUtil(dbProvider);
this.useProperties = useProperties;
}
public void Initialize(string tablePrefix, string schedName, AdoUtil adoUtil)
{
this.tablePrefix = tablePrefix;
this.schedNameLiteral = "'" + schedName + "'";
this.adoUtil = adoUtil;
}
