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):
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
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?