September 24, 2015 at 4:53 am
Hi There,
Im have a situation to insert data from multiple sources to a single table.
ie., multiple and concurrent insert on same table
will it lead to dead lock at any point? is there any possibility?
How insert will work ?
what is the architecture ?
any references to read?
Thanks in advance
September 24, 2015 at 11:38 am
squvi.87 (9/24/2015)
Hi There,Im have a situation to insert data from multiple sources to a single table.
ie., multiple and concurrent insert on same table
will it lead to dead lock at any point? is there any possibility?
Certainly the possibility for deadlocks is there. It depends on how the queries are written. If we're only, ever, talking about an INSERT statement, then no, for the most part, deadlocks are not what you have to worry about. However, if you also have reads/updates/deletes mixed in with the inserts, you could see deadlocks. The traditional mechanisms of ensuring that the queries run as fast as possible, that the tables are accessed in the same order, still apply.
How insert will work ?
Same as any other insert. An insert from two web apps or from two client apps or from two anythings, is just an insert from two locations. One of them will hold a lock while it does the insert. The other will wait until it can get a lock on that page or a different page and it will do its insert. Over and over.
what is the architecture ?
any references to read?
Thanks in advance
Not sure what you mean. What is the architecture for an insert? There isn't one. Is there an architecture that allows for higher volume inserts? Not really at the fundamentals level. Just make sure your data is properly normalized (and no denormalization doesn't actually help here). If you're in the situation where you know that IO Latches are your number one contention issue, then you can look to in-memory tables as a mechanism. However, that's an Enterprise only solution.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply