Two User Updates the Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Two User Updates the Table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-04 : 09:46:47

matter writes "I have application in Dot net. Their are Two Users updates the same table table simultaneously. What will happen the data. Which will be reflected back?"

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-04 : 12:51:28

SQL Server locks data when it gets modified so they won't really update the data simultaneously. The question is whether or not one of them will still overwrite the others changes or not, and that depends on how you write your code.

For example, if user 1 runs a query like this
UPDATE table1 SET column1 = 'A' WHERE keycolumn = 100

and user 2 runs a query like this
UPDATE table1 SET column1 = 'B' WHERE keycolumn = 100

One of the updates will overwrite the other and the value in column1 after both updates run will depend on which user connection got the lock first (that one will be overwritten by the update from the connection that runs second).

So you can do something like this

For user 1, read the row first, display it to the user, and let them make their change. Keep the value that is read in a variable and use that variable in the update something like this,
user 1 runs a query like this
UPDATE table1 SET column1 = 'A' WHERE keycolumn = 100 AND column1 = @column1originalvalue

and user 2 runs a query like this
UPDATE table1 SET column1 = 'B' WHERE keycolumn = 100 AND column1 = @column1originalvalue

Now the first user to update the row will cause the other users update to fail, so the second update will not overwrite the first. This is called optimistic concurrency. You'd need to do some checking after each update to see if the row was updated or not - so that you can tell the user that their change could not be saved(check the @@rowcount SQL Server variable to see if rows were updated by an UPDATE statement, in this case the first UPDATE will result in @@rowcount being 1 and the second UPDATE will result in @@rowcount being 0)

.NET DataSets and DataAdapters do a lot of this for you but you can customize it by editing the SQL statements or stored procedures that are generated by the DataAdapter.

Ian83

5 Posts

Posted - 2007-01-20 : 09:52:40

(Spam Removed)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources