What Are Dynamic Temporary Tables?

Locks are put on functions for technical reasons. The less restrictive locks, user and restart, are required on those functions that use dynamic temporary tables. Dynamic temporary tables are worktables created and dropped in the same application.

Since they are temporary and built as part of the process, and since there can be multiple copies of them at one time (that is, one copy for each user running the function at a given moment), Deltek decided to build the tables in the tablespace of the user running the application. This way, Costpoint knows whose table is whose. But this also means that a user cannot run the function with the same user ID on more than one workstation. (The same-named table cannot exist more than once in a tablespace.)

Those functions with the most restrictive lock, single, may also use dynamic temporary tables, but they are more stringent for two other reasons. One reason is that the functions write to permanent worktables that do not know whose records are whose. If two users were running the same application, their records would become mixed up and the results would be unpredictable.

The second cause for single application locks is to prevent the possible shutdown of the database. Some functions, if run by more than one person at a time, lock the database. For example, when two people are running the same posting program, they are both selecting from the same ledger and writing to the same ledger. The contention for these records bottles up the database so that the database stops responding to anyone, and single locks result.