Blocking problem
in [Programming SQL]
|
From: Jack W. on 23 Feb 2010 16:45 Hi there, I'm invoking "SqlConnection.BeginTransaction()" just before I do a series of inserts into multiple tables. I just ran a test and discovered that until the transaction is committed, it apparently blocks other users coming through the same code. Each user will be inserting completely different data into the same tables using this code and it needs to occur simultaneously (I don't want this blocking other users IOW). Can someone enlighten me on how to do this. I'm guessing it has something to do with the transaction's isolation level but it's not clear to me which (if any) will fix this (by calling the "SqlConnection.BeginTransaction()" overload that takes an "IsolationLevel" enumerator). Thanks in advance.
From: Erland Sommarskog on 23 Feb 2010 16:58 Jack W. (_no_spam@_no_spam.com) writes: > I'm invoking "SqlConnection.BeginTransaction()" just before I do a > series of inserts into multiple tables. I just ran a test and discovered > that until the transaction is committed, it apparently blocks other > users coming through the same code. Each user will be inserting > completely different data into the same tables using this code and it > needs to occur simultaneously (I don't want this blocking other users > IOW). Can someone enlighten me on how to do this. I'm guessing it has > something to do with the transaction's isolation level but it's not > clear to me which (if any) will fix this (by calling the > "SqlConnection.BeginTransaction()" overload that takes an > "IsolationLevel" enumerator). Thanks in advance. Use Profiler to see what the API generates. If it issues SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, you are in trouble. READ COMMITTED is probably what you want. But the cause could also be due to poor indexing. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Jack W. on 23 Feb 2010 17:07 >> I'm invoking "SqlConnection.BeginTransaction()" just before I do a >> series of inserts into multiple tables. I just ran a test and discovered >> that until the transaction is committed, it apparently blocks other >> users coming through the same code. Each user will be inserting >> completely different data into the same tables using this code and it >> needs to occur simultaneously (I don't want this blocking other users >> IOW). Can someone enlighten me on how to do this. I'm guessing it has >> something to do with the transaction's isolation level but it's not >> clear to me which (if any) will fix this (by calling the >> "SqlConnection.BeginTransaction()" overload that takes an >> "IsolationLevel" enumerator). Thanks in advance. > > Use Profiler to see what the API generates. If it issues > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, you are in trouble. READ > COMMITTED is probably what you want. > > But the cause could also be due to poor indexing. Thank you. I'll do that but I just tested all of these isolation levels using "SqlConnection.BeginTransaction()" and none of them fix the problem. Could it be some setting needs to be turned on in the DB itself? If not and you say I'm in trouble, then you're saying there's no way to get around this? Is the problem with the indexing itself as you suggested, i.e., SQL Server places an exclusive lock on it. I thought SQL Server could handle multiple concurrent transactions like this (and it is a serious problem for me if I can't fix it - who would have thought).
From: Uri Dimant on 24 Feb 2010 03:12 Jack W It could ne defenitely the problem of indexing. Do you identify blocked/blocking proccess? What type of LOCK do they acquire? "Jack W." <_no_spam@_no_spam.com> wrote in message news:Ou6ZjSNtKHA.1796(a)TK2MSFTNGP02.phx.gbl... >>> I'm invoking "SqlConnection.BeginTransaction()" just before I do a >>> series of inserts into multiple tables. I just ran a test and discovered >>> that until the transaction is committed, it apparently blocks other >>> users coming through the same code. Each user will be inserting >>> completely different data into the same tables using this code and it >>> needs to occur simultaneously (I don't want this blocking other users >>> IOW). Can someone enlighten me on how to do this. I'm guessing it has >>> something to do with the transaction's isolation level but it's not >>> clear to me which (if any) will fix this (by calling the >>> "SqlConnection.BeginTransaction()" overload that takes an >>> "IsolationLevel" enumerator). Thanks in advance. >> >> Use Profiler to see what the API generates. If it issues >> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, you are in trouble. READ >> COMMITTED is probably what you want. >> >> But the cause could also be due to poor indexing. > > Thank you. I'll do that but I just tested all of these isolation levels > using "SqlConnection.BeginTransaction()" and none of them fix the problem. > Could it be some setting needs to be turned on in the DB itself? If not > and you say I'm in trouble, then you're saying there's no way to get > around this? Is the problem with the indexing itself as you suggested, > i.e., SQL Server places an exclusive lock on it. I thought SQL Server > could handle multiple concurrent transactions like this (and it is a > serious problem for me if I can't fix it - who would have thought).
From: Erland Sommarskog on 24 Feb 2010 03:21 Jack W. (_no_spam@_no_spam.com) writes: > Thank you. I'll do that but I just tested all of these isolation levels > using "SqlConnection.BeginTransaction()" and none of them fix the > problem. Could it be some setting needs to be turned on in the DB > itself? If not and you say I'm in trouble, then you're saying there's no > way to get around this? Is the problem with the indexing itself as you > suggested, i.e., SQL Server places an exclusive lock on it. I thought > SQL Server could handle multiple concurrent transactions like this (and > it is a serious problem for me if I can't fix it - who would have > thought). SQL Server can indeed handle multiple concurrent transactions, but a presumption is that you have a proper design. I would suggest that you post the code you have problem with as a start. Most likely we will ask to see table and index definitions as well. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|
Next
|
Last
Pages: 1 2 3 Prev: Count Deleted from Output Trick? Next: Using xp_fixeddrives on a linked server |