Home How can I lock a table for read and write so I can execute SQL and then remove the locks afterward?
Reply: 0

How can I lock a table for read and write so I can execute SQL and then remove the locks afterward?

user1659 Published in June 23, 2018, 5:32 pm

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.312482 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO