master/worker in sql server without locks

What would be the best way to implement master-worker approach using ms sql-server? I.e., one java process (master) puts data to the table(s) and one or more java processes (workers) retrieve this data from table(s), processes it and updates some fields in the records being retrieved.

Which approach will be preferable to implement in order to avoid sql-server locks?

Approach 1: Single master inserts data into one table and one worker retrieves it, processes and updates status flag in the records being retrieved.

Approach 2: Each worker has it's own table and sin开发者_StackOverflow中文版gle master distributes the data across the tables. Each worker retrieves data from it's own table, processes it and updates status flag.

As fas as I concerned there is NO way to enforce sql-server the way to lock the records. It is possible only to give some hints. If so, will the second approach be acceptable?

EDIT:

@Matt Ball: Java process could be just a thread. And locks are not thread locks but sql-server record/page/table locks.

And I want to avoid the sql-server locks or reduce them in order to raise the performance.

Thanks.

---

You can't avoid DB locks, this is how ACID properties are guaranteed in most comercial relational engines. That being said, what you desire is indeed, as Martin pointed out, a queue.. Queues are the cornerstone of the producer-consumer or master-worker patterns. Queues backed by tables are feasable and can scale quite high: hundreds of concurent enqueue/dequeue operations are quite feasable. The article Martin links goes into details explaining how to do it.

You do not need separate queues per worker, nor per master.. Separating into a table per worker will only create operational hurdles: how many tables? What if I add a worker? what if a worker is overloaded, how do I transfer it's pending work to a different one? How do I shut down workers on idle periods? One single queue is the best approach, because you can add/reduce the numebr of workers dynamically w/o doing expensive DB operations (Add new table, transfer existing work etc). A single table will also allow for multiple workers to load-balance the work among themselves naturarly.

While it is technically possible, I would recommend against doing a state based table (state 'to process', state 'processed'). Use instead a true queue, from where records are deleted when a worker picks it up. If you need a persisted state, use a table for state and a queue for events. The enqueued info should contain the id to process.