Locks : Blocks and Blocking

Database
Kks
Enthusiast

Locks : Blocks and Blocking

Sometimes during the batch windows I see from PMON Block and Blocking session .
Is there any way I can record all such sessions and time
and then troubleshoot them

are these locks delay in Batch windows

any help welcome

6 REPLIES
Enthusiast

Re: Locks : Blocks and Blocking

The Blocks are due to the delayed queries. New queries will be kept on hold(Blocked) when some other query that is already running/delayed is still holding up locks on objects which are accessed by the new query.The delayed query is the "Blocking" query while the new query is "Blocked" query.

you can use Lock Display console utility to view the currently active transaction locks.Otherwise if you want to maintain a log, Use the Locking Logger option .When the Locking Logger option is enabled, the Teradata Database maintains ongoing logs of:
• Transaction Identifiers
• Session Identifiers
• Lock Object Identifier
• Lock Levels associated with executing SQL statements which have been delayed because of database lock contention
Kks
Enthusiast

Re: Locks : Blocks and Blocking

is it normal to have blocks and blocking sessions?
or we need to resolve such situtations
Enthusiast

Re: Locks : Blocks and Blocking

Blocks are normal but are not expected frequently. If you encounter frequent blocks, You should probe into such queries and identify what caused the delay.Stale stats,resource contention and badly written queries are some of the main reasons that might delay your queries.
Enthusiast

Re: Locks : Blocks and Blocking

How to enable this Locking Logger?

Will it have a big impact on system resources (CPU, IO, memory) ?

Senior Apprentice

Re: Locks : Blocks and Blocking

You need to enable Locking Logger using the dbscontrol utility (modify general 9 = true) followed by a Teradata restart.

Can't tell exactly about the impact, it depends on the mode (continuous vs. snapshot) and the other LockLog% settings in dbscontrol.

Dieter

Enthusiast

Re: Locks : Blocks and Blocking

Thanks much Dieter.

Just wanted to know if the impact is something like DBQL or should it be generally less than that?

Can this feature be part of day to day production or is it only being used on specific engagements.