Maintaining Concurrency of database at row level (Locking)

Hi All,

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!

more ▼

asked Jan 30, 2012 at 10:06 PM in Default

avatar image

335 15 15 20

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Jan 30, 2012 at 10:07 PM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

(comments are locked)
10|1200 characters needed characters left

As @Kev Riley explains - you are worrying about implementing a feature that is already built into SQL Server unless you have changed the isolation level of your environment.

more ▼

answered Jan 31, 2012 at 09:02 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 30, 2012 at 10:06 PM

Seen: 1591 times

Last Updated: Jan 31, 2012 at 09:02 AM

Copyright 2018 Redgate Software. Privacy Policy