Phantom objects when parallel transaction create views and drop tables

Detail name	Value
Changelog Number	5607
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0
Fix Versions	Exasol 6.1.0, Exasol 6.0.12
Resolution Date	2018-09-17

Background

As "phantom objects" we classify objects that are not accessible/visible to the users (DBA included) but are still using up system resources. This means they are not visible in catalog system tables and no queries can be performed on them. They are harmless in the sense that no query may accidentally use them, but phantom tables do use space like normal tables do. For large phantom tables, the extra space used may be significant.

Synopsis

We have discovered several scenarios where phantom tables are created when two parallel transactions drop table(s) and create view(s). In those scenarios the newly created view(s) use the table(s) that are being deleted.

Details

The following table details the scenario and helps explain how phantom tables come into being:
Assumption: We assume we have view v on table phantom:

create table phantom (i int);
create view v as select * from phantom;

Session 1	Session 2	Details
set autocommit off	set autocommit off	Autocommit switched off in both sessions
create or replace table phantom ...		Table phantom is recreated, meaning old table dropped, now a brand new table is created
	create or replace view v as select * from phantom	The newly created view refers the old phantom table
commit;		new table committed
	commit	new view committed

Notes:

• The view uses the new table but internally still refers to the old one preventing the deletion of the old table
• Dropping the table and creating a view lead to a phantom table scenario as well, but this one fixes itself automatically when the view is used (view is outdated)
• The order in which the transactions commit is irrelevant, either way the phantom table is generated
• Other scenarios which replace an existing table to one with the same name lead to the very same outcome (e.g. create table tmp; drop table phantom; rename table tmp to phantom; )

Identifying whether a system is affected by phantom objects

Relevant information about data usage can be found in the "$EXA_STATS_DB_SIZE" (hidden) system table. The query below retrieves the total data usage of the system, the data used by the database objects, and the difference between them as unexplained (phantom) data. Large values in the PHANTOM_SIZE column are an indication that the system is likely affected by phantom objects.

select
	measure_time,
	COMMIT_SIZE,
	MEM_SIZE,
	MULTICOPY_SIZE,
	COMMIT_SIZE -MEM_SIZE -MULTICOPY_SIZE PHANTOM_SIZE,
	cast( (COMMIT_SIZE -MEM_SIZE - MULTICOPY_SIZE) * 100 / COMMIT_SIZE as dec(7, 3)	) "PHANTOM_%"
from
	(
	select
		cast(COMMITTED_SIZE / 1024 / 1024 as dec(12, 1)) COMMIT_SIZE,
		cast(MULTICOPY_DATA / 1024 / 1024 as dec(12, 1)) MULTICOPY_SIZE,
		cast( (MEM_OBJECT_SIZE +INDICES_MEM_SIZE +STATISTICS_SIZE) / 1024 / 1024 as dec(12, 1)	) MEM_SIZE,
			T.*
		from
			EXA_STATISTICS."$EXA_STATS_DB_SIZE" T
	)
where
	measure_time between date '2017-11-01' and
	date '2018-06-01' and
	to_char(measure_time) like '% 00:0%'
order by
	MEASURE_TIME desc;

 

Workaround in Exasol 6.0.11 and lower

• Remove the phantom tables: the only way to remove the phantom tables is to restart the database and set the command-line parameter -removePhantomViewReferences=1
• Minimize phantom table effect: avoid executing in parallel transactions create or replace table and create or replace view statements with the view using the table. If this cannot be avoided, one can miminize the effect of phantom tables by executing truncate table before dropping the table - this way very little space is kept occupied and never used.

Exasol 6.0.12 and higher

In Exasol 6.0.12 the above described phantom generating scenario has been fixed and the views remove phantom objects upon access. To activate the fix the database must be started with the following command-line parameter set: -useWriteLocksOnDroppedObjects=1. Apart from setting write locks on dropped objects and thus helping with phantom detection, it also truncates the tables to be dropped before the actual drop takes place and thus the space taken by potential phantom tables is reduced to the table metadata which is very small. On the negative side, this may also trigger transaction conflicts involving dropped objects.

Changed behavior

Write locks are set on dropped objects when the new command-line parameter {{-useWriteLocksOnDroppedObjects}} is set ({{-=useWriteLocksOnDroppedObjects=1}}). This may generate potential transaction conflicts involving the dropped objects.