How to ensure atomic nature of multiple table actions?

Database
sdc
Enthusiast

How to ensure atomic nature of multiple table actions?

I am working on an application which generates a result that I want to store in a Teradata database.  The database consists of several tables and the application's result will span multiple tables.  I want to ensure either (1) a whole result is written or (2) nothing is written.  In other words, I want the result to be atomic.

I'll call the storing of the result the "operation."  The operation needs to involve checking to see if constituent elements of the result already exist in the database.  If an element already exists I want to re-use it by finding its key.  If the element does NOT already exist, I want to create it and get its key.  In my application code, I want this logic to be handled by separate objects' methods.  I think the objects would be executing their own logic via SQL, but under the context of some type of control (transaction?) which ensures that the whole result is atomic.

Multiple clients will be trying to do this operation at the same time.  Also, another application will be using the tables at the same time, but not doing any writing.

My question is: What is the preferred way of handling these requirements in Teradata?

I've looked into some of the following concepts, but it is not clear to me what the tradeoffs are between them.  Here's the concepts and my problem with implementing them:

  • Multi statement requests

    Seems like the whole operation must be in one SQL statement, but I want the operation to be carried out by separate methods in my app code.
  • Using "Begin transaction" and "end transaction"

    Can a transaction be started in one statement (made by one object's method in my app code) and ended in another?  Maybe yes if using a shared cursor?
  • Waiting to commit until the whole operation is done

    I'm worried that multiple clients' operations would deadlock if all the tables required to write a result are not ensured to be available at the start of trying the operation.

It seems like I need something that will tie the whole operation together wait until the minimum locks it needs to write the result are all available and then do the whole thing.  I'm leaning towards using "Begin Transaction" and "End Transaction", but I'm not sure.  Hopefully someone can give me some ideas to consider.

7 REPLIES
sdc
Enthusiast

Re: How to ensure atomic nature of multiple table actions?

31 views and no replies.  Can I ask the question in a better way?  Is something unclear?  Too long of a post?  I can do something with incomplete answers if people don't want to address the whole question.

Teradata Employee

Re: How to ensure atomic nature of multiple table actions?

Let me take a stab at providing the answers. 

Atomicity isn't unique to Teradata, all major databases support "Transactions", which are atomic (https://en.wikipedia.org/wiki/ACID). It's database 101. 

As far as locking is concerned, many DBMS acquire locks dynamically as they process rows from a request; however, Teradata's locking is determined at the begining of the request before the actual write operations. In other words, Teradata will lock the entire table if the write operation doesn't utilize primary index to locate the target row. If you want to increase concurrency, then make sure table level locks are kept to minimum.

Also, if your's is an interactive application, make sure you don't start a database transaction until after user has finalized the operation. For example, don't perform a database update, when user checks a checkbox, but wait until she clicks OK or Apply then begin a transaction, perform all modificatiions and end the transaction before waiting for user response again.

Databases typically have no knowledge of application anatomy such as different methods or objects etc. When a typical Teradata application connects, it opens a session. Multiple writes will not lock each other when issued in the same session. 

Deadlocks between different sessions can happen irrespective of if the locks are at table level or row (actually rowhash) level. One simplest way to avoid deadlock is lock the tables in the same order. For example, if one transaction requires writes to table a,b,c,d, and other requires writes to tables d & b only, and the first transactions modified table a,b,c,d in that order, then second transaction should modify (or at least lock for write) b first and then d.

Mutli-statement requests enable special optimizations when Teradata has advance knowledge of the entire transaction -- for example, delete followed by a commit. From what you described, it doesn't appear that you'll greatly benefit from this feature.

sdc
Enthusiast

Re: How to ensure atomic nature of multiple table actions?

Padhia, thanks very much for your response.  That is certainly helpful.

One question that your response brought to mind is this: Is it possible to do a single transaction with multiple SQL queries?  Like:

"BEGIN TRANSACTION"

"<do some stuff>"

"<do some other stuff>"

"<do more stuff>"

"END TRANSACTION"

...where each line is a separate query?  That is what I really desire for my object-oriented application, for multiple statements to be added up in a transaction and then executed at once.

sdc
Enthusiast

Re: How to ensure atomic nature of multiple table actions?

I did some testing and found that in my example above, each line would individually acquire a lock instead of waiting for the "END TRANSACTION" to do it all at once.  So, even if it is working as a single transaction, there is still a possibility for deadlock here because it does not wait until the end to try to execute everything.

Please note that in my situation, I cannot control the "other application" I mentioned in the original post, which will be only reading, but will not be using the tables in any specific order.  So, I cannot use padhia's suggestion of using the tables in a common order to avoid deadlock.  (I am assuming that the read locks that the other application causes when it reads can contribute to a deadlock, but I'm not sure of this.)

Junior Contributor

Re: How to ensure atomic nature of multiple table actions?

Each request places the neccessary locks (if they're not set already) and all locks will be released when the transaction is commited.

But in every DBMS there's the same recommendation, set the locks at the begin of the transaction to avoid deadlocks:

BEGIN TRANSACTION
lock table table1 for write -- all locks as a single MultiStatement Request
;lock table table2 for write
;
<do some stuff>
<do some other stuff>
<do more stuff>
END TRANSACTION

multiple statements to be added up in a transaction and then executed at once

If you can add up those statements into a single SQL string this would be best case, a MultiStatement Request:

<do some stuff>
;<do some other stuff>
;<do more stuff>
;

Can you show some actual queries?

sdc
Enthusiast

Re: How to ensure atomic nature of multiple table actions?

Dieter, thanks so much for your response.  Very helpful, as always.  I was actually wondering if something like your suggestion was a common practice or not, sounds like it is.  I will try to implement your suggestion and if I can't make it work I'll come up with some actual queries to further illustrate my situation.  Thank you!

sdc
Enthusiast

Re: How to ensure atomic nature of multiple table actions?

Dieter, I am using your suggested method with success.  Thanks for your help!

FYI for anyone's future reference, I had to put a semicolon after "BEGIN TRANSACTION".