Teradata Locks

Database
Enthusiast

Teradata Locks

Hi, Everyone what is the meaning of locklevel in dbqlogtbl when the locklevel is 'Access', Does it mean the table or row is locked? If it is a select statement such as below select * from table where col1='A' and col2='B' where record will be locked? the record both satisfied col1='A' and col2='B' or the record satisfied col1='A' or col2='B'
2 REPLIES 2
Teradata Employee

Re: Teradata Locks

If the optimizer can identify a specific RowHash (e.g. access via PI or USI) then a RowHash lock can be used. (And if a specific partition can be identified for a partitioned table, then a partition-level lock may be used.) Otherwise, a table-level lock would be required.

 

LockLevel is the highest lock level (Access, Read, Write, Exclusive) for any type of lock used for the request.

Highlighted
Ambassador

Re: Teradata Locks

Hi,

 

Adding to @Fred information...

 

An 'access' lock is Teradata's dirty read. The row can still be updated or deleted whilst the SELECT is running. This is the normal lock placed by end/business user queries in a Teradata environment. ETL code is typically the only code that uses a higher level of lock (Read, Write or Exclusive).

 

Using your query as an example:

- If col1 was a single column PI or USI on the table then all rows where the 'col1' criteria is met will be locked.

- (vice versa) is true if 'col2' is a single column PI or USI

- If 'Col1' and 'col2' form a composite PI or USI then only the rows meeting both criteria will be met.

 

If neither of these conditions are met then typically the entire table is locked.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com