well, actually I have two questions, but related to blocking issue. first question is how the dbc.pseudo table block happens? is there anyway to relieve such blocking on a busy system
second is under what condition can an idle session still hold table level locks? I saw several instances of events that some JDBC connected session idle for a long time however blocked batch job which try to rename the table. The idle session is still holding table level access lock.
1. You don't want to get rid of the pseudo-locks :-)
The request to set a table level lock is sent to each AMP in parallel, but it's not guaranteed to be received at exactly the same time on each AMP. Within an AMP locks are serialized, thus two session requesting incompatible locks at the same time might block/deadlock each other. Before requesting a table level locks each session requests a pseudo-table-lock from that AMP who is responsible for that table, serializing the table level lock.
2. A session running in ANSI transaction mode has to COMMIT the transaction to release the locks. A session running in Teradata mode might have submitted a BEGIN TRANSACTION/BT but no END TRANSACTION/ET.
Check the transaction_mode in dbc.sessioninfo view, T -> Teradata, A -> ANSI
Session mode is set in the JDBC connection string using TMODE=A/T and there's a get/setAutoCommit function.