Is it OK to perform extra backend operations while locking the table?

Hello,

I've never used transactions or locks before, but I think in my case I had to use it now.

This is the situation: I need to SELECT codes from the codes table, then INSERT them to Table2, but I have to make sure that no other users insert the same codes from codes into Table2.

The problem is that the codes aren't a unique column, so the same user can insert multiple rows with the same code, but only at the same request.

But before I insert the codes to Table2, I prepare the array result from the SELECT query to the insert. What I do is I get the result into an array, then I add the data (Table2 columns) to the array, then I insert them in bulk.

"Pseudo" code:

Begin Transaction;

$codes_array = // SELECT codes from codes table WITH (HOLDLOCK, UPDLOCK)

// Now add extra data to the above array in my backend (PHP):
$data_to_insert = [];
foreach ($codes as $code) {
  $data_to_insert[] = [
    "code" => $code,
    "some_column" => "some_data",
  ]
}

INSERT $data_to_insert to Table2

Commit Transaction;

So I select the codes with (UPDLOCK, HOLDLOCK) so no one else can select codes at this time, then append extra data then insert to Table2.

Is it OK to do that? The foreach loop takes really really short time, but still I wonder if that's something OK to do, i.e. to hold the table locked for backend operations and not just other SQL operations

Also in my case, is there a risk for a dead lock? I didn't think so but again, it's the first time I use it

LaryAI

3 years ago

Level 58

Yes, it is okay to perform extra backend operations while locking the table. In fact, it is a common practice to use locks and transactions to ensure data consistency and prevent race conditions.

Regarding the risk of a deadlock, it depends on the specific implementation and usage of locks. In the provided code snippet, there doesn't seem to be a risk of a deadlock since the transaction is short and the locks are released after the insert operation. However, it's always a good practice to test and monitor the application for any potential issues.

Here's an updated version of the code snippet with some minor improvements:

BEGIN TRANSACTION;

SELECT codes FROM codes WITH (UPDLOCK, HOLDLOCK);

$data_to_insert = [];
foreach ($codes as $code) {
  $data_to_insert[] = [
    "code" => $code,
    "some_column" => "some_data",
  ];
}

INSERT INTO Table2 (code, some_column) VALUES (?, ?) $data_to_insert;

COMMIT TRANSACTION;

In this version, we use parameterized queries to prevent SQL injection attacks and improve performance. We also use the INSERT INTO syntax instead of the INSERT statement with an array of values.

Please or to participate in this conversation.