Can we insert and select the same data at a time

Question

Can we insert and select the same data at a time

nitin.iec

SSC Rookie

Points: 34
More actions
September 14, 2009 at 2:57 am

#98109

suppose we have one database it consist one table (x) have 5 records now one person insert two new records and another person at the same done a select statement. so what will be o/p for select statement ?it will will i.e older one i.e. 5 records or new one i.e 7 records.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

nothing happens "at the same time", one event has the follow the next.

If the insert happened milliseconds before the select , depending on you isolation level, the select would be blocked whilst the insert finished then it would return 7 rows.

Clear Sky SQL
My Blog[/url]

nitin.iec SSC Rookie Points: 34 More actions · Answer 2

but suppose the Exceptional case when both insert and select(used in differnt systems and both usres using the same database) statement occured at the same time??then in this case what will be o/p 5 or 7 recrds

Prileep Mathan SSC Veteran Points: 207 More actions · Answer 3

nitin.iec (9/14/2009)
but suppose the Exceptional case when both insert and select(used in differnt systems and both usres using the same database) statement occured at the same time??then in this case what will be o/p 5 or 7 recrds

What output you actually like to see?

I think you can select the way you needed, using proper cursor types.

Paul White SSC Guru Points: 150467 More actions · Answer 4

SQL Server protects you against worrying about such considerations.

Internal synchronization mechanisms exist to ensure that the scenario you are worrying about will never occur.

The answer is therefore as Dave B already said - with one small addition:

Data writers do not always block readers. Row versioning in 2005 and later allows statement- and transaction-level optimistic concurrency when enabled for the database. Readers will always see the latest committed value (either at the start of the SELECT statement, or the transaction, respectively) when using these facilities.

The answer to your question could also be neither 5 nor 7, but that is another story, and I sense that might just confuse you.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

nitin.iec SSC Rookie Points: 34 More actions · Answer 5

simply i want to see weather it will show the old record or new inserted record ......(i am talking about the exceptional case when inset the data and select statement by other user done in exactly same time as the insertion starts)

Paul White SSC Guru Points: 150467 More actions · Answer 6

OK. If the two things happened at EXACTLY the same moment (the database was set up so that the INSERT didn't block the SELECT or vice-versa...)

You would get 5 rows.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

nitin.iec SSC Rookie Points: 34 More actions · Answer 7

as per Mr. White Ans i.e. OK. If the two things happened at EXACTLY the same moment (the database was set up so that the INSERT didn't block the SELECT or vice-versa...)

Means i can get may be 5 or may 7 rows as the O/P depend on the database setup....

Nabha SSCrazy Eights Points: 8534 More actions · Answer 8

nitin.iec (9/14/2009)
as per Mr. White Ans i.e. OK. If the two things happened at EXACTLY the same moment (the database was set up so that the INSERT didn't block the SELECT or vice-versa...)
Means i can get may be 5 or may 7 rows as the O/P depend on the database setup....

Dave and Paul did sum it up very well.

Just to attempt to answer your question, again it depends. Is 7 a committed data, uncommitted data? if it is inserting and the transaction is not complete yet then you wont get 7 no matter what your database set up is, because its neither committed nor uncommitted data. From SQL server point of view, its not still a 'data'

Even when we say 'dirty read' i think its the 'uncommitted' data but not something which is not into the database yet. I mean if the insert operation is not complete it wont even qualify to be uncommitted or committed data.

Paul, Dave, Please correct me if I am wrong. Thanks.

---------------------------------------------------------------------------------

Slick84 SSCertifiable Points: 5602 More actions · Answer 9

Paul White (9/14/2009)
OK. If the two things happened at EXACTLY the same moment (the database was set up so that the INSERT didn't block the SELECT or vice-versa...)
You would get 5 rows.

lol.....

--
:hehe:

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 10

You would get a deadlock if both queries occured at the exact same time.

Table hints will allow you to perform a dirty read. READUNCOMMITTED

Robert Stewart Hall of Fame Points: 3399 More actions · Answer 11

Put down the cursor and step away from it. There is never a reason to use any kind of cursor in this situation.

Prileep Mathan (9/14/2009)
nitin.iec (9/14/2009)
but suppose the Exceptional case when both insert and select(used in differnt systems and both usres using the same database) statement occured at the same time??then in this case what will be o/p 5 or 7 recrds
What output you actually like to see?
I think you can select the way you needed, using proper cursor types.