SQL server locking queries

Dear All,

I am working on a multi-user application. I have a stored procedure that will be accessed by multiple users at the same time. My stored procedure reads a value from a single row of a table and updates the row later after doing some processing. When one user reads the row of the table, till he/she updates the row, no other user should be allowed to even read that row of the table but to achieve parallel processing, other users can work on other rows.

I am using following locks to achieve consistency and parallelism:
1. rowlock - to achieve parallel processing
2. xlock - to achieve mutual exclusion - when one user reads a row, no other user is allowed to read the same row.
3. holdlock - holds the above locks until user updates the row

code snippet is given below:

BEGIN TRANSACTION
.
.
.
select  @SNId = b.Id , @PartID = a.Id , @DeviceID = a.Device_Id , @CurrSN_1 = b.CurrSerialNo_1 , @CurrSN_2 = b.CurrSerialNo_2_OR_EndSN
from     wps_part_number   a  with (nolock) join   wps_serial_number  b  with (rowlock , xlock , holdlock) on a.Id = b.PartID  where a.Part_Label = @ModelLabel
if  @@ROWCOUNT = 0
begin
      ROLLBACK TRANSACTION
      raiserror('....',16,1)
      return
end

.
.
.

update wps_serial_number with (rowlock , xlock , holdlock) set CurrSerialNo_1 = @Quantity + 1  ,   CurrSerialNo_2_OR_EndSN = null  where id = @SNId;
if @@Error <> 0
begin
           ROLLBACK TRANSACTION
            raiserror('....',16,8)
           return
end
insert into wps_SN_Allocation_History ( PartID , DistID , StartSN , EndSN , AllocationDate ) values ( @PartID , @DistID , 1 , @Quantity , getdate() )
if @@Error <> 0
begin
      ROLLBACK TRANSACTION
      raiserror('.....,16,8)
      return
end

.
.
.

if @@error = 0
      COMMIT TRANSACTION
else
      ROLLBACK TRANSACTION

I am using a transaction beacuse I have to hold a lock until the transaction commits. The main table in the stored procedure is wps_serial_number on which I have to apply locking. After processing wps_serial_number I have to insert a row in wps_sn_allocation_history table and all these tasks should be done as an atomic operation.

Now, i have the following problems/queries:

1. Even I am locking a single resource i.e. wps_serial_number table, yet i am getting the following message when i run the stored procedure using a multi-threading application:

Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Because i am locking on a single resource, i should not get any deadlock.

2. As i am using wps_serial_number table in 2-3 queries but the same row each time. Do i need to apply all the locks each time i run a query on wps_serial_number table.
As i am using holdlock option, i think i need to mention the locks in the first query only.

3. Do i need to mention the locks in insert operation also for a multi threading application where multiple threads try to insert in a table at the same time or sql server takes care of locking itself while insertion.

Can anybody please tell me a better option in terms of locking or isolation level for the above application or please let me know what and where i am doing wrong.

Waiting for your responses...

Thanks in anticipation,
Regards,
Sorabh Kumar