May 11

What is a pseudo table Lock in Explain Plan?

When you want to retrieve the rows from the table, the very first step in explain plan is the pseudo table lock on that table

e.g –

Type select * from <databasename>.<tablename> in the SQL assistant, and do the explain plan for this table. The very first step you see is –

1) First, we lock a distinct <databasename>.”pseudo table” for read on a
RowHash to prevent global deadlock for

2)Next, we lock <databasename>.<tablename> for read.

We know that for retrieval of rows from the table we need to put the read lock which we are implementing in step 2, but the question is that what is this pseudo table lock in the step 1 ?

We know that each AMP holds a portion of a table. We also know that when a Full Table Scan is performed that each AMP will read their portion of the table.

Now suppose that two different users wants to place multiple locks on the same table and one user gets one lock and the other user gets another lock. Both user requires lock made by other user and have to wait for indefinite time to acquire that lock because actually both the users are waiting for each other to release lock. This is called DEADLOCK.

A Pseudo Lock is how Teradata prevents a deadlock.

When a user does an All-AMP operation Teradata will assign a single AMP to command the other AMPs to lock the table. We can call this AMP as the “Gatekeeper” AMP. This AMP will always be responsible for locking that particular table on all AMPs. Now all the users running an all AMP query on the table have to report to this “Gatekeeper” AMP for getting permission on locks.

The “Gatekeeper” AMP never plays favorites and performs the locking on a First Come First Serve basis. The first user to run the query will get the lock. The others will have to wait. In this way Teradata prevents the deadlock situation when an all AMP operation is made in the query

Note – Teradata selects this “Gatekeeper” AMP by hashing the tablename used in the select query and then matching the hash value in the hash map. The AMP number which it gets from hash map is assigned as “Gatekeeper” AMP.

Refer the image below taken from Coffings to understand the concept better –


1 ping

Skip to comment form

  1. pankaj

    Hi Admin,

    If the query is doing a single-AMP operation, then will there be no pseudo table lock?

    This way if two users are accessing the table using a single-AMP operation, then also there will be a global deadlock situation.

    Am I right?

  2. ganesh

    This is what i was searching to understand first step in explain plan better


  3. Jojack

    Thanks a lot

  4. Raghu Nakk

    Very clearly explained. Thank you very much..

  5. Rabbit

    Hi Admin,

    Happy New Year..!!!

    Thanks for the explanation . We would like to know

    After hasing the table id, how that singe amp makes sure that other amps don’t acquire the locks.

  6. Arif

    One correction: Teradata selects the Gatekeeper AMP by hashing the Table ID. It does not use the table name for this purpose.

  7. Kumar

    Hi Admin,
    Does this pseudo table lock applies only to the full table scan scenario or for all the data retrieval using select query it’s applicable?

    Thanks for the nice explanation..:)

    1. admin

      No its not applicable only to full table scan, whenever your query is going for all AMP operation then it is applicable

      1. Kumar

        Hi Admin,
        One more clarification, In what cases will the query goes for all AMP operation?
        Just to put in another way, does the all AMP operation means (data distributed equally across the AMP’s) is good for performance wise and also for faster data retrieval?

  8. MIKE

    Hi Admin,
    If you have soft copy of tom coffings book please share it.


  9. Awais

    Nice and Easy!


  10. Venkatesh

    Very neat explanations in all of your posts!

    Thanks admin!

  11. Rohan

    Excellent !! You have explained it in a very simple way. Very Nice work. Keep it up so that guys like me can learn 🙂

    1. admin

      thanks rohan 🙂

  1. What is a pseudo table Lock in Explain Plan? – TeraData Tech » BlinkMoth Software Industries | BlinkMoth Software Industries

    […] Post From Teradata – Google Blog Search: explain plan, pseudo table lock in teradata , pseudo […]

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>