Cómo utilizar la extensión PHP PDO para realizar transacciones MySQL en PHP en Ubuntu 18.04

El autor seleccionó Open Sourcing Mental Illness para recibir una donación como parte del programa Write for DOnations.

Introducción

Una transacción MySQL es un grupo de comandos SQL relacionados lógicamente que se ejecutan en la base de datos como una sola unidad. Las transacciones se utilizan para hacer cumplir la normativa ACID (atomicidad, consistencia, aislamiento y durabilidad) en una aplicación. Se trata de un conjunto de estándares que rigen la fiabilidad de las operaciones de procesamiento en una base de datos.

La atomicidad garantiza el éxito de las transacciones relacionadas o un fracaso total si se produce un error. La coherencia garantiza la validez de los datos enviados a la base de datos según la lógica empresarial definida. El aislamiento es la ejecución correcta de transacciones concurrentes, lo que garantiza que los efectos de la conexión de diferentes clientes a una base de datos no se afecten entre sí. La durabilidad garantiza que las transacciones relacionadas lógicamente permanezcan en la base de datos de forma permanente.

Las sentencias SQL emitidas a través de una transacción deben tener éxito o fallar por completo. Si alguna de las consultas falla, MySQL revierte los cambios y nunca se confirman en la base de datos.

Un buen ejemplo para entender cómo funcionan las transacciones MySQL es un sitio web de comercio electrónico. Cuando un cliente realiza un pedido, la aplicación inserta registros en varias tablas, como: ordersy orders_products, según la lógica de negocio. Los registros de varias tablas relacionados con un solo pedido deben enviarse de forma atómica a la base de datos como una sola unidad lógica.

Otro caso de uso es el de una aplicación bancaria. Cuando un cliente transfiere dinero, se envían un par de transacciones a la base de datos. Se debita la cuenta del remitente y se acredita la cuenta del receptor. Las dos transacciones deben confirmarse simultáneamente. Si una de ellas falla, la base de datos volverá a su estado original y no se guardarán cambios en el disco.

En este tutorial, utilizará la extensión PHP PDO, que proporciona una interfaz para trabajar con bases de datos en PHP, para realizar transacciones MySQL en un servidor Ubuntu 18.04.

Prerrequisitos

Antes de comenzar, necesitarás lo siguiente:

• Un servidor Ubuntu 18.04 configurado siguiendo la configuración inicial del servidor con Ubuntu 18.04, incluido un usuario sudo que no sea root.
• Apache, MySQL y PHP instalados en su sistema. Puede seguir la guía sobre cómo instalar la pila Linux, Apache, MySQL y PHP (LAMP) en Ubuntu 18.04. Puede omitir el paso 4 (configuración de hosts virtuales) y trabajar directamente con la configuración predeterminada de Apache.

Paso 1: creación de una base de datos y tablas de muestra

Primero, creará una base de datos de muestra y agregará algunas tablas antes de comenzar a trabajar con transacciones MySQL. Primero, inicie sesión en su servidor MySQL como usuario root:

sudo mysql -u root -p

Cuando se le solicite, ingrese su contraseña de root de MySQL y presione ENTERpara continuar. Luego, cree una base de datos. Para los fines de este tutorial, la llamaremos sample_store:

CREATE DATABASE sample_store;

Verá el siguiente resultado:

OutputQuery OK, 1 row affected (0.00 sec)

Crea un usuario llamado sample_userpara tu base de datos. Recuerda reemplazarlo PASSWORDcon un valor seguro:

CREATE USER 'sample_user'@'localhost' IDENTIFIED BY 'PASSWORD';

Otorgue privilegios completos a su usuario en la sample_storebase de datos:

GRANT ALL PRIVILEGES ON sample_store.* TO 'sample_user'@'localhost';

Por último, vuelva a cargar los privilegios de MySQL:

FLUSH PRIVILEGES;

Verás el siguiente resultado una vez que hayas creado tu usuario:

OutputQuery OK, 0 rows affected (0.01 sec). . .

Con la base de datos y el usuario en su lugar, ahora puede crear varias tablas para demostrar cómo funcionan las transacciones MySQL.

Cerrar sesión en el servidor MySQL:

QUIT;

Una vez que el sistema cierre su sesión, verá el siguiente resultado:

OutputBye.

Luego, inicia sesión con las credenciales que sample_useracabas de crear:

sudo mysql -u sample_user -p

Ingrese la contraseña sample_usery presione ENTERpara continuar.

Cambie a sample_storepara convertirla en la base de datos seleccionada actualmente:

USE sample_store;

Verá el siguiente resultado una vez seleccionado:

OutputDatabase Changed.

A continuación, crea una productstabla:

CREATE TABLE products (product_id BIGINT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(50), price DOUBLE) ENGINE = InnoDB;

Este comando crea una productstabla con un campo llamado product_id. Se utiliza un BIGINTtipo de datos que puede admitir un valor grande de hasta 2^63-1. Se utiliza este mismo campo como para PRIMARY KEYidentificar productos de forma única. La AUTO_INCREMENTpalabra clave indica a MySQL que genere el siguiente valor numérico a medida que se insertan nuevos productos.

El product_namecampo es de un tipo VARCHARque puede contener hasta un máximo de 50letras o números. Para el producto price, se utiliza un DOUBLEtipo de datos para adaptarse a los formatos de punto flotante en precios con números decimales.

Por último, utiliza el InnoDBcomo el ENGINEporque admite cómodamente las transacciones MySQL a diferencia de otros motores de almacenamiento como MyISAM.

Una vez que haya creado su productstabla, obtendrá el siguiente resultado:

OutputQuery OK, 0 rows affected (0.02 sec)

A continuación, agregue algunos elementos a la productstabla ejecutando los siguientes comandos:

INSERT INTO products(product_name, price) VALUES ('WINTER COAT','25.50');
INSERT INTO products(product_name, price) VALUES ('EMBROIDERED SHIRT','13.90');
INSERT INTO products(product_name, price) VALUES ('FASHION SHOES','45.30');
INSERT INTO products(product_name, price) VALUES ('PROXIMA TROUSER','39.95');

Verá un resultado similar al siguiente después de cada INSERToperación:

OutputQuery OK, 1 row affected (0.02 sec). . .

Luego, verifique que los datos se hayan agregado a la tabla de productos:

SELECT * FROM products;

Verás una lista de los cuatro productos que has insertado:

Output+------------+-------------------+-------+| product_id | product_name      | price |+------------+-------------------+-------+|          1 | WINTER COAT       |  25.5 ||          2 | EMBROIDERED SHIRT |  13.9 ||          3 | FASHION SHOES     |  45.3 ||          4 | PROXIMA TROUSER   | 39.95 |+------------+-------------------+-------+4 rows in set (0.01 sec)

A continuación, creará una customerstabla para almacenar información básica sobre los clientes:

CREATE TABLE customers (customer_id BIGINT PRIMARY KEY AUTO_INCREMENT, customer_name VARCHAR(50) ) ENGINE = InnoDB;

Al igual que en la productstabla, se utiliza el BIGINTtipo de datos para customer_idy esto garantizará que la tabla pueda admitir una gran cantidad de clientes hasta 2^63-1 registros. La palabra clave AUTO_INCREMENTincrementa el valor de las columnas una vez que se inserta un nuevo cliente.

Dado que la customer_namecolumna acepta valores alfanuméricos, se utiliza VARCHARun tipo de datos con un límite de 50caracteres. Nuevamente, se utiliza el InnoDBalmacenamiento ENGINEpara admitir transacciones.

Después de ejecutar el comando anterior para crear la customerstabla, verá el siguiente resultado:

OutputQuery OK, 0 rows affected (0.02 sec)

Agregará tres clientes de muestra a la tabla. Ejecute los siguientes comandos:

INSERT INTO customers(customer_name) VALUES ('JOHN DOE');
INSERT INTO customers(customer_name) VALUES ('ROE MARY');
INSERT INTO customers(customer_name) VALUES ('DOE JANE');

Una vez agregados los clientes, verá un resultado similar al siguiente:

OutputQuery OK, 1 row affected (0.02 sec). . .

Luego, verifique los datos de la customerstabla:

SELECT * FROM customers;

Verás una lista de los tres clientes:

Output+-------------+---------------+| customer_id | customer_name |+-------------+---------------+|           1 | JOHN DOE      ||           2 | ROE MARY      ||           3 | DOE JANE      |+-------------+---------------+3 rows in set (0.00 sec)

A continuación, creará una orderstabla para registrar los pedidos realizados por distintos clientes. Para crear la orderstabla, ejecute el siguiente comando:

CREATE TABLE orders (order_id BIGINT AUTO_INCREMENT PRIMARY KEY, order_date DATETIME, customer_id BIGINT, order_total DOUBLE) ENGINE = InnoDB;

Utilice la columna order_idcomo PRIMARY KEY. El BIGINTtipo de datos le permite alojar hasta 2^63-1 pedidos y se incrementará automáticamente después de cada inserción de pedido. El order_datecampo contendrá la fecha y hora reales en que se realizó el pedido y, por lo tanto, utilice el DATETIMEtipo de datos. Se customer_idrelaciona con la customerstabla que creó anteriormente.

Verá el siguiente resultado:

OutputQuery OK, 0 rows affected (0.02 sec)

Dado que el pedido de un solo cliente puede contener varios artículos, es necesario crear una orders_productstabla para guardar esta información.

Para crear la orders_productstabla, ejecute el siguiente comando:

CREATE TABLE orders_products (ref_id BIGINT PRIMARY KEY AUTO_INCREMENT, order_id BIGINT, product_id BIGINT, price DOUBLE, quantity BIGINT) ENGINE = InnoDB;

Utilice el ref_idcomo PRIMARY KEYy esto se incrementará automáticamente después de cada inserción de registro. El order_idy product_idse relacionan con orderslas productstablas y respectivamente. La pricecolumna es de tipo de datos DOUBLEpara poder aceptar valores flotantes.

El motor de almacenamiento InnoDBdebe coincidir con las otras tablas creadas previamente, ya que el pedido de un solo cliente afectará a varias tablas simultáneamente mediante transacciones.

Su salida confirmará la creación de la tabla:

OutputQuery OK, 0 rows affected (0.02 sec)

No agregará ningún dato a las tablas ordersy orders_productspor ahora, pero lo hará más adelante utilizando un script PHP que implementa transacciones MySQL.

Cerrar sesión en el servidor MySQL:

QUIT;

El esquema de la base de datos ya está completo y lo has rellenado con algunos registros. Ahora crearás una clase PHP para gestionar las conexiones a la base de datos y las transacciones MySQL.

Paso 2: Diseño de una clase PHP para gestionar transacciones MySQL

En este paso, creará una clase PHP que utilizará PDO (objetos de datos PHP) para gestionar transacciones MySQL. La clase se conectará a su base de datos MySQL e insertará datos de forma atómica en la base de datos.

Guarde el archivo de clase en el directorio raíz de su servidor web Apache. Para ello, cree un DBTransaction.phparchivo con su editor de texto:

sudo nano /var/www/html/DBTransaction.php

Luego, agrega el siguiente código al archivo. Reemplázalo PASSWORDcon el valor que creaste en el Paso 1:

/var/www/html/DBTransaction.php

?phpclass DBTransaction{    protected $pdo;    public $last_insert_id;    public function __construct()    {        define('DB_NAME', 'sample_store');        define('DB_USER', 'sample_user');        define('DB_PASSWORD', 'PASSWORD');        define('DB_HOST', 'localhost');        $this-pdo = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASSWORD);        $this-pdo-setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);        $this-pdo-setAttribute(PDO::ATTR_EMULATE_PREPARES, false);    }

Hacia el comienzo de la DBTransactionclase, el PDO utilizará las constantes ( DB_HOST, DB_NAME, DB_USER, y DB_PASSWORD) para inicializar y conectarse a la base de datos que creó en el paso 1.

Nota: Dado que estamos demostrando transacciones MySQL a pequeña escala, hemos declarado las variables de la base de datos en la DBTransactionclase. En un proyecto de producción de gran tamaño, normalmente crearía un archivo de configuración independiente y cargaría las constantes de la base de datos desde ese archivo mediante una require_oncedeclaración PHP.

A continuación, configure dos atributos para la clase PDO:

• ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION: Este atributo indica a PDO que genere una excepción si se encuentra un error. Dichos errores se pueden registrar para su depuración.
• ATTR_EMULATE_PREPARES, false:Esta opción deshabilita la emulación de declaraciones preparadas y permite que el motor de base de datos MySQL prepare las declaraciones por sí mismo.

Ahora agregue el siguiente código a su archivo para crear los métodos para su clase:

/var/www/html/DBTransaction.php

. . .    public function startTransaction()    {        $this-pdo-beginTransaction();    }    public function insertTransaction($sql, $data)    {        $stmt = $this-pdo-prepare($sql);        $stmt-execute($data);        $this-last_insert_id = $this-pdo-lastInsertId();    }    public function submitTransaction()    {        try {            $this-pdo-commit();        } catch(PDOException $e) {            $this-pdo-rollBack();            return false;        }          return true;    }}

Guarde y cierre el archivo presionando CTRL+ X, Y, luego ENTER.

Para trabajar con transacciones MySQL, crea tres métodos principales en la DBTransactionclase; startTransaction, insertTransaction, y submitTransaction.

• startTransaction:Este método le indica a PDO que inicie una transacción y desactiva la confirmación automática hasta que se emita un comando de confirmación.

• insertTransaction:Este método toma dos argumentos. La $sqlvariable contiene la sentencia SQL que se va a ejecutar, mientras que la $datavariable es una matriz de datos que se van a vincular a la sentencia SQL, ya que se utilizan sentencias preparadas. Los datos se pasan como una matriz al insertTransactionmétodo.

• submitTransaction: Este método confirma los cambios en la base de datos de forma permanente mediante la emisión de un commit()comando. Sin embargo, si hay un error y las transacciones tienen un problema, el método llama al rollBack()método para revertir la base de datos a su estado original en caso de que se genere una excepción PDO.

Su DBTransactionclase inicializa una transacción, prepara los distintos comandos SQL que se van a ejecutar y, finalmente, confirma los cambios en la base de datos de forma atómica si no hay problemas; de lo contrario, la transacción se revierte. Además, la clase le permite recuperar el registro order_idque acaba de crear accediendo a la propiedad pública last_insert_id.

La DBTransactionclase ahora está lista para ser llamada y utilizada por cualquier código PHP que crearás a continuación.

Paso 3: Creación de un script PHP para utilizar la clase DBTransaction

Creará un script PHP que implementará la DBTransactionclase y enviará un grupo de comandos SQL a la base de datos MySQL. Imitará el flujo de trabajo del pedido de un cliente en un carrito de compras en línea.

Estas consultas SQL afectarán a orderslas tablas y orders_products. Su DBTransactionclase solo debe permitir cambios en la base de datos si todas las consultas se ejecutan sin errores. De lo contrario, obtendrá un error y cualquier cambio que intente realizar se revertirá.

Estás creando un pedido único para el cliente JOHN DOEidentificado con customer_id 1. El pedido del cliente tiene tres artículos diferentes con cantidades diferentes de la productstabla. Tu script PHP toma los datos del pedido del cliente y los envía a la DBTransactionclase.

Crea el orders.phparchivo:

sudo nano /var/www/html/orders.php

Luego, agregue el siguiente código al archivo:

/var/www/html/pedidos.php

?phprequire("DBTransaction.php");$db_host = "database_host";$db_name = "database_name";$db_user = "database_user";$db_password = "PASSWORD";$customer_id = 2;$products[] = [  'product_id' = 1,  'price' = 25.50,  'quantity' = 1];$products[] = [  'product_id' = 2,  'price' = 13.90,  'quantity' = 3];$products[] = [  'product_id' = 3,  'price' = 45.30,  'quantity' = 2];$transaction = new DBTransaction($db_host, $db_user, $db_password, $db_name);

Ha creado un script PHP que inicializa una instancia de la DBTransactionclase que creó en el Paso 2.

En este script, se incluye el DBTransaction.phparchivo y se inicializa la DBTransactionclase. A continuación, se prepara una matriz multidimensional de todos los productos que el cliente está pidiendo en la tienda. También se invoca el startTransaction()método para iniciar una transacción.

A continuación, agregue el siguiente código para finalizar su orders.phpscript:

/var/www/html/pedidos.php

. . .$order_query = "insert into orders (order_id, customer_id, order_date, order_total) values(:order_id, :customer_id, :order_date, :order_total)";$product_query = "insert into orders_products (order_id, product_id, price, quantity) values(:order_id, :product_id, :price, :quantity)";$transaction-insertQuery($order_query, [  'customer_id' = $customer_id,  'order_date' = "2020-01-11",  'order_total' = 157.8]);$order_id = $transaction-last_insert_id;foreach ($products as $product) {  $transaction-insertQuery($product_query, [    'order_id' = $order_id,    'product_id' = $product['product_id'],    'price' = $product['price'],    'quantity' = $product['quantity']  ]);}$result = $transaction-submit();if ($result) {    echo "Records successfully submitted";} else {    echo "There was an error.";}

Guarde y cierre el archivo presionando CTRL+ X, Y, luego ENTER.

Preparas el comando que se va a insertar en la tabla de pedidos mediante el insertTransactionmétodo. Después de esto, recuperas el valor de la propiedad pública last_insert_idde la DBTransactionclase y lo usas como $order_id.

Una vez que tenga un $order_id, utilice el ID único para insertar los artículos del pedido del cliente en la orders_productstabla.

Por último, se llama al método submitTransactionpara confirmar todos los detalles del pedido del cliente en la base de datos si no hay problemas. De lo contrario, el método submitTransactionrevertirá los cambios intentados.

Ahora ejecutará el orders.phpscript en su navegador. Ejecute lo siguiente y reemplácelo your-server-IPcon la dirección IP pública de su servidor:

http://your-server-IP/orders.php

Verá la confirmación de que los registros se enviaron correctamente:

Su script PHP está funcionando como se esperaba y el pedido junto con los productos asociados se enviaron a la base de datos de forma atómica.

Ha ejecutado el orders.phparchivo en una ventana del navegador. El script invocó la DBTransactionclase que, a su vez, envió los ordersdetalles a la base de datos. En el siguiente paso, verificará si los registros se guardaron en las tablas de la base de datos relacionadas.

Paso 4: Confirmación de las entradas en su base de datos

En este paso, verificará si la transacción iniciada desde la ventana del navegador para el pedido del cliente se publicó en las tablas de la base de datos como se esperaba.

Para ello, inicie sesión nuevamente en su base de datos MySQL:

sudo mysql -u sample_user -p

Ingrese la contraseña sample_usery presione ENTERpara continuar.

Cambiar a la sample_storebase de datos:

USE sample_store;

Asegúrese de que la base de datos haya cambiado antes de continuar confirmando el siguiente resultado:

OutputDatabase Changed.

Luego, emita el siguiente comando para recuperar registros de la orderstabla:

SELECT * FROM orders;

Esto mostrará el siguiente resultado detallando el pedido del cliente:

Output+----------+---------------------+-------------+-------------+| order_id | order_date          | customer_id | order_total |+----------+---------------------+-------------+-------------+|  1       | 2020-01-11 00:00:00 |           2 |       157.8 |+----------+---------------------+-------------+-------------+1 row in set (0.00 sec)

A continuación, recupere los registros de la orders_productstabla:

SELECT * FROM orders_products;

Verá un resultado similar al siguiente con una lista de productos del pedido del cliente:

Output+--------+----------+------------+-------+----------+| ref_id | order_id | product_id | price | quantity |+--------+----------+------------+-------+----------+|      1 |  1       |          1 |  25.5 |        1 ||      2 |  1       |          2 |  13.9 |        3 ||      3 |  1       |          3 |  45.3 |        2 |+--------+----------+------------+-------+----------+3 rows in set (0.00 sec)

La salida confirma que la transacción se guardó en la base de datos y que su DBTransactionclase auxiliar está funcionando como se esperaba.

Conclusión

En esta guía, utilizó PHP PDO para trabajar con transacciones MySQL. Si bien este no es un artículo concluyente sobre el diseño de un software de comercio electrónico, proporcionó un ejemplo para usar transacciones MySQL en sus aplicaciones.

Para obtener más información sobre el modelo ACID de MySQL, visite la guía InnoDB y el modelo ACID en el sitio web oficial de MySQL. Visite nuestra página de contenido de MySQL para obtener más tutoriales, artículos y control de calidad relacionados.