StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The marked as answer reply tells about deadlock. Though, there was no sufficient information provided to assume this, so it can be just blocking waiting issue. </p> <p>Which error do you see: </p> <ul> <li>1)<br> deadlock victim 1205 error<br> "Server: Msg 1205, Level 13, State 50, Line 1<br> Transaction (Process ID 5?) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."?</li> <li>2)<br> time-out error<br> "Timeout Expired. The Timeout Period Elapsed Prior To Completion Of The Operation Or The Server Is Not Responding."</li> </ul> <p>Since the topic starter wrote: "But the table just locks and releases after 5 to 6 mins", I believe this is the period for 2) while deadlock victims 1) are chosen and rollback-ed in 5 sec. </p> <p>Also, it was mentioned that inserts are from different computers. Queries run by transactional COM+ components will default to serializable tx iso level which exclude deadlocking but increase blocking contention (waiting). </p> <blockquote> <p>"I have used transactions in the stored procedure and also tried setting the isolation levels to READ_UNCOMMITTED, SERIALIZABLE and SNAPSHOT. Nothing seems to work properly" </p> </blockquote> <p>This, more than probably, means that your design and indexing are not "proper" but SQL Server just make its work properly. </p> <p>With the information provided it is guessing game to advise anything.</p> <p>Avoid placing CL-IX (clustered indexes) on columns that are DML-ed (updated, inserted, deleted). DMLs to clustered index key columns will require locks on the CL-IX (to move the row) and all nonclustered indexes (since the leaf level of non-clustered indexes reference rows by CL-IX key value) </p> <p>You should have an indexed table or the whole table will be locked (blocked) on insert leading to increased waiting time and timeouts. </p> <p>Read <a href="http://support.microsoft.com/kb/323630" rel="nofollow">"How to resolve blocking problems that are caused by lock escalation in SQL Server"</a> (note: this is outdated article. For ex., you should use TOP instead of deprecated ROWCOUNT adviced there) </p> <p>In SQL Server 2005 and higher one should consider READ COMMITTED SNAPSHOT tx iso level , which is possible whenever the default READ COMMITTED was used.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload