what happens when you drop a table that is used in a running insert query?

Database
Enthusiast

what happens when you drop a table that is used in a running insert query?

Just a general question... what happens when mytbl is dropped after insert into mytbl2 sel * from mytbl is submitted but not yet completed?  mytbl is definately dropped (cannot run any selects on it), but the insert statement is still running.  Will the insert ultimately fail or have an incomplete load?  Or does the fact that it is still running indicate that all necessary information from mytbl was extracted, is stored in spool, and is being manipulated/inserted/distributed etc to the destination table?

3 REPLIES
Senior Supporter

Re: what happens when you drop a table that is used in a running insert query?

what makes you sure that the table is droped while the insert/select is still running? Did you checked the DBQL records for the two statements? Do you still see the insert/select session in viewpoint?

a drop require a exclusive lock

and explain will show something link

  1) First, we lock a distinct db."pseudo table" for exclusive

     use on a RowHash to prevent global deadlock for test_db_uli.test_a.

  2) Next, we lock db.tb for exclusive use.

So, as long as any SQL is touching the table the drop will not be executed.

Enthusiast

Re: what happens when you drop a table that is used in a running insert query?

Just FYI...the query utlimately failed after a considerable runtime...error message 2640:  Specified table does not exist

Thanks!

Teradata Employee

Re: what happens when you drop a table that is used in a running insert query?

The insert select statement was probably sitting in a TASM throttle queue and had not yet started to execute.