|
|
|
Forum Newbie
Group: Forum Members
Last Login: 2/8/2006 9:34:00 AM
Posts: 3,
Visits: 1
|
|
Not being familiar with teradata, I have the following issue.
An ETL system requires that tables can be read and written to at the same time by the same user, but different connections. Deadlocks occur, seemingly at random. Is there a way to minimize this using isolation levels as dirty reads, shadow reads ....
Any information is appreciated in my search for an answer
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 2/7/2008 10:41:59 AM
Posts: 61,
Visits: 2
|
|
One option is to modify all your Select statements to include a "lock for access" qualifier.
An easier method would be to create a separate database containing just views that reference the base tables. Then alter the view creation DDL to include the lock for access qualifier. For example:
"Replace View DBName.TableName as lock row for access Select.....;"
Locking for access will allow your reads to occur even if the table is being written to and will allow writes to occur even if it is being read. There is plenty more information on the different locking alternatives in the Teradata manuals.
Good luck,
Jason
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 2/8/2006 9:34:00 AM
Posts: 3,
Visits: 1
|
|
Jason, thank you very much.
I just support our ETL product and need to understand the locking mechanism of Teradata. Unfortunately I do not have manuals (yet). Are there specific ones I need to get hold of?
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 2/7/2008 10:41:59 AM
Posts: 61,
Visits: 2
|
|
Yes, go to http://www.info.ncr.com/Teradata/eTeradata-BrowseBy.cfm
Click on "Teradata Database", then under Titles, click "SQL Reference".
Download the manual titled "SQL Reference: Statement and Transaction Processing" and then look up the chapter titled "Transaction Processing".
--Jason
P.S. As an ETL developer, you will probably want to familiarize yourself eventually with all of the SQL related manuals for your version of Teradata.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 2/8/2006 9:34:00 AM
Posts: 3,
Visits: 1
|
|
|
Jason, wonderful, thank you.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 12/1/2008 3:54:26 PM
Posts: 225,
Visits: 541
|
|
|
If your application queries a table and then updates it within the same transaction, it is vulnerable to a classic deadlock scenario. The query will acquire a read lock and then attempt to upgrade it to a write lock. A deadlock occurs if a competing transaction tries to do the same thing. In this case, locking for access will not solve the problem. Instead, consider using locking for write on the initial query to eliminate the need for a lock upgrade. Alternatively, consider breaking the transaction into multiple transactions.
|
|
|
|