数据库 Mysql事务详解

Mysql事务
mysql的事务默认是自动提交的，也就是你提交一个query，他就直接执行！我们可以通过
set autocommit=0  禁止自动提交
set autocommit=1  开启自动提交

//mysql事务
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>
#include <mysql/mysql.h>

#define BEGIN_TRAN "START TRANSACTION"
#define SET_UNAUTO "SET AUTOCOMMIT=0"
#define SET_AUTO "SET AUTOCOMMIT=1"
#define COMMIT_TRAN "COMMIT"
#define ROLLBACK_TRAN "ROLLBACK"

/**
 * mysql_mbegintran - 开启事务
 * @conn:MYSQL对象指针
 * 成功返回0，失败返回错误码
 * */
int mysql_mbegintran(MYSQL *conn)
{
    int ret = 0;
    //执行事务开始SQL
    ret = mysql_query(conn, BEGIN_TRAN);
    if (ret != 0)
    {
        printf("mysql_query() failed ! error message:%s\n", mysql_error(conn));
        return ret;
    }
    //mysql默认事务自动提交，现在设置事务手动提交
    ret = mysql_query(conn, SET_UNAUTO);
    if (ret != 0)
    {
        printf("mysql_query() failed ! error message:%s\n", mysql_error(conn));
        return ret;
    }
    return ret;
}

/**
 * mysql_mrollback - 事务回滚
 * @conn:MYSQL对象指针
 * 成功返回0，失败返回错误码
 * */
int mysql_mrollback(MYSQL *conn)
{
    int ret = 0;
    //执行回滚SQL
    ret = mysql_query(conn, ROLLBACK_TRAN);
    if (ret != 0)
    {
        printf("mysql_query() failed ! error message:%s\n", mysql_error(conn));
        return ret;
    }
    //恢复mysql执行SQL默认提交操作
    ret = mysql_query(conn, SET_AUTO);
    if (ret != 0)
    {
        printf("mysql_query() failed ! error message:%s\n", mysql_error(conn));
        return ret;
    }
    return ret;
}

/**
 * mysql_mcommit - 事务提交
 * @conn:MYSQL对象指针
 * 成功返回0，失败返回错误码
 * */
int mysql_mcommit(MYSQL *conn)
{
    int ret = 0;
    //执行提交SQL
    ret = mysql_query(conn, COMMIT_TRAN);
    if (ret != 0)
    {
        printf("mysql_query() failed ! error message:%s\n", mysql_error(conn));
        return ret;
    }
    //恢复mysql执行SQL默认提交操作
    ret = mysql_query(conn, SET_AUTO);
    if (ret != 0)
    {
        printf("mysql_query() failed ! error message:%s\n", mysql_error(conn));
        return ret;
    }
    return ret;
}

int main(int arg, char *args[])
{
    MYSQL mysql, *conn;
    conn = mysql_init(&mysql);
    conn = mysql_real_connect(&mysql, "localhost", "dbuser1", "123456", "db1",
            0, 0, 0);
    if (conn == NULL)
    {
        /*mysql_error()打印错误原因*/
        printf("mysql_real_connect() failed ! error message:%s \n",
                mysql_error(&mysql));
        return -1;
    }
    printf("connect db server ok !\n");
    //设置字符集
    if (mysql_query(conn, "set names utf8") != 0)
    {
        printf("mysql_query() failed ! error message:%s \n",
                mysql_error(&mysql));
        return -1;
    }
    //开启事务
    if (mysql_mbegintran(conn) != 0)
    {
        return -1;
    }
    //执行多条插入语句
    if (mysql_query(conn,
            "insert into student (name,passwd,classid) values('小米','123',10)")
            != 0)
    {
        printf("mysql_query() failed ! error message:%s \n",
                mysql_error(&mysql));
        return -1;
    }
    if (mysql_query(conn,
            "insert into student (name,passwd,classid) values('小红','123',20)")
            != 0)
    {
        printf("mysql_query() failed ! error message:%s \n",
                mysql_error(&mysql));
        return -1;
    }
    //提交事务
    if (mysql_mcommit(conn) != 0)
    {
        return -1;
    }
    //开启事务
    if (mysql_mbegintran(conn) != 0)
    {
        return -1;
    }
    //执行多条插入语句
    if (mysql_query(conn,
            "insert into student (name,passwd,classid) values('小黑','123',30)")
            != 0)
    {
        printf("mysql_query() failed ! error message:%s \n",
                mysql_error(&mysql));
        return -1;
    }
    if (mysql_query(conn,
            "insert into student (name,passwd,classid) values('小飞','123',40)")
            != 0)
    {
        printf("mysql_query() failed ! error message:%s \n",
                mysql_error(&mysql));
        return -1;
    }
    //回滚事务
    if (mysql_mrollback(conn) != 0)
    {
        return -1;
    }
    /*关闭mysql连接*/
    mysql_close(conn);
    return 0;
}

.SUFFIXES:.c .o
CC=gcc
SRCS=hello.c
OBJS=$(SRCS:.c=.o)
EXEC=hello

start:$(OBJS)
    $(CC) -o $(EXEC) $(OBJS) -lmysqlclient
    @echo "--------OK-------"
.c.o:
    $(CC) -Wall -g -o $@ -c $<
clean:
    rm -f $(OBJS)
    rm -f $(EXEC)