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:
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.
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.
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.
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:
"<do some stuff>"
"<do some other stuff>"
"<do more stuff>"
...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.
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.)
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:
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>
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?
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!
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".