February 23, 2012 at 12:16 am
From a long time i have an interesting question in my mind.Would you people like to clear me here in this case.
Lets say we have one database. Two applications are using the same database.mean App1 and App2.
Lets say here is a table named Table1.At the same time from different locations both applications(consider applications as operators) wants to edit/Update record num 5.How we will have a check that to control ans synchronize both application.
For example record 5 have amount 10.if this is not syncronized one operator will see 10 and other wll see 15(when one update to 15).
Hope you got it?
February 23, 2012 at 2:19 am
it depends what isolation level you have set on the connection, by default its read committed.
if we take this as an example
i open two SSMS query windows, so this acts like the two apps
in one window i do
BEGIN TRAN
UPDATE table SET columnA = 10 WHERE columnB = 'A'
in the other window i do
SELECT columnA FROM table WHERE columnB = 'A'
The select wont return any values due to locks
So I now go back to first update and do
COMMIT TRAN
This tells SQL that the update to 10 has finished and any other process can now access the data
So you should see that the SELECT now has a result set.
If you dont use READ COMMITTED and you use a different isolation level the behavour changes.
I would read up on isolation levels.
February 23, 2012 at 2:26 am
if you do not mind i want to elaborate a little bit more.....explain me
what are isolation levels and how they will be treated?
give me a complete example so that i can adopt in future in writing select/update/delete etc queries.
can we make our data more concurent on sqlser side?
February 23, 2012 at 2:36 am
Engr Shafiq (2/23/2012)
@Anthony:if you do not mind i want to elaborate a little bit more.....explain me
what are isolation levels and how they will be treated?
give me a complete example so that i can adopt in future in writing select/update/delete etc queries.
can we make our data more concurent on sqlser side?
what are isolation levels and how they will be treated?
A quick google search or using BOL will tell you all about isolation levels and the differences between each of them
give me a complete example so that i can adopt in future in writing select/update/delete etc queries
Pick an isolation level, stick with it through out your development and learn the way in which it affects DML. If your using SSMS only then you will default to READ COMMITTED unless you override it by using SET ISOLATION LEVEL ###############
can we make our data more concurent on sqlser side?
Concurrancy is dependent on isolation level, as above, pick one, read about the SQL locking routines which are there to provide you with concurancy.
In your example, if you are using READ COMMITTED as default and have not changed it, then it depends on which app access the record first
If app1 does the update first it will update to 10, if app2 does the update first it will update to 15, but you should always be doing a select before updating in your apps, so the user of app1 can see the value before choosing to update it.
E.g 1
App1 updates first
App1 Does SELECT notices its 5
App1 Does Update to 10
App2 Does SELECT gets no results due to read committed isolation level
App1 Completes Update
App2 now gets results and notices its 10
App2 Does Update to 15
App2 completes update
App1 and App2 will see the record is 15
E.g 2
App2 updates first
App2 Does SELECT notices its 5
App2 Does Update to 15
App1 Does SELECT gets no results due to read committed isolation level
App2 Completes the update
App1 now gets results and notices is 15
App1 DOESN'T need to update to 10
App1 and App2 will see the record as 15
February 23, 2012 at 4:03 am
The question what isolation level to use depends on the expected result for a given scenario.
In your case:
What should App2 return as amount while App1 update the amount column from 10 to 15 for row with id=5 ?
a) 15
b) 10
c) either one, it doesn't matter
d) it would also be ok, if App2 wouldn't return id=5 at all
February 27, 2012 at 10:54 pm
I am writing sqlserver queries from a long time....can you help me how to write insert/update and delete queries in a good and professional way?
February 27, 2012 at 11:30 pm
Engr Shafiq (2/27/2012)
I am writing sqlserver queries from a long time....can you help me how to write insert/update and delete queries in a good and professional way?
New question - new thread, please.
Don't hijack other threads with semi- or non-related questions.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply