CODEWITHSUNDEEP
sqlsql-server-2005locking
user287745
user287745

Reputation: 3171

What locks are enforced by SQL Server 2005 Express?

Consider a web page having grid-view connected to SqlDataSource having all permission to insert update and delete.

Publish the web page.

This is all on one computer local

Now

Now I edit in both browsers and press update one by one fine no problem

The last update is the one retained.

But hypothetical situation:

Computer has capability of running two apps at the same time

Both users get ready and press the update in the browsers at the same time

Even if you consider two different computers this is not possible but for this question

Consider it as possible

Update from two different sources to the same database same table same same row

At the same time, same second, same micro second no delay, both hit the database server at the same time.

What will happen?

In theory I have studied that database management software implement locks when writing no reading, no other writing, etc but does SQL Server 2005 Express implement locks in practical or is it assumed that situation like above will never occur?

If locks there please provide explanation or resource which would explain it keeping in view different scenarios of access

Thank you

edit:-- I am not using control like sqldatasource so please when by providing statements to avoid bling update

its like-- algo---

sqlconnection conn=new .....
sqlcommand
command text is "sql statement for updating values of a particular row"
conn.Open();  
cmd.ExecuteNonQuery();  
conn.close;

so as seen how can I define the check that before executenonquery that if the data is recently changed are you sure you want to proceed? or something

I am kind of confused here I think.. }

Upvotes: 0

Views: 257

Answers (2)

Martin Smith
Martin Smith

Reputation: 453328

SQL 2005 will enforce locks. Before a row can be updated the transaction must acquire an exclusive lock on it. Only 1 transaction can be granted this at a time so the other one will have to wait for that transaction to commit (2 phase locking) before being granted the lock that it needs for the update.

The second write will "win" in that it will overwrite the first one. You can implement optimistic concurrency controls in the sqldatasource to detect that the row has changed and abort the second one rather than blindly overwriting the first edit.

Edit

Following clarification to the question. If you want to roll your own you could add a timestamp column to the table (In SQL Server 2005 this is updated automatically when a row is updated) and bring that back as a hidden dataitem in the gridview then in your UPDATE statement add a where clause UPDATE ... WHERE PrimaryKeyColumn=@PKValue AND TimeStampCol=@OriginalTimestampValue If no rows were affected (retrievable from ExecuteNonQuery - generally) then another transaction modified the row. This might be a bit more lightweight than the alternative used by the data source control where it passes back the original values of all columns and adds them into the WHERE clause with similar logic.

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294307

This is solved by most applications using Optimistic Concurency control. Applications simply add more conditions to the update WHERE clause in order to detect changes that occured between the time the data was read and the moment the update is applied. Is called optimistic concurency because the applicaiton assumes no concurent changes will occur, and if they do occur they are are detected and the appplicaiton has to restart the operation. The alternative to optimistic concurency is pesimistic concurency where the applications explicitly locks the data it plans to update. In practice operaitons involving user interaction are never done under pesimistic concurency model.

Other concurency model, specially in distributed applications, is the one implied by the Fiefdom and Emissaries model.

So while database locks and transaction concurency models are always omnipresent in any database operation, when user interaction is involved no application will ever rely on the database locks. User interactions are simply way to long in terms of database transactions. Acquiring locks for the while forgetful Fred is out to lunch and has a data screen open on his desktop simply doesn't work.

Upvotes: 2

Related Questions