What transaction isolation level in Teradata 14 closely matches IsolationLevel.Snapshot?

Question

I am using the Teradat 14 .NET provider. I want to execute the following SQL in a single transaction:

delete mydb.mytable;
insert into mydb.mytable select * from mydb.myothertable;

This issue I have encountered is that, while the delete is instant, the insert takes a few seconds. If a select occurs after the delete, but before the insert has committed (as opposed to before the insert has been executed), no rows return. Therefore, I don't want the results of both statements to be visible by any other SELECT statement until the transaction is committed. IsolationLevel.Snapshot has a description that best matches what I want:

Reduces blocking by storing a version of data that one application can read while another is modifying the same data. Indicates that from one transaction you cannot see changes made in other transactions, even if you requery.

The problem is that Teradata 14 appears to not support this type of transaction:

The isolation level is not supported by this version of Teradata Database.

What do I have to do to keep the results of the delete and insert from being visible to other select statements until the transaction has been committed?

Edit

Here is the code I am using after dnoeth's answer. I am using a Teradata session and am putting all SQL into a single string which is still returning no results if a select is done after the delete, but before the insert as completed. dnoeth, am I doing it as you suggested? Notice that there is no TdTransaction object since I am doing bt;et; in the SQL.

Using con As TdConnection = GetNewConnection()
    Using cmd As TdCommand = con.CreateCommand
        cmd.CommandText = "bt;delete mydb.mytable;insert into mydb.mytable select * from mydb.myview;et;"
        cmd.ExecuteNonQuery()
    End Using
End Using

[Teradata Isolation Levels](http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1142_111A/ch09.131.022.html) — Andrew, Jul 23 '14 at 19:30

score 1 · Answer 1 · answered Jul 24 '14 at 20:31

1

Does your session run in ANSI or Teradata mode?

If you need to run both Delete/Insert within a transaction you must

run both as a MultiStatement Request (MSR), e.g. both send using a single SQL string (preferred)

or

do an explicit BT; DELETE...; INSERT...; ET; in a Teradata session

But then the DELETE will be slower (it was a FastPath Delete before, because it was the last statement within a transaction, thus there was no need to create a Transient Journal).

The probability to submit a Select in-between the Delete/Insert is low, if you have to avoid it, there's another solution called view-switching:

Selects on the table must be done using a view x and you need to create a copy of the table, tab1 and tab2.

Insert into tab2;
replace view x as select * from tab2
delete from tab1 (or keep the data as a kind of backup)

the next time:

Insert into tab1
replace view x as select * from tab1
delete from tab2

and so on...

All Selects before the Replace will access the old version, then there's a short lock for the Replace and all new Selects access the new data.

answered Jul 24 '14 at 20:31

dnoeth

59,503
4
39
56

I am in Teradata mode. I tried the `bt;[..]et;` as you suggested, but I am still seeing an empty table if I execute the select while the statement is completing which will actually be very common. The `insert` takes up to 6 seconds, so that's ample time for at least one user to execute the select during this period. I've already considered the dual table approach, but decided against it due to the overhead involved in managing which table is read from. – oscilatingcretin Jul 25 '14 at 12:07
Also, I updated my question with the code I am using in case that helps you. – oscilatingcretin Jul 25 '14 at 12:09
When you send both Delete/Insert in the same string you don't need BT/ET. The Selects on the table use a dirty read (LOCK ROW ACCESS) when you're able to see anything during the transaction. To avoid this you might block any access using "LOCK TABLE mydb.mytable EXCLUSIVE delete mydb.mytable;insert into mydb.mytable select * from mydb.myview;" – dnoeth Jul 25 '14 at 12:34
Strange. I am getting `[Teradata Database] [3523] The user does not have DROP TABLE access to mydb.mytable` when I am not trying to drop a table. At any rate, would this lock cause all other selects to wait until the command is complete? If so, I've already implemented something similar, but I am really wanting to allow all selects to read from the current data and not see the new data until after the delete and insert are completed. I am thinking that snapshot isolation is what I want, but Teradata does not support that. – oscilatingcretin Jul 25 '14 at 13:32
Yes, EXCLUSIVE lock blocks any other access and you need DROP TABLE rights to do that, sorry. When you remove the ACCESS lock, i.e. go back to the default locks for Selects and Insert, you get ANSI SERIALIZABLE, which is the best you can get in TD when you absolutely need to avoid users selecting during the insert. – dnoeth Jul 25 '14 at 14:16

What transaction isolation level in Teradata 14 closely matches IsolationLevel.Snapshot?

1 Answers1