1

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.

Brian Tompsett - 汤莱恩
  • 5,195
  • 62
  • 50
  • 120
oscilatingcretin
  • 9,495
  • 31
  • 111
  • 188

1 Answers1

1

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;

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.

EDIT:

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

Rob Paller
  • 7,506
  • 23
  • 23
  • What happens if this procedure is called twice at the same time and both calls end up selecting the same ID before either of them get to the update? – oscilatingcretin Jul 31 '12 at 17:30
  • 1
    see edit for additional explanation of the exclusive rowhash lock. – Rob Paller Jul 31 '12 at 17:47
  • So I got it all working. Is there a way to test if this locking is working? I naturally thought of finding a way to have the sproc wait so that I have enough time to send another request through, but research shows that only UDFs are capable of this. – oscilatingcretin Aug 01 '12 at 12:51
  • 1
    You can test the logic outside the stored procedure using BTEQ and using a .OS command to call a script that sleeps for a period of time before the END TRANSACTION command is submitted. This would allow you to call a second process that attempts to assign a new identity value for the same table using the stored procedure which is held by the sleep process. – Rob Paller Aug 01 '12 at 13:16
  • 1
    Alright, you've earned your paycheck, so I'll mark your answer! – oscilatingcretin Aug 03 '12 at 18:06
  • Or you could just issue a SELECT * FROM [some huge table] before the END TRANSACTION as a waiting mechanism... – C.B. May 23 '13 at 11:25