Locking preventing users being allocated work

[Steve Bell]

I have a stored procedure that is being used to allocate customer's work requests to users when they press a button on a UI.

The SQL within the stored procedure is split in the following way

Allocate based on priority
--------------------------
A SELECT is done joining the 4 tables (see below) to determine what work hasn't been been allocated and whether the user has the ability to do the work and the priority/SLA of the type of work to make sure they get the right piece of work where more than 1 exists of that type.

Allocate based on SLA
---------------------
A SELECT is done joining the 4 tables (see below) to determine what work hasn't been been allocated and whether the user has the ability to do the work and the priority/SLA of the type of work to make sure they get the right piece of work where more than 1 exists of that type.

Update tables
-------------
If any work is found, of which there may be more than 1 piece for the customer, then the rows on one or both of the 2 work tables are updated to reflect they have been allocated to that user.

Tables
1 x Work type A (*)
Includes ...
Customer Reference
Type of Work Id
Date Received
Status
User Id

1 x Work type B (*)
Includes ...
Customer Reference
Type of Work Id
Date Received
Status
User Id

1 x User Abilities
Includes
User Id
Type of Work Id

1 x Reference Data for work types
Includes
Type of Work Id
Type of Work Description
Priority
SLA

(*) These tables are joined in a view to give a consolidated view of unallocated work. Both tables contain work in different states of progress.

To prevent multiple users being allocated the same work I was using "FOR UPDATE" in the queries. However we encountering deadlocks as a consequence.

I've tried restructuring the SQL to embed each SELECT within an UPDATE statement and removed the corresponding "FOR UPDATE" clauses. In testing this hasn't prevented the same customer being allocated to multiple users.

I'm not clear if "LOCK IN SHARE MODE" will cure the problem and if it is better to keep the SELECT separate from the UPDATE or embed it within the UPDATE statement.