Explicitly lock and unlock a table using ODBC

Question

I have to perform some calculations with data stored in an MSSQL Server database and then save the results in the same database.

I need to load (part of) a table into C++ data structures, perform a calculation (that can take substantial time), and finally add some rows to the same table.

The problem is that several users can access the database concurrently, and I want the table to be locked since the data is loaded in memory until the results of the calculation are written to the table.

Using the ODBC SDK, is it possible to explicitly lock and unlock part of a table?

I have tried the following test program, but unfortunately the INSERT statement succeeds before StmtHandle1 is freed:

SQLDriverConnect(ConHandle1,  NULL, (SQLCHAR *)"DRIVER={ODBC Driver 13 for SQL Server};"
                                               "SERVER=MyServer;"
                                               "DATABASE=MyDatabase;"/*, ... */);

SQLSetStmtAttr(StmtHandle1,SQL_ATTR_CONCURRENCY,(SQLPOINTER)SQL_CONCUR_LOCK,SQL_IS_INTEGER);

SQLExecDirect(StmtHandle1, (SQLCHAR *)"SELECT * FROM [MyTable] WITH (TABLOCKX, HOLDLOCK)", SQL_NTS);


SQLDriverConnect(ConHandle2, NULL, (SQLCHAR *)"DRIVER={ODBC Driver 13 for SQL Server};"
                                              "SERVER=MyServer;"
                                              "DATABASE=MyDatabase;"/*, ... */);

SQLSetStmtAttr(StmtHandle2,SQL_ATTR_CONCURRENCY,(SQLPOINTER)SQL_CONCUR_LOCK,SQL_IS_INTEGER);

SQLExecDirect(StmtHandle2, (SQLCHAR *)"INSERT INTO [MyTable] VALUES (...)", SQL_NTS);

Answer 1

unfortunately the INSERT statement succeeds before StmtHandle1 is freed

By default SQL Server opereates in autocommit mode, i.e. opens a tarnsaction and commits it for you.

You requested TABLOCKX and the table was locked for the duration of your transaction, but what you want instead is to explicitely open a transaction and don't commit/rollback it until you'll done with your calculations, i.e. you should use

begin tran; SELECT top 1 * FROM [MyTable] WITH (TABLOCKX, HOLDLOCK);

And you don't need to read the whole table, top 1 * is sufficient.