如何使用SqlClient在ADO.NET/C#4.0中解锁SQL Server 2008表

Question

我在返回时创建了一个方法：

1. 锁定了一张桌子
2. 从中读取值
3. 将更新后的值写回
4. 解锁桌子

该代码适用于Oracle。现在我无法让它在SQL Server 2008上运行。方法如下，执行我的解锁命令会产生一个带有文本的SqlException：

"NOLC"不是可识别的表提示选项。如果它打算作为表值函数或CHANGETABLE函数的参数，确保数据库兼容性模式设置为90。

代码：

public static int GetAndSetMaxIdTable(DbProviderFactory factory, DbConnection cnctn, DbTransaction txn, int tableId, string userName, int numberOfIds)
{
bool isLocked = false;
string sql = string.Empty;
string maxIdTableName;
if (tableId == 0)
maxIdTableName = "IdMax";
else
maxIdTableName = "IdMaxTable";
try
{
bool noPrevRow = false;
int realMaxId;
if (factory is OracleClientFactory)
sql = string.Format("lock table {0} in exclusive mode", maxIdTableName);
else if (factory is SqlClientFactory)
sql = string.Format("select * from {0} with (TABLOCKX)", maxIdTableName);
else
throw new Exception(string.Format("Unsupported DbProviderFactory -type: {0}", factory.GetType().ToString()));
using (DbCommand lockCmd = cnctn.CreateCommand())
{
lockCmd.CommandText = sql;
lockCmd.Transaction = txn;
lockCmd.ExecuteNonQuery();
isLocked = true;
}
using (DbCommand getCmd = cnctn.CreateCommand())
{
getCmd.CommandText = CreateSelectCommand(factory, tableId, userName, getCmd, txn);
object o = getCmd.ExecuteScalar();
if (o == null)
{
noPrevRow = true;
realMaxId = 0;
}
else
{
realMaxId = Convert.ToInt32(o);
}
}
using (DbCommand setCmd = cnctn.CreateCommand())
{
if (noPrevRow)
setCmd.CommandText = CreateInsertCommand(factory, tableId, userName, numberOfIds, realMaxId, setCmd, txn);
else
setCmd.CommandText = CreateUpdateCommand(factory, tableId, userName, numberOfIds, realMaxId, setCmd, txn);
setCmd.ExecuteNonQuery();
}
if (factory is OracleClientFactory)
sql = string.Format("lock table {0} in share mode", maxIdTableName);
else if (factory is SqlClientFactory)
sql = string.Format("select * from {0} with (NOLOC)", maxIdTableName);             
using (DbCommand lockCmd = cnctn.CreateCommand())
{
lockCmd.CommandText = sql;
lockCmd.Transaction = txn;
lockCmd.ExecuteNonQuery();
isLocked = false;
}
return realMaxId;
}
catch (Exception e)
{
...
}
}

那么这里出了什么问题？这个错误是从哪里来的？服务器还是客户端？我从C代码中复制了这个语句，它应该在那里工作。不幸的是，我无法调试和检查它是否适合我。

编辑：只是尝试锁定和解锁(不读取或更新)会导致相同的异常。

谢谢&BR-Matti

Answer 1

TABLOCKX提示根据您的意愿锁定表，但您无法手动解锁它。锁定持续的时间取决于您的事务级别。如果您的连接上没有活动事务，那么在执行SELECT时会持有锁，此后会丢弃锁。

如果你想实现"锁定表->对表做点什么->释放锁"的顺序，你需要实现这个T-SQL脚本的ADO.NET等价物：

BEGIN TRAN
SELECT TOP (1) 1 FROM myTable (TABLOCKX, KEEPLOCK)
-- do something with the table
COMMIT -- This will release the lock, if there is no outer transaction present

您可以通过DbCommand对象执行"BEGIN TRAN"/"COMMIT"，也可以使用System.Data.SqlClient.SqlTransaction类启动并提交事务。

注意：只有当您的连接尚未登记在事务中时，这种方法才有效！SQL Server不支持嵌套事务，因此COMMIT不会执行任何操作，并且会持有锁。如果您有一个事务已经在运行，则在事务完成之前无法释放锁。在这种情况下，通过sp_getapplock/sp_releaseaplock进行同步可能会有所帮助。

编辑：如果你想自学交易、锁定和阻止，我推荐这两个视频：http://technet.microsoft.com/en-us/sqlserver/gg545007.aspx和http://technet.microsoft.com/en-us/sqlserver/gg508892.aspx

Answer 2

以下是SqlClient的一个表的答案，我根据TToni的答案编写了代码：

public static int GetAndSetMaxIdTable(DbProviderFactory factory, DbConnection cnctn,   DbTransaction txn, int numberOfIds)
{
bool noPrevRow = false;
int realMaxId;

using (DbCommand getCmd = cnctn.CreateCommand())
{
getCmd.CommandText = "SELECT MaxId FROM IdMax WITH (TABLOCKX)"
getCmd.Transaction = txn;
object o = getCmd.ExecuteScalar();
if (o == null)
{
noPrevRow = true;
realMaxId = 0;
}
else
{
realMaxId = Convert.ToInt32(o);
}
}
using (DbCommand setCmd = cnctn.CreateCommand())
{
if (noPrevRow)
setCmd.CommandText = CreateInsertCommand(factory, tableId, userName, numberOfIds, realMaxId, setCmd, txn);
else
setCmd.CommandText = CreateUpdateCommand(factory, tableId, userName, numberOfIds, realMaxId, setCmd, txn);
setCmd.ExecuteNonQuery();
}
return realMaxId;
}

我是这样的：

...
try
{
using (txn = cnctn.BeginTransaction())
{
oldMaxId = GetAndSetMaxIdTable(factory, cnctn, txn, 5);
for (i = 0; i < 5; i++)
{
UseNewIdToInsertStuff(factory, cnctn, txn, oldMaxId + i + 1)
}
txn.Commit();
return true;
}
}
catch (Exception e)
{
// don't know if this is needed
if (txn != null && cnctn.State == ConnectionState.Open)
txn.Rollback();
throw e;
}
...

对于oracle客户端来说，它似乎是可取的：

SELECT MaxId from IdMax WHERE ... FOR UPDATE OF MaxId

-m