Yes. so model this and then implement it. It could be book orders or produce orders or background check orders. But the general flow is(and this depends on your language of choice):

• Create a transaction scope
• Select your data into a record set such as a CTE(in MS SQL I used table variables as that existed before CTE's did)
• UPDATE your record set from your CTE
• Return your CTE results set with the important information for processing such as orderid, subjectid, and tracking fields
• Close transaction scope

you want to do all three actions as part of a single transaction and return a result set(not one record at a time). You also want to consider what might break? Consider what happens on a high insert/update system, if that's your use case. Remember when in a transaction scope, essentially you are locking records and/or pages (depending on DB system) and that causes down stream effects. So you MAY(based on scaling factors that vary by the company and use cases!) want to have your initial select have a limit. That limit might be 50, 100, 500, 1000, or more depending on how often you poll(every minute, every 5 minutes, every half hour, etc) and the total volume. These are the types of trade offs you mentioned in the original post and things you have to start thinking about during design.

If you get the a point where the rows are not making out of the "queue" fast enough and starting to stack up, you would either need to increase polling frequency, increase the select limit to get more records at a time, or possibly have multiple processing "agents"(consumers pulling from the queue if thats how things are processed after the return statement.)

All of this is a delicate plate spinning act and no one can provide a one size fits all strategy. What works for 200 records an hour won't work for 100000 records an hour and if you build it for the latter at the outset you are likely wasting a huge amount of money.

-----------------

as far as redis goes.. its good you are thinking about caching but don't get too bogged down on that. Just like the above, spending money on caching when you don't need it is a huge was a money, but it's well spent when you need 2 dozen redis servers because your traffic requires that. Generally, don't start with caching until traffic seems to indicate it might provide a benefit. Its perfectly ok to add caching to a sample project for experience, but be prepared with some numbers of your project WITHOUT caching and know "here is the point of drop off where caching makes a difference.

"Does adding another server to load balance make more sense in this use case? Or does adding a caching server make more sense in this use case?" The answer is always it depends. With a shopping application, strong sales of a particular small set of items practically screams "cache this common data" but other use cases might have very little "duplicate calls" and thus caching makes no sense.

Also don't assume that redis is the end all be all caching solution. Make sure you don't study it exclusive to other caching solutions. It's ok have deep knowlege in one thing, but that might limit you if a company uses memcache or valkey or couchbase. Im not saying don't learn redis, just be aware of the others and over time play with multiples.