Archives of the TeradataForum

Message Posted: Thu, 19 Oct 2006 @ 21:39:35 GMT

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

As Teradata continues to minimize locking for DDL and dictionary tables (generally a good thing), this is one of the trade-offs. You can explicitly request different locking behavior if that suits your specific situation better.

For example, CREATE INDEX by default now acquires a WRITE lock when it starts and escalates to an EXCLUSIVE lock when it completes. (EXPLAIN works for DDL also.) If the 2641 is occurring on a SELECT, there are several options you could consider, including:

Downgrade the SELECT lock to ACCESS rather than READ

Pro: This SELECT could run while the index is being built (though not during the dictionary update at the very end)

Con: If the SELECT runs a long time, the CREATE INDEX might have to wait to escalate the lock and complete (and while it's waiting, all new requests against the table would be blocked).

Downgrade the initial CREATE INDEX lock to READ

Pro: SELECTs could run while the index is being built.

Con: Other DDL (e.g. a second CREATE INDEX) could be submitted and try to execute against the same table during the CREATE INDEX; such requests would fail, but only after the bulk of the work had been done. Attempts to update the contents of the table may result in deadlocks.

Upgrade the initial CREATE INDEX lock to EXCLUSIVE (like Teradata used to do...)

Pro: SELECT won't get parsed until after the index create finishes

Con: No queries against this table will run until after the index create finishes

This is by no means intended as an exhaustive list either of options or consequences, just food for thought.

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