在编辑 SQL Server 记录数据时采用什么模式来锁定该记录
本文关键字:记录 模式 什么 锁定 SQL 编辑 Server 数据 | 更新日期: 2023-09-27 18:32:45
我在开发打开连接、开始事务和提交/回滚/关闭连接的正确策略时遇到问题。上下文是一个 ASP.NET 的WebForms应用程序。客户端可以调出记录进行编辑,我希望其他客户端在编辑操作期间无法更新该记录。
其他客户端应该能够读取该记录的上次提交版本。我现在用于编辑操作的方法是打开连接并在 IsolationLevel.RepeatableRead
开始事务,这在锁定方面做了我想要的事情。
但是,我不会立即关闭连接...相反,当客户端主动编辑记录中的值时,我保持连接和事务打开,以便我保持锁定。编辑完成后,客户端提交或回滚更改,然后关闭连接。
下面是表示数据库中记录的类的骨骼:
public class DBRecord : IDisposable
{
private OleDbTransaction tran; // holds the open transaction
private Dictionary<string, object> values = new Dictionary<string, object>();
private bool disposedValue = false;
public DBRecord (bool forUpdate) {
OleDbConnection conn = new OleDbConnection(<connection string>);
try {
conn.Open();
tran = conn.BeginTransaction (forUpdate ? IsolationLevel.RepeatableRead : IsolationLevel.ReadCommitted);
OleDbCommand comm = new OleDbCommand("SET XACT_ABORT ON", conn, tran);
comm.ExecuteNonQuery();
comm = new OleDbCommand("SELECT * FROM " + DBTable + " WHERE KEY = ?", conn, tran);
comm.Parameters.Add(new OleDbParameter("@Key", Key));
using (OleDbDataReader rdr = comm.ExecuteReader())
{
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; i++)
{
mvoValues.Add(rdr.GetName(i), rdr.GetValue(i));
}
}
}
} catch {
conn.Close();
throw;
}
if (!forUpdate) {
// don't need to keep the record locked
tran.Commit();
conn.Close();
}
}
public UpdateField(string field, object newValue) {
// this is only called if the object was instantiated with forUpdate true
OleDbCommand comm = new OleDbCommand("UPDATE " + DBTable + " SET " + field + " = ? WHERE " + KeyField + " = ?", tran.Connection, tran);
comm.Parameters.Add(new OleDbParameter("@Value", newValue));
comm.Parameters.Add(new OleDbParameter("@Key", Key));
try {
oCommand.ExecuteNonQuery();
} catch {
OleDbConnection conn = tran.Connection;
tran.Rollback();
conn.Close();
}
}
public void Commit()
{
OleDbConnection conn = tran.Connection;
tran.Commit();
conn.Close();
}
public void Rollback()
{
OleDbConnection conn = tran.Connection;
tran.Rollback();
conn.Close();
}
protected virtual void Dispose(bool disposing)
{
if (!disposedValue)
{
if (disposing)
{
if ((tran != null) && (tran.Connection != null))
{
try
{
OleDbConnection conn = tran.Connection;
/// release rowlocks we acquired at instantiation
tran.Rollback();
conn.Close();
}
catch (Exception)
{
// since we're disposing of the object at this point, there's not much
// we can do about a rollback failure, so silently ignore exceptions
}
}
}
disposedValue = true;
}
}
}
数据库的"允许快照隔离"和"读取提交快照"设置为 true,并且连接字符串指定"已启用 MARS 为 true"。
我很清楚这不是正确的方法:
它违背了我读到的关于连接的所有内容——尽可能晚地打开,尽可能快地关闭。 但是,我不知道在客户端进行编辑时保持记录锁定更新的另一种方法。
我遇到的更大的问题是当我在编辑过程中终止浏览器时。现在我正在开发中,所以我正在使用VS 2015和IIS Express。当我关闭浏览器时,IIS Express 进程也会关闭。似乎从未调用过我的
Dispose()
,因为这些进程在我的 SQL Server Express 实例中保持打开状态。显然,无论服务器是否停止,我需要以某种方式保证连接都会关闭。
在之前与数据库相关的问题中,我收到了一个建议,即在本地存储中缓存记录数据的副本,跟踪更新,然后在准备好提交更改时执行新事务。这肯定会使我的连接更短,更新操作更具原子性,但记录不会被锁定。解决方案可能是创建一个"我正在更新"字段,客户端可以在编辑操作开始时对其进行测试和设置,但这似乎是一种黑客攻击,特别是考虑到数据库引擎提供了锁定机制。
直觉上,我在这里尝试做的事情似乎是一个足够常见的用例,应该已经有一个模式,但我显然没有问谷歌正确的问题,因为我在搜索中得到的只是如何使用using
块。谁能指出我正确的方法?谢谢。
模式名称是悲观的离线锁定,用于您要查找的内容,https://dzone.com/articles/practical-php-patterns/practical-php-patterns-13,这就是您所指的具有"我正在更新"的列。
您可以查看锁定的其他解决方案,https://petermeinl.wordpress.com/2011/03/14/db-concurrency-control-patterns-for-applications/。
出于多种原因,通常建议您使用乐观锁定而不是悲观锁定。它不需要您在编辑之前更新行,不需要像某些悲观的锁定解决方案那样保持连接打开状态,并且不需要实现锁定超时。但是,它确实具有副作用,即当用户保存更改时,系统可能会提示他们是否要覆盖或合并自开始编辑以来发生的更改。
这些与SQL Server内置的锁定并不完全相同,尽管SQL Server的某些功能可用于帮助实现它们。