x

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 '12 at 10:06 PM in Default

innovator gravatar image

innovator
335 14 15 17

(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 '12 at 10:07 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

(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 '12 at 09:02 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x37
x15

asked: Jan 30 '12 at 10:06 PM

Seen: 1135 times

Last Updated: Jan 31 '12 at 09:02 AM