0

I am sort of a newbie when it comes to transactions in databases, so don't have much experience with them. I am in the situation where I have a jobs table, which is a job queue, and I want to process the job. Ideally I prevent other processes from accessing a particular actively-worked-on job record, and then delete the job after it is completely handled.

The problem is, some jobs will be fully in a single transaction from start to finish, while other jobs have parts which don't need to be wrapped in a transaction. For example, say we are querying for some tags in the middle of a transaction while we are going to increment the tag usage count for a particular user (just making this up), to increase their stats. In theory, I think, I would want to do this (pseudocode):

begin transaction
tag_update_job = select * from jobs where id = x
tag_ids = select ids from tags where name in tag_update_job.tag_names
increment count for user_tag_counts_rollup for each tag_ids
delete job
commit transaction

But what I'd actually like to do is not have that select ids from tags in the transaction, since it doesn't need to be in the transaction, but it requires some transaction data as input to the query. I am using knex.js as a SQL wrapper, for PostgreSQL, and am not sure if it's possible in plain SQL to accomplish what I'm trying to accomplish: have everything be in the transaction, except fetching the tags, even though it occurs in the middle of the transaction.

How can that be accomplished? Or what should I do?

1
  • The first three steps can be done in a single UPDATE statement.
    – user1822
    Jul 1, 2022 at 6:54

1 Answer 1

1

The query does not modify anything, so there is no benefit in keeping it outside the transaction. You might just as well have it be a part of the transaction, because it does not take any unpleasant locks on anything. I am not sure what you are trying to optimize here, but my advice is to leave it well alone.

What you should optimize, however, are the first two queries: that should be a single query with a join.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.