SQL Server atomic operations - locking table
Posted on 2015-02-05
I have a table called tasks that I'd like to grab about 5 tasks from (from N machines). I want to basically do the following:
- select Y rows, where there's a column in there that gives me the job size as a number, and I want to stop when Y grows greater than say 100
- mark those rows as job started
This query / update is happening from multiple machines, so I need to lock the table and/or do a transaction. What's the right approach?
Thanks a lot!