MySQL supports
several table types, two of which are InnoDB (the default) and MyISAM.
A single database can contain tables of different types. The behavior
of a table is determined by its table type. For example, by definition,
a table created of MyISAM type does not support transactions. Consequently,
all DML statements (updates, deletes, inserts) are automatically committed.
If you need transactional support, specify a table type of InnoDB
in the
DBCREATE_TABLE_OPTS= LIBNAME option. This table type allows for updates, deletes,
and inserts to be rolled back if an error occurs; or updates, deletes,
and inserts to be committed if the SAS DATA step or procedure completes
successfully.
By default, the MYSQL
LIBNAME engine sets
AUTOCOMMIT=YES regardless
of the table type. If you are using tables of the type InnoDB, set
the LIBNAME option
AUTOCOMMIT=NO to
improve performance. To control how often COMMITS are executed, set
the
DBCOMMIT= option.
Note: The DBCOMMIT option can affect
SAS/ACCESS performance. Experiment with a value that best fits your
table size and performance needs before using it for production jobs.
Transactional tables require significantly more memory and disk space
requirements.