Archives of the TeradataForum

Message Posted: Mon, 06 Jul 2009 @ 20:05:29 GMT

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

A "LOCKING XXX FOR ACCESS" is only valid on a SELECT statement. When you are doing a DELETE, the optimizer will take a WRITE lock on the row hash (if you specify the entire primary index in the WHERE clause) or on the table. In the case of your statement, the optimizer will take WRITE lock on the systemx table (or the underlying table if this is a view) since you are specifying an IN clause with a sub-select.

If you want others to be able to read your table while you are deleting the rows, they need to specify "LOCKING ROW FOR ACCESS" or "LOCKING systemx FOR ACCESS" in their queries. This is usually accomplished by specifying "LOCKING ROW FOR ACCESS" in a view that points to the table so that others don't have to code it in their queries.

When you specify "LOCKING ROW FOR ACCESS", the optimizer automatically upgrades it to a table level access lock if you don't specify the primary index, so that's usually the better option to use.

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