Stored Procedure - SQL Server 2000

[AykutArslan]

hey,

how many user access and run the same
"stored procedure" at the same time
on SQL Server 2000?

If more than one,
how can I solve the concurreny problem ?
Any example code for:
- one user is accassing the stored procedure xyz
which is using the table tab1 and tab2
- other user can not access the same stored procedure at the same time
- other user can only read from tab1 and tab2

 

[rwb1912]

If I understand correctly, only one particular user can access the stored procedure at a time. If a user access the SP then he/she can modify the tables a and b, other users would only be able to read the tables. Am I correct ?

If so then you could perform this my modifying the security on these tables while the user is in the procedure. Another possibility is to store a global variable denoting that the SP is in use and when other users access the SP only perform read operations in their context.

Alternatively write seperate procedures one for read and other for write. That way you won't have a contention problem.

Cheers