Desde MySQL 3.23 el tipo de tabla por default era MyISAM, ahora desde la versión 3.23.34 MySQL soporta otro tipo que es menos conocido llamado InnoDB. Esta es una nueva alternativa que se distingue primordialmente en lo siguiente:
Las operaciones que se realizan con este tipo de tablas pueden ser ejecutadas como transacciones. Esto permite ejecutar diferentes comandos SQL como una sola entidad, si se llegara a presentar algun error durante su ejecución, toda la transacción seria cancelada.
Las transacciones puedes ser ejecutadas en los cuatro diferentes modos de isolation levels del ANSI-SQL/92 standard (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE).
Al llevarse a cabo las transacciones, el engine de InnoDB usa bloqueos a nivel de registro. Esto significa que durante una transaccion no se bloquea toda la tabla, como sería el caso de las tablas MyISAM durante un comando de LOCK TABLE. Esto nos da una gran ventaja cuando muchos usuarios estan modificando la misma tabla.
El engine de InnoDB también reconoce automaticamente un deadlock (esto es cuando dos procesos se estan bloqueando mutuamente) en cuyo caso temina uno de ellos automaticamente.
Cuando se definen relaciones entre tablas, el engine de InnoDB se encarga de que la integridad referencial se respete. (Llave foranea.)
Si se presenta una falla en el equipo, las tablas InnoDB son llevadas a un estado de consistencia (tomando en cuenta que el file system donde se encuentren los datos no este dañado).
Creando tablas InnoDB
A diferencia de las tablas MyISAM, las tablas InnoDB usan un table space para almacenar todas las tablas con sus índices.
En versiones anteriores era necesario especificar el tamaño y ruta del table space, ahora eso no es necesario ya que se hace de manera automatica. Cuando MysQL soporta tablas InnoDB, se crea el archivo ibdata1 con un tamaño de 10MB y cuando este archivo requiere de más tamaño se expande 8MB. Adicionalmente se crean tres archivos de log ib_logfile0, ib_logfile1, y ib_arch_log_0000000000. Estos cuatro archivos estan en formato binario y no deben de cambiarse a mano.
CREATE DATABASE soporte
USE soporte;CREATE TABLE tabla1 (colA INT AUTO_INCREMENT, colB INT,
PRIMARY KEY (colA)) TYPE=InnoDB
INSERT INTO tabla1 (colB) VALUES (10);
Esta es una vista de lo archivos que tenemos en el directorio de MySQL
C:MySQLdata>dir ib*
Volume in drive C is Local Disk
Volume Serial Number is 68D8-B64EDirectory of C:MySQLdata
02/07/2006 11:18a 10,485,760 ibdata1
02/07/2006 11:18a 5,242,880 ib_logfile0
02/02/2006 06:28p 5,242,880 ib_logfile1
3 File(s) 20,971,520 bytes
0 Dir(s) 22,873,788,416 bytes freeC:MySQLdatasoporte>dir
Volume in drive C is Local Disk
Volume Serial Number is 68D8-B64EDirectory of C:MySQLdatasoporte
02/07/2006 11:15a 65 db.opt
02/07/2006 11:17a 8,590 tabla1.frm
2 File(s) 8,655 bytes
2 Dir(s) 22,873,784,320 bytes free
El archivo tabla1.frm contiene solamente la definición de la tabla, en el mismo formato de los demás tipos de tablas.
Los comandos anteriores fueron ejecutados bajo windows, pero es lo mismo para linux. De echo los archivos pueden ser transferibles entre sistemas operativos.
Cambiando el tipo de tabla
Es posible cambiar los tipos de tabla de MyISAM a InnoDB y viceversa. Para hacer esto se hace uso del comando ALTER TABLE:
ALTER TABLE tabla TYPE=InnoDB
El cambio podría fallar si la tabla MyISAM contiene un indice full-text, por lo que primeo debe de eliminarse:
ALTER TABLE tabla DROP INDEX indice
Para linux, es posible cambiar varias tablas mediante el script mysql_convert_table_format, si no se especifica un nombre todas las tablas de la base de datos se convierten.
root# mysql_convert_table_format [opt] --type=InnoDB dbname [tablename]
Nota: No deben de cambiarse las tablas de la base de datos de mysql, estas deben de permanecer MyISAM.
Cambiemos ahora la tabla que creamos a MyISAM:
mysql> alter table tabla1 type=MyISAM;
Query OK, 1 row affected, 1 warning (0.22 sec)
Records: 1 Duplicates: 0 Warnings: 0
Si observamos el directorio, podemos ver que ahora existen 3 archivos para la tabla.
C:MySQLdatasoporte>dir
Volume in drive C is Local Disk
Volume Serial Number is 68D8-B64EDirectory of C:MySQLdatasoporte
02/07/2006 11:15a 65 db.opt
02/07/2006 11:35a 8,590 tabla1.frm
02/07/2006 11:35a 9 tabla1.MYD
02/07/2006 11:35a 2,048 tabla1.MYI
4 File(s) 10,712 bytes
2 Dir(s) 22,872,178,688 bytes free
Regresemos la tabla a InnoDB, donde se eliminaran los archivos y solo quedará tabla1.frm.
mysql> alter table tabla1 type = InnoDB;
Query OK, 1 row affected, 1 warning (0.22 sec)
Records: 1 Duplicates: 0 Warnings: 0C:MySQLdatasoporte>dir
Volume in drive C is Local Disk
Volume Serial Number is 68D8-B64EDirectory of C:MySQLdatasoporte
02/07/2006 11:38a .
02/07/2006 11:38a ..
02/07/2006 11:15a 65 db.opt
02/07/2006 11:38a 8,590 tabla1.frm
2 File(s) 8,655 bytes
2 Dir(s) 22,872,170,496 bytes free
Transacciones
El uso de transacciones nos ayuda a mantener la integridad de los datos en caso de algun problema, como puede ser una falla eléctrica, falla de hardware, etc. Y se ejecutan como una unidad, es decir o se ejecuta todo o no se hace nada. Para poder hacer esto se deben de encerrar los comandos entre un BEGIN y un COMMIT.
Por ejemplo si quisieramos transferir de una cuenta a otro 100 pesos primero deberíamos de ejecutar:
UPDATE tablename SET value=value-100 WHERE accountno=111
y después
UPDATE tablename SET value=value+100 WHERE accountno=222
Para que se ejecutaran como una unidad tendriamos que colocarlos entre el BEGIN y el COMMIT
BEGIN
UPDATE tablename SET value=value-100 WHERE accountno=111
UPDATE tablename SET value=value+100 WHERE accountno=222
COMMIT
Las transacciones también ayudan a que no se modifiquen datos por dos usuarios al mismo tiempo. Con las tablas MyISAM se puede hacer esto con el comando LOCK TABLE, pero esto bloquearía toda la tabla, en cambio con InnoDB solo se bloquea el registro que se esta afectando.
Por default MySQL se encuentra en modo auto commit, esto quiere decir que si no se especifica un BEGIN, MySQL automaticamente lo hace y al finalizar la transacción, automaticamente hace un COMMIT. Cuando nosotro escribimos BEGIN (también es posible hacerlo con START TRANSACTION que es el standard de ANSI), MySQL deshabilita el auto commit y cuando le damos un COMMIT o ROLLBACK se habilita nuevamente el auto commit.
También es posible deshabilitar el autom commit mediante el comando SET AUTOCOMMIT = 0. En este caso todos los queries que se ejecuten, llevaran un BEGIN explicito y tendran que escribir el COMMIT o ROLLBACK para finalizar la transacción.
En las tablas InnoDB no es permitido abrir una transacción dentro de otra, si se hiciera esto MySQL automaticamente le dara un COMMIT a la transacción abierta.
Savepoints
Los savepoints, son marcas que se hacen dentro de una transacción para poder regresar a un punto en especifico. Esto se hace mediante el comando ROLLBACK TO SAVEPOINT, con esto se hace commit a las operaciones que están hasta el SAVEPOINT y lo posterior se hace ROLLBACK.
Termino de transacciones de manera automaticamente
Las transacciónes son terminadas automaticamente, como si se le diara COMMIT por los siguientes comandos:
ALTER TABLE, CREATE INDEX, CREATE TABLE, DROP DATABASE,
DROP TABLE, LOCK TABLES, RENAME TABLE, SET AUTOCOMMIT=1,
TRUNCATE, UNLOCK TABLES
Transacciones y bloqueos
En MySQL es por default es posible consultar información que esta siendo actualizada,(comunmente llamado lecturas sucias) pero esto puede no ser conveniente para ciertas aplicaciones por lo que MySQL ofrece forma de modificar su tipo de bloqueo mediante la palabra LOCK IN SHARE MODE.
Por lo tanto si queremos evitar el hacer lecturas sucias debemos de anexarlo a nuestras sentencias.
SELECT * FROM tabla1 LOCK IN SHARE MODE;
De esta manera la consulta será desplegada solamente cuando no se esten haciendo modificaciones a los datos.
Lo que hace el LOCK IN SHARE MODE, es colocar un bloqueo compartido sobre el registro, pero también es posible colocar un bloqueo exclusivo mediante la palabra FOR UPDATE.