«

»

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
<databasename>.<tablename>.

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 -




11 comments

1 ping

  1. Raghu Nakk says:

    Very clearly explained. Thank you very much..

  2. Rabbit says:

    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.

  3. Arif says:

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

  4. Kumar says:

    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 says:

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

      1. Kumar says:

        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?

  5. MIKE says:

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

    Thanks.

  6. Awais says:

    Nice and Easy!

    Thanks

  7. Venkatesh says:

    Very neat explanations in all of your posts!

    Thanks admin!

  8. Rohan says:

    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 says:

      thanks rohan :)

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

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

Leave a Reply

Your email address will not be published.

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=""> <strike> <strong>