When user tries to access data from any table Teradata applies read lock on that table. We can see from the explain plan of the query-
Explain sel * from mydb.teradatapoint;
1) First, we lock a distinct mydb.”pseudo table” for read on a
RowHash to prevent global deadlock for mydb.teradatapoint.
2) Next, we lock mydb.teradatapoint for read.
In the second step, Teradata locks the table for read. But from the step 1, we can see that Teradata first applied lock on pseudo table. What is that and why Teradata again wants to use another lock to access the table? Let me explain-
We all know that Teradata divides any table and distributes rows across AMPs i.e. every AMPs in the Teradata hold some portion of a table. So in order to get the data from any table, every AMPs return their portion of the data. Now suppose USER1 submitted select query, i.e. Read access and USER2 submitted delete query at the same time in a table.
Suppose USER1 successfully applied read lock on AMP-1 and USER2 applied Write lock on AMP-2. Now USER1 wants to apply a read lock on AMP-2 to get the remaining part of data and at the same time USER2 also wants to place write lock on AMP-2 to delete data.
So both users will wait for each other for an indefinite time to release the lock. This is called DEADLOCK situation.
Teradata uses Pseudo Lock mechanism to avoid this kind of deadlock situation. Whenever users submit SQL to access any table, Teradata will assign one AMP to inform other AMPs for locking the particular table in all AMPs. This AMP is known as gatekeeper AMP. This gatekeeper AMP is responsible for locking table on all AMPs. If any user tries to access that table, the gatekeeper Amp must know about this, only then the access can be granted.
The gatekeeper AMP takes the request from users on the basis of First Come First Serve. That means the request which reaches the AMP first will get the lock applied and the rest will go in wait state. In this way Teradata avoids Global deadlock situation.
How Teradata decides the Gatekeeper AMP?
The table name goes through Hashing Algorithm and the value generated decides the Gatekeeper AMP. The value is taken from Hash Map and is assigned as Gatekeeper AMP.