I would recommend looking at the locking issue rather than blocking issue first: what kind of query, tool or set up (ANSI?) that caused the locking not being release? Do you allow direct user query access to the base tables? Maybe view is more suitable?
Hi hh,
Its actually a query fired from our microstrategy reporting facility given to the users. This is all valid, as I have checked that and the query should be locking at table level in order to update the table and then continue to produce that results. The updating takes a while, but that is fine at that time.
The lock does eventually get released, but what I would like to do is at a certain time be able to run a job or a script to check which types of locks and maybe which processes are holding these locks.
Can someone please help me with the syntax to do this.
Luke,
Only way to see blocked process is by turning on Locking Logger.
I believe you cannot query the sessions which may have acquired locks w/t Locking logger. You may see if there are any blocked process using this facility.
When you turn on Locking logger, TD System creates a lock logger table.
You may query this table for checking blocked process.
Lets see if somebody else has any other session than this.
Hope that helps.
Vinay
Can anyone help with the following problem :
When we start our batch in the evening, if a user has left a query running or a query has failed but left a non-shareable access lock on a table, then our batch may start but at a point it waits for that lock to be released, which is fine, but it may not get released at all or for a long time. Then at a later time, the blocking lock is found and released to continue with the batch.
I think what I need to do is to run some commands to see if there is any blocking locks there at the start of the batch and then deal with them as early as possible then start the batch.
Can anyone tell me which commands that I would use to achieve this. I am pretty new to teradata so could you please provide the syntax to achieve this.
thanks..