长时间独占表

 

     应用程序操作数据库表时，需要长时间（可能要几分钟，甚至十来分钟）修改数据表的数据，而在整个过程中，不容许别的用户操作数据（即只能单用户操作数据），这种情况，相当于要把表锁住（跟数据库概念的锁不一样），等执行完后，解锁，别的用户才可以操作数据。就这种问题，有以下几种种解决方案。

      1.设置标记字段（Flag）

             在操作前设置标记为正在操作，执行完成后标记复原。执行开始时，检查这个标记字段是正在操作状态还是未操作状态，从而决定是否继续执行当前操作。

           缺点是，如果在执行过程中，客户端和数据库服务器的连接断开，则标记字段被标记为正在操作状态，此时，需要人工到后台数据库修改字段到未操作状态，其他客户程序才能执行。可能会想到，打标记和复位标记用事物控制，带来的问题是事务中间的得到的数据在发生物理错误时也丢失了。

  2. 在应用程序中，建两个数据连接的对象。

      一个（pubConn）用来连接数据、操作数据表，另一个(PubConnLock)用来控制单用户操作（锁表）。

2.1方法A 建一张上锁用的空表

   如建一张名Test的表，只有ID一个字段。

       pubConnLock.BeginTrans '开启事务
        strSql = "select  * from Test with (tablockx)"
        pubConnLock.Execute (strSql)
        '''''''
            '长时间操作表的模块，操作数据用的数据库连接对象是PubConn不能是pubConnLock。

       ''''''''
         pubConnLock.CommitTrans

   查询语句 “select  * from Test with (tablockx)”执行时会在系统表dm_tran_locks 中生成一条request_mode='X'"的记录行，下面是查锁的语句

  SqlStr = "SELECT * from sys.dm_tran_locks where resource_associated_entity_id=object_ID('dbo.test') and request_mode='X'"

  在每次执行长时间操作表的模块之前，检查该锁是否存在，存在则不执行，不存在时才能执行，这样就能达到单用户操作的目的了。

2.2 方法B 记录每个执行过程在一个表中

   建一张表Test，只有一个字段（OperateName）,保存应用程序操作数据过程的字段。

  pubConnLock.BeginTrans '开启事务
        strSql = "select  * from Test with (UPDLOCK,READPAST) where OperateName='FunctionModule1'" '功能模块1

        rs.Open sql, pubConnLock, 1, 1
        If rs.RecordCount > 0 Then
          '''''''
            '长时间操作表的模块，操作数据用的数据库连接对象是PubConn不能是pubConnLock。

          ''''''''
        End If
   pubConnLock.CommitTrans

with (UPDLOCK,READPAST)，UPDLOCK:读取表时使用更新锁，而不使用共享锁，并将锁一直保留到语句或事务的结束。UPDLOCK 的优点是允许您读取数据（不阻塞其它事务）并在以后更新数据，同时确保自从上次读取数据后数据没有被更改。READPAST: 会把被锁住的行直接过滤。因此，select  * from Test with  (UPDLOCK,READPAST) 就是把未被锁住的行读出来并把这些行加锁，也就是在当前这个进程中执行的事务获取的记录行，在别的进程中查询不到。