two inserts at the same time 3

[iwease]

Hi,

I have a couple questions about what happens when people insert at the same time


Case 1
Suppose that I have a table and two different users. User 1 does a bulk insert in the table. While this bulk insert is being performed, User 2 inserts a record. Will User 2's record appear at the end of the bulk insert, at the beginning of the bulk insert, or in the middle?

Case 2
Suppose that I have a table and two different users. User 1 opens a transaction and does a bunch of separate insert statements. While User 1 is preforming these statements, User 2 inserts a record. Again, will User 2's record appear at the end of the bulk insert, at the beginning of the bulk insert, or in the middle?

Thanks a bunch

 

[SQLDenis]

tables are not ordered, middle last beginning doesn't mean anything. The only way to get back rows in a specific order is by using ORDER BY

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[iwease]

what I mean is, assuming my primary key is an identity column, will the insert from user 2 have an identity that is greater then those from user 1's bulk insert, less then, or inbetween (ie...user 1 has some records with an ID lower than user 2's and some records with an ID above)

 

[SQLDenis]

if it is within a transaction then probably not since it will block the other tran

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[ptheriault]

A bulk insert is run as one big batch. so in case #1 user 2's Ids would be higher than user 1.

In case #2 it depends on who aquires the locks first.

Look up ACID properties in BOL. It will help you understand how transactions work.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[iwease]

ok...so since I am either using a bulk insert or a transaction, User 2's inserted record will never appear with an ID that is between User 1's minimum and maximum ID

Right?

 

[PRPhx]

Also throw into the works, are you doing any kind of locking on your table. And if so, what kind of locking. Again, take a look at BOL and search for lock hints. IF your entire table is locked, no other user will be able to do anything until th elock is released. This means that user1's data will be inserted, the table/page/row/etc unlocked, then user2 can insert.

It also depends on who access athe table first. I know this is a very basic description of locking. BOL has a pretty good description of what goes on.

 

[iwease]

What is BOL? I'm assuming it is some sort of reference?

 

[philhege]

Books On Line; the SQL Server reference.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Not NULL-terminated yet.

 

[iwease]

ah..thanks