Archives of the TeradataForum

Message Posted: Sun, 16 May 1999 @ 11:33:38 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Just to elaborate on Mark Andrew's response:

With earlier releases, you have a potential deadlock - not on TBL_A, but on the dictionary tables. If USER1 and USER2 both attempt to CREATE TABLE at the same time, they will both attempt to apply locks on DBC.TVFIELDS, DBC.ACCESSRIGHTS, DBC.TVM, DBC.DBASE and DBC.INDEXES.

In releases prior to V2R2, I believe that some of these locks were EXCLUSIVE at the table level. The process of applying such locks is an all-AMP operation, so USER1's request might lock AMPs 1 thru 4 and USER2's request might lock AMPs 5 thru 8 at the same time. Since USER2 has the locks on AMPs 5 thru 8, USER1 can not proceed because USER1 can't get their locks on AMPs 5 thru 8. In turn, since USER1 has the locks on AMPs 1 thru 4, USER2 can not proceed because USER2 can't get their locks on AMPs 1 thru 4. This is the classic deadlock situation. Eventually, an abort will be sent to USER1/2 and an automatic re-try will result. Because these locks are on dictionary tables, it doesn't matter if these tables had the same name or not - the problem was with concurrent CREATE TABLEs.

With releases V2R2 and later, the locks are at the row-hash level and really can't deadlock (or so theory goes). So if USER1 gets the row-hash lock, then USER2 is blocked until USER1 has completed their CREATE TABLE. Obviously, this is a really big improvement.

Either way, one user will be able to create the table and the other user will get an error indicating that they are attempting to create a duplicate table. Given that both users submit their CREATE TABLE at the same time, there's really no way to identify who will actually perform the create and who will get the error.

The behavior above isn't limited to the two users performing CREATE TABLEs, it would also be true if there is a mix of CREATE/DROP TABLE or DROP/DROP TABLE (in releases before V2R2, the name of the table wouldn't matter because the locks were applied at the table level).

This leads to another question: Generally people implement temporary tables with a DROP TABLE followed with the CREATE TABLE. If that's the case, then your problem is much broader. Consider if USER1 is performing the CREATE TABLE TBL_A and USER2 is performing a DROP TABLE TBL_A.

Depending on what you're trying to accomplish and how your machine is being administrated, you can either:

- give each user their own perm space and create the table under their own UserID

- share a common database and modify the table name with UserID to make each table name unique

- pre-CREATE the tables and then manage their use programmatically in your application.

Eric is right about the volatile tables in V2R3 and depending on what you're trying to accomplish, they might be worth investigating.

	<Prev	Next>		<<First	<Prev	Next>	Last>>