April 18, 2014 at 6:23 am
My requirement is a "Lock Table" which locks the table when some process is running ( i.e. no insertion or updation function can be performed) but allows the selection from the table.
April 18, 2014 at 6:29 am
You could use an application lock.
Look up sp_getapplock in BOL
Basically, you could set the lock from the application and then put a check in a trigger.
Once you're done, release the lock.
-- Gianluca Sartori
April 18, 2014 at 7:03 am
I Goggled about "sp_getapplock"
This approach has some downsides
1) Other users can’t SELECT from the table.
2) If you rebuild indexes on the table with the “online=on” option, it will want to put a schema lock on the table, to prevent other schema changes. The exclusive lock prevents the rebuild from starting.
sp_getapplock is the built-in way to allow only one user in a section of code at a time.
Is it right option as my requirement is; When one user insert or update records in database, other user can select records from database but can't insert or update at that time.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply