Please help me with the way by which I can maintain concurrency of a database . For Ex->
Suppose I have a table named as Employee (id int,name varchar(20)) Data is ->
1 Rock 2 Austin 3 BigShow 4 John
Now how should I maintain row level lock on id = 2 ,If one application is trying to update row at id = 2 and simultaneously another application is trying to read the same row (id = 2)
Immediate reply would be appreciated!
asked Jan 30 '12 at 10:06 PM in Default
Are you wanting to manage the concurrency yourself, outside of the controls already built into SQL Server?
Concurrency and locking is controlled by the isolation level. The default , Read Committed, will protect your data exactly as you have described above. An Update will attempt to acquire an exclusive lock, whereas a read will request a shared lock - both of these together are incompatible, so either the read will be blocked until the update is committed, or the update will wait until the read has finished.
answered Jan 30 '12 at 10:07 PM
Kev Riley ♦♦