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 –