SAS/ACCESS Interface for MySQL

Autocommit and Table Types

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. [cautionend]

Top of Page