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.
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 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.
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.