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.
| Author |
Topic |
|
kensai
Posting Yak Master
172 Posts |
 Posted - 2008-07-28 : 03:39:47
|
| Hi. I have a stored procedure that does a few selects and than a few inserts into multiple tables. I use transaction with default isolation level for this operations, which I think is "read committed". My question is, which part of table does the default isolation level lock for inserts in transactions? Does it lock the whole table? Will I have trouble when two users try to insert at the same time? I checked BOL Isolation levels but couldn't see anything regarding to this, only for reads. The inserted data is not used by anyone else. Should I change the isolation level or is it ok like this? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-07-28 : 06:17:49
|
| Depends on memory available, amount of rows you're inserting, amount of records in the table and a few other things. The isolation level doesn't affect the type of locks taken (row, page, table), just how long they stay around.--Gail ShawSQL Server MVP |
 |
|
|
kensai
Posting Yak Master
172 Posts |
Posted - 2008-07-28 : 10:03:32
|
quote:
Originally posted by GilaMonster
Depends on memory available, amount of rows you're inserting, amount of records in the table and a few other things. The isolation level doesn't affect the type of locks taken (row, page, table), just how long they stay around.
Memory is not decided yet, it's still in development. Only single line rows are inserted to tables with very small amount of data. So I guess there's no simple answer for what'll happen when two users run a stored procedure at the same time which uses a transaction to insert data? |
|
|
|
kensai
Posting Yak Master
172 Posts |
Posted - 2008-07-28 : 10:53:43
|
| Well I did do a test. I created a sql script very similar to the procedure I mentioned. I created a while block doing 100.000 iterations. I put my code in this while block, which means beginning the transaction and committing in the block. I ran this script in four different query windows, so I began and committed 400.000 transactions which have a select and an insert in them. All four of them completed all of the iterations without any errors. So can I say that I'm safe with my transactional inserts? I do hope I'm not missing anything about this test. |
|
|
|
|
|
|
|
|
Transactions and locks question