SQLite transaction on power failure or system crash

Question

I have a running C program that updates an SQLite record's description and I want it to be transactional that's why there is BEGIN and COMMIT statements before and after the UPDATE statement is executed. I'm not sure if this is already sufficient if in case my SQLite DB system will encounter power failure or brownout or operating system crash? Thank you!

#include <stdio.h>
#include <string.h>
#include <sqlite3.h>

int
main(void)
{
        sqlite3 *db_ptr;
        sqlite3_stmt *stmt0, *stmt1, *stmt2;
        char desc[25];
        int ret = 0;

        ret = sqlite3_open("stock.db", &db_ptr);

        if (ret != SQLITE_OK) {
                printf("Database opening error\n");
        }

        printf("Modify description: ");
        gets_s(desc, 24);

        char sql_stmt0[128];
        strlcpy(sql_stmt0, "BEGIN TRANSACTION", sizeof(sql_stmt0));
        strlcat(sql_stmt0, ";", sizeof(sql_stmt0));
        ret = sqlite3_prepare_v2(db_ptr, sql_stmt0, -1, &stmt0, 0);
        sqlite3_step(stmt0);

        char sql_stmt1[128];
        strlcpy(sql_stmt1, "UPDATE tbl_stock SET DESCRIPTION = ", sizeof(sql_stmt1));
        strlcat(sql_stmt1, "'", sizeof(sql_stmt1));
        strlcat(sql_stmt1, desc, sizeof(sql_stmt1));
        strlcat(sql_stmt1, "'", sizeof(sql_stmt1));
        strlcat(sql_stmt1, "WHERE BCID = '900012345';", sizeof(sql_stmt1));
        ret = sqlite3_prepare_v2(db_ptr, sql_stmt1, -1, &stmt1, 0);
        sqlite3_step(stmt1);

        char sql_stmt2[128];
        strlcpy(sql_stmt2, "COMMIT", sizeof(sql_stmt2));
        strlcat(sql_stmt2, ";", sizeof(sql_stmt2));
        ret = sqlite3_prepare_v2(db_ptr, sql_stmt2, -1, &stmt2, 0);
        sqlite3_step(stmt2);

        if (ret != SQLITE_OK) {
                printf("Unable to update stock\n.");
                sqlite3_close(db_ptr);
                return 1;
        }

        sqlite3_finalize(stmt0);
        sqlite3_finalize(stmt1);
        sqlite3_finalize(stmt2);
        sqlite3_close(db_ptr);

        return 0;
}

Question aside, you may wish to study [parameter binding](https://stackoverflow.com/questions/64311771/how-do-you-bind-parameters-to-a-sql-statement-in-sqlite3-for-c) for SQLite queries. It makes the code simpler to read, and handles quotes better. — MyICQ, Aug 21 '22 at 09:14

score 0 · Accepted Answer · answered Aug 21 '22 at 10:10

0

Generally speaking, this is as much as you can get from an ACID-compliant database. For details, make sure to read the SQLite documentation, particularly Atomic Commit In SQLite and How To Corrupt An SQLite Database File

answered Aug 21 '22 at 10:10

PChemGuy

1,582
3
6
18

score 0 · Answer 2 · answered Aug 21 '22 at 16:14

As I think further, I do a simple simulation with OS crash by calling this sysctl knob sysctl debug.kdb.panic=1 to produce kernel panic in FreeBSD (this is my OS used). I apply this knob one-at-a-time in some areas of the code to generate 5 scenarios. With 1st, 2nd, 3rd and 4th scenarios, the UPDATE query did not reflect in the database, only in the 5th scenario when panic occurs after closing the database with sqlite3_close(db_ptr) and in all these scenarios the database did not corrupt. Here's the modified code.

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>

int
main(void)
{
        sqlite3 *db_ptr;
        sqlite3_stmt *stmt0, *stmt1, *stmt2;
        char desc[25];
        int ret = 0;

        ret = sqlite3_open("stock.db", &db_ptr);

        if (ret != SQLITE_OK) {
                printf("Database opening error\n");
        }

        printf("Modify description: ");
        gets_s(desc, 24);

        char sql_stmt0[128];
        strlcpy(sql_stmt0, "BEGIN TRANSACTION", sizeof(sql_stmt0));
        strlcat(sql_stmt0, ";", sizeof(sql_stmt0));
        ret = sqlite3_prepare_v2(db_ptr, sql_stmt0, -1, &stmt0, 0);
        sqlite3_step(stmt0);

        system("sysctl debug.kdb.panic=1");     /* 1st scenario */

        char sql_stmt1[128];
        strlcpy(sql_stmt1, "UPDATE tbl_stock SET DESCRIPTION = ", sizeof(sql_stmt1));
        strlcat(sql_stmt1, "'", sizeof(sql_stmt1));
        strlcat(sql_stmt1, desc, sizeof(sql_stmt1));
        strlcat(sql_stmt1, "'", sizeof(sql_stmt1));
        strlcat(sql_stmt1, "WHERE BCID = '900012345';", sizeof(sql_stmt1));
        ret = sqlite3_prepare_v2(db_ptr, sql_stmt1, -1, &stmt1, 0);
        sqlite3_step(stmt1);

        system("sysctl debug.kdb.panic=1");     /* 2nd scenario */

        char sql_stmt2[128];
        strlcpy(sql_stmt2, "COMMIT", sizeof(sql_stmt2));
        strlcat(sql_stmt2, ";", sizeof(sql_stmt2));
        ret = sqlite3_prepare_v2(db_ptr, sql_stmt2, -1, &stmt2, 0);
        sqlite3_step(stmt2);

        system("sysctl debug.kdb.panic=1");     /* 3rd scenario */

        if (ret != SQLITE_OK) {
                printf("Unable to update stock\n.");
                sqlite3_close(db_ptr);
                return 1;
        }

        sqlite3_finalize(stmt0);
        sqlite3_finalize(stmt1);
        sqlite3_finalize(stmt2);

        system("sysctl debug.kdb.panic=1");     /* 4th scenario */

        sqlite3_close(db_ptr);

        system("sysctl debug.kdb.panic=1");     /* 5th scenario */

        return 0;
}

SQLite transaction on power failure or system crash

2 Answers2