Archives of the TeradataForum

Message Posted: Sat, 20 Nov 2010 @ 17:21:57 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Faible,

You're correct, in the example you give request and transaction are probably the same thing, but they are different.

When looking at these items you also need to consider what a Statement is, so here goes with all three.

Statement: this is a single SQL command such as SELECT, CREATE, UPDATE etc.

Request: this is one or more Statements that the Parsing Engine receives as a single piece of text (bear in mind that for a program, SQL is just a character string, i.e. a piece of text). A Request may contain one or more Statements.

Transaction: this is the "unit of work", it all gets done or none of it gets done. When a transaction ends, any locks are released and any changes are committed.

When talking about this area you also have to remember that Teradata supports two transaction modes, Teradata (aka BTET) and ANSI. The definitions shown above are accurate for both modes, but what happens with transactions differs between the two.

Teradata Mode

By default, a Teradata session runs in Teradata mode. In this mode by default each request is a transaction. Because there are no commands which explicitly state the beginning and end of a transaction these are called implicit transactions.

So in the following example there are 4 statements, 3 requests and 3 transactions.

          Update t3
          Set col1 = 'xyz'
          Where col44 = 78;

          Select * from t1;

          Select * from t2; select col1, col3 from t4;

As each request (and therefore the transaction) ends, any locks are released and any changes are committed. So the WRITE lock required by the Update is released when the Update ends.

You can override the implicit transaction definitions of Teradata by using BT and ET commands. So in the following example, there are 4 statements and 3 requests but only 1 transaction.

          BT;

          Update t3
          Set col1 = 'xyz'
          Where col44 = 78;

          Select * from t1;

          Select * from t2; select col1, col3 from t4;

          ET;

Because there is only one transaction, any locks are held and no changes are committed until the ET command. So the WRITE lock required by the Update is held throughout the two SELECT requests.

ANSI Mode

When running in ANSI mode, there is a mix of Teradata's implicit and explicit transaction rules. ANSI transactions are started implicitly (by the first request) but are ended explcitly by a COMMIT command. So, in the following example there are 4 statements and 3 requests but only 1 transaction.

          Update t3
          Set col1 = 'xyz'
          Where col44 = 78;

          Select * from t1;

          Select * from t2; select col1, col3 from t4;

          Commit;

Because there is only one transaction, any locks are held and no changes are committed until the ET command. So the WRITE lock required by the Update is held throughout the two SELECT requests.

Error handling

The biggest difference comes when handling errors.

Basically, in Teradata mode the entire transaction is rolled back. In ANSI mode (I think this bit is correct) the last Request is rolled back, it is then up to the application to decide what to do: re-submit the failing request, commit current changes or rollback.

BTEQ and "RETRY ON"

Firstly, remember that Bteq is ** just ** an application. It is subject to the same rules as any other application running against a Teradata DBMS.

The RETRY option is a feature of this application, nothing to do with the DBMS.

What the manual is refering to by "partially committed transaction" is not strictly accurate, you're correct. What it is describing is shown by the following example:

          BT;

          Update t1
          Set col11 = 'xyz'
          Where col111 = 78;

          Update t2
          Set col22 = 'xyz'
          Where col222 = 78;

          Update t3
          Set col3 = 'xyz'
          Where col333 = 78;

          ET;

Assume that:

- these commands are submmitted by BTEQ with the RETRY option set to ON

- we're running in Teradata mode (must be because we're using BT and ET commands)

- "Update t1" changes 15 rows

- "Update t2" changes 75 rows

- "Update t3" fails

What will happen is this:

- the DBMS will rollback the entire transaction, thereby undoing the changes (90 rows) done by the first two requests in the transaction,

- the DBMS will complete the transaction (i.e. "end transaction"), releasing locks

- the error code gets returned to BTEQ which then resubmits THE LAST REQUEST (i.e. "Update t3...")

- the DBMS treats this as a brand new transaction (because the previous transaction for this session was completed) and because there's no BT command it is treated as an implicit transaction.

- IF this command now works, the changes for "update t3" are done, when the request (which is now a transaction by itself) finishes the locks are released and changes are committed.

It is this situation which is described as "partially committed transaction". Not true in the technical sense (as you correctly pointed out) but from the business/processing logic implied within the code. It is certainly not what was wanted when the code was written.

To add to your 'fun', in the above example the ET command will then fail with a 3510 "too many end transactions" error.

Personally, in any production BTEQ scripts that I write I never use "RETRY ON" and I (almost) always check the return code from each request using ".IF ERRORCODE". That allows me to quit BTEQ or jump past the ET command just to avoid this problem and extraneous errors (because someone in 'management' will demand their explanation !).

I hope that helps. Any more questions, just ask.

Cheers,

Dave

Ward Analytics Ltd: Information in motion ( www.ward-analytics.com )

	<Prev	Next>		<<First	<Prev	Next>	Last>>