How to lock a table in SQL?

Question

SQL

Actually, there is a C++ program (let's call it create_task) that pulls data from the tasks(mssql) table. This sign is constantly changing. From 10 to 100 new entries can be added per minute. create_task throws a request to the tasks table with a timeout of 5 seconds (looks for entries with time_start = tgdd), and after reading the data, creates a stream, changes the state of the entry in the stream in create_task (time_start = current date) and performs some calculations that last from 2 seconds to 30 min. At the end of the calculation, it removes the entry from create_task. Everything works well and normally, however, when running several copies of create_task, duplication occurs, i.e. for the same entry in tasks, one thread is launched in different copies of create_task. This does not always happen, but when the first copy of create_task read data, created a stream but has not yet changed the status of the record,
here is the code

...
//для работы с com
  CoInitialize(NULL);

  //база данных
  ADODB::_ConnectionPtr m_conn = 0;
  ADODB::_RecordsetPtr m_rs = 0;

  //бесконечный цикл для процесс
  while (TRUE)
       {
                        HRESULT hr;
      try 
      {					
        hr = m_conn.CreateInstance(__uuidof(ADODB::Connection));
        if FAILED(hr) {
          throw _com_error(hr);
        }
        m_conn->CursorLocation = ADODB::adUseClient;	
        m_conn->Open(cons, L"", L"", ADODB::adConnectUnspecified);
        //m_conn->PutIsolationLevel( ADODB::adXactReadUncommitted );
        hr = m_rs.CreateInstance(__uuidof(ADODB::Recordset));
        if FAILED(hr) 
        {
          throw _com_error(hr);
        }
                               //char  sql_query[200] = "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;\0 BEGIN TRANSACTION ;\0 ";
        //m_rs->Open(sreq_param, m_conn.GetInterfacePtr(), ADODB::adOpenStatic, ADODB::adLockReadOnly, ADODB::adCmdUnspecified);

                               //запрос на получение задач на выполнение
                               char sreq_param[200] = "SELECT t.id, t.user_id, t.reports_id, r.name FROM tasks t, reports r WHERE time_start is NULL AND t.reports_id = r.id;\0";
        m_rs->Open(sreq_param, m_conn.GetInterfacePtr(), ADODB::adOpenStatic, ADODB::adLockReadOnly, ADODB::adCmdUnspecified);
        while ( !m_rs->ADOEOF)
                               {
//создаем поток и меняем значение в записи  таблицы

                               }
                              //char sql_query2[200] = "COMMIT TRANSACTION ;\0 ";
           //m_rs->Open(sql_query2, m_conn.GetInterfacePtr(), ADODB::adOpenStatic, ADODB::adLockReadOnly, ADODB::adCmdUnspecified);
                               m_rs->Close();
        m_conn->Close();
                        }
                        catch(...)
      {
        printf("Unhandled exception...");
      };
                        Sleep(5000);

         }
...

Copal towards SET TRANSACTION ISOLATION LEVEL SERIALIZABLE and BEGIN TRANSACTION / COMMIT TRANSACTION Attempts to use are framed as a comment. I don't understand how to use them and how they work.
If one copy of create_task starts BEGIN TRANSACTION and then another, how will the requests behave? What is the scope of BEGIN TRANSACTION (if, for example, in a stream I make a connection to the database)? Is it possible to lock a table using ADODB methods?
A very big request to "chew" the question. Thank you in advance.

Answer 1

Well, you need a queue.
In a bad way, make marks in the table about what happens with the tasks (in processing, in the queue, ...)

Answer 2

when the first copy of create_task was reading data, created a stream but has not yet changed the status of the record, and the second copy was already reading data

Personally, I have been using the SELECT ... FOR UPDATE construct in such cases for many years - i.e. a read with a simultaneous lock that occurs within an open transaction. The first thread reads the record and locks it at the DBMS level. In this case, any other thread(s) that tried to read the record also with the FOR UPDATE modifier will wait in line until the first one completes the transaction (changing the processing status). Only then will the next thread continue execution and complete the read operation. At the same time, we must not forget to check the status - if, after acquiring the lock, we see that the status is "processed", then the current thread is "late" and should end without further processing of the record.