如何锁定表以进行读写，以便可以执行SQL，然后再删除锁? [英] How can I lock a table for read and write so I can execute SQL and then remove the locks afterward?

问题描述

我现在才开始研究Teradata的锁定功能，而Google对此颇为困惑.希望我能从SE中得到一个非常简单且精简的答案.

I am just now starting to dig into Teradata's locking features and Google is fairly convoluted with explanations on this. Hopefully, I can get a very simple and streamlined answer from SE.

在Teradata中遇到很多有关身份列的问题之后，我决定创建一种模仿Oracle序列的机制.为此，我正在创建一个包含两个字段的表，一个字段保存一个表名，另一个字段存储其最后使用的序列.然后，我将创建一个带有表名的存储过程.在该过程中，它将执行以下选项:

After encountering numerous issues with identity columns in Teradata, I've decided to create a mechanism that mimics Oracle's sequence. To do this, I am creating a table with two fields, one that holds a table name and the other that stores its last-used sequence. I am going to then create a stored procedure that takes a table name. Within the procedure, it will perform the following options:

• 从序列表中选择最后使用的序列到变量(select LastId from mydb.sequence where tablename = :tablename)
• 在变量的值上加1，从而使其递增
• 更新序列表以使用增量值
• 将序列变量返回到过程的OUT参数，以便我可以在我的.NET应用程序中访问已排序的ID

• Select the last-used sequence from the sequence table into a variable (select LastId from mydb.sequence where tablename = :tablename)
• Add 1 to the variable's value, thus incrementing it
• Update the sequence table to use the incremented value
• Return the sequence variable to the procedure's OUT param so I can access the sequenced ID in my .NET app

所有这些操作都在进行时，我需要为所有读取和写入访问锁定序列表，以确保对该过程的其他调用不会在当前正在对其进行排序的过程中尝试对表进行排序. .显然，这是为了防止同一序列在同一张表中被重复使用两次.

While all of those operations are taking place, I need to lock the sequence table for all read and write access to ensure that other calls to the procedure do not attempt to sequence the table while it is currently in the process of being sequenced. This is obviously to keep the same sequence from being used twice for the same table.

如果这是.NET，我将使用Sync Lock(VB.NET)或lock(C#)来防止其他线程输入代码块，直到当前线程完成为止.我想知道是否有一种方法可以像锁定.NET中的线程一样锁定表.

If this were .NET, I'd use a Sync Lock (VB.NET) or lock (C#) to keep other threads from entering a block of code until the current thread was finished. I am wondering if there's a way to lock a table much in the same way that I would lock a thread in .NET.

推荐答案

考虑对事务的行哈希锁使用显式锁定机制:

Consider using an explicit locking mechanism for a rowhash lock for the transaction:

BEGIN TRANSACTION;

LOCKING ROW EXCLUSIVE
SELECT LastId + 1 INTO :LastID
FROM MyDB.SequenceCols
WHERE TableName = :TableName
  AND DatabaseName = :DatabaseName;

UPDATE MyDB.SequenceCols
SET LastId = :LastID
WHERE TableName = :TableName
  AND DatabaseName = :DatabaseName;

END TRANSACTION;

rowhash锁将允许该过程被其他进程针对其他表使用.为了确保行级锁定，您必须完全限定SequenceCols表的主索引.实际上，SequenceCols表的主索引在DatabaseName和TableName上应该是UNIQUE.

The rowhash lock will allow the procedure to be used by other processes against other tables. To ensure row level locking you must fully qualify the primary index of the SequenceCols table. In fact, the primary index of the SequenceCols table should be UNIQUE on DatabaseName and TableName.

排它行锁将阻止另一个进程读取行，直到处理完该行锁的所有者END TRANSACTION为止.

The exclusive rowhash lock would prevent another process from reading the row until the END TRANSACTION is processed owner of the rowhash lock.

这篇关于如何锁定表以进行读写，以便可以执行SQL，然后再删除锁?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！