|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
ADO.NET Transaction LockingI have a transaction that is Updating or inserting multiple tables(12 or
more) This transaction takes about a 2 seconds to perform. During that two seconds. We are getting timeouts expired errors from other clients requesting records from the same 12 or so tables. What setting is there to say only lock the changed or new records do not lock the entire table which is what appears to be happening? I am using SPs to do all inserting and a multiple command objects. What is occuring in the ado.net lock is it a table lock page lock or record lock. These are simple insert or updates, except for one procedure that validates the data. Usually, this works fine without anything special. I don't understand why if
the entire transaction takes 2 seconds, why other clients are timing out, even if the transaction is locking the whole table? That should be quick enough, so that the other clients can just wait the 2 seconds, and then the data they want should be available again. In general, make sure your updates and anything your stored procedure is doing, is using the table index for its queries, so table scans aren't being done. Show quote "scott" <sc***@microsoft.com> wrote in message news:2F64AACF-34DE-41EA-8987-70FF2A5905F2@microsoft.com... >I have a transaction that is Updating or inserting multiple tables(12 or > more) This transaction takes about a 2 seconds to perform. During that > two > seconds. We are getting timeouts expired errors from other clients > requesting records from the same 12 or so tables. What setting is there > to > say only lock the changed or new records do not lock the entire table > which > is what appears to be happening? > > I am using SPs to do all inserting and a multiple command objects. > > What is occuring in the ado.net lock is it a table lock page lock or > record > lock. > These are simple insert or updates, except for one procedure that > validates > the data.
Show quote
"Marina Levit [MVP]" wrote: My updates are primary key based and my inserts are just that inserts and > Usually, this works fine without anything special. I don't understand why if > the entire transaction takes 2 seconds, why other clients are timing out, > even if the transaction is locking the whole table? That should be quick > enough, so that the other clients can just wait the 2 seconds, and then the > data they want should be available again. > > In general, make sure your updates and anything your stored procedure is > doing, is using the table index for its queries, so table scans aren't being > done. > > "scott" <sc***@microsoft.com> wrote in message > news:2F64AACF-34DE-41EA-8987-70FF2A5905F2@microsoft.com... > >I have a transaction that is Updating or inserting multiple tables(12 or > > more) This transaction takes about a 2 seconds to perform. During that > > two > > seconds. We are getting timeouts expired errors from other clients > > requesting records from the same 12 or so tables. What setting is there > > to > > say only lock the changed or new records do not lock the entire table > > which > > is what appears to be happening? > > > > I am using SPs to do all inserting and a multiple command objects. > > > > What is occuring in the ado.net lock is it a table lock page lock or > > record > > lock. > > These are simple insert or updates, except for one procedure that > > validates > > the data. > > > they only insert on record at a time. I was using the default command timeout of 30 seconds I up it to 60 seconds which I think is way over kill. Not sure what else to offer it is realy basic code BeginTransaction call Proc1 to n CommitTransaction if an error occurs anywhere in between rollback transaction. The only thing that may be out of the ordinarily is in a seperate connection I log events telling me what step it is on, this is how I know it is taking 2 seconds or more. So these deadlocks keep going, long after the transaction completed? If the
transaction completes in 2 seconds, the previous request that was made during the transaction, still continues to wait for 58 seconds, and then says it timed out? That sounds odd to me. Do you have a simple re-pro case of this? Show quote "scott" <sc***@microsoft.com> wrote in message news:825A949E-D6DA-4FFC-9985-96A69FD1569A@microsoft.com... > > > "Marina Levit [MVP]" wrote: > >> Usually, this works fine without anything special. I don't understand why >> if >> the entire transaction takes 2 seconds, why other clients are timing out, >> even if the transaction is locking the whole table? That should be quick >> enough, so that the other clients can just wait the 2 seconds, and then >> the >> data they want should be available again. >> >> In general, make sure your updates and anything your stored procedure is >> doing, is using the table index for its queries, so table scans aren't >> being >> done. >> >> "scott" <sc***@microsoft.com> wrote in message >> news:2F64AACF-34DE-41EA-8987-70FF2A5905F2@microsoft.com... >> >I have a transaction that is Updating or inserting multiple tables(12 or >> > more) This transaction takes about a 2 seconds to perform. During >> > that >> > two >> > seconds. We are getting timeouts expired errors from other clients >> > requesting records from the same 12 or so tables. What setting is >> > there >> > to >> > say only lock the changed or new records do not lock the entire table >> > which >> > is what appears to be happening? >> > >> > I am using SPs to do all inserting and a multiple command objects. >> > >> > What is occuring in the ado.net lock is it a table lock page lock or >> > record >> > lock. >> > These are simple insert or updates, except for one procedure that >> > validates >> > the data. >> >> >> > My updates are primary key based and my inserts are just that inserts and > they only insert on record at a time. I was using the default command > timeout > of 30 seconds I up it to 60 seconds which I think is way over kill. Not > sure > what else to offer it is realy basic code BeginTransaction call Proc1 to n > CommitTransaction if an error occurs anywhere in between rollback > transaction. The only thing that may be out of the ordinarily is in a > seperate connection I log events telling me what step it is on, this is > how I > know it is taking 2 seconds or more.
Show quote
"Marina Levit [MVP]" wrote: Unfortantaley no I cannot reproduce the error since it is happening at most > So these deadlocks keep going, long after the transaction completed? If the > transaction completes in 2 seconds, the previous request that was made > during the transaction, still continues to wait for 58 seconds, and then > says it timed out? That sounds odd to me. > Do you have a simple re-pro case of this? > > "scott" <sc***@microsoft.com> wrote in message > news:825A949E-D6DA-4FFC-9985-96A69FD1569A@microsoft.com... > > > > > > "Marina Levit [MVP]" wrote: > > > >> Usually, this works fine without anything special. I don't understand why > >> if > >> the entire transaction takes 2 seconds, why other clients are timing out, > >> even if the transaction is locking the whole table? That should be quick > >> enough, so that the other clients can just wait the 2 seconds, and then > >> the > >> data they want should be available again. > >> > >> In general, make sure your updates and anything your stored procedure is > >> doing, is using the table index for its queries, so table scans aren't > >> being > >> done. > >> > >> "scott" <sc***@microsoft.com> wrote in message > >> news:2F64AACF-34DE-41EA-8987-70FF2A5905F2@microsoft.com... > >> >I have a transaction that is Updating or inserting multiple tables(12 or > >> > more) This transaction takes about a 2 seconds to perform. During > >> > that > >> > two > >> > seconds. We are getting timeouts expired errors from other clients > >> > requesting records from the same 12 or so tables. What setting is > >> > there > >> > to > >> > say only lock the changed or new records do not lock the entire table > >> > which > >> > is what appears to be happening? > >> > > >> > I am using SPs to do all inserting and a multiple command objects. > >> > > >> > What is occuring in the ado.net lock is it a table lock page lock or > >> > record > >> > lock. > >> > These are simple insert or updates, except for one procedure that > >> > validates > >> > the data. > >> > >> > >> > > My updates are primary key based and my inserts are just that inserts and > > they only insert on record at a time. I was using the default command > > timeout > > of 30 seconds I up it to 60 seconds which I think is way over kill. Not > > sure > > what else to offer it is realy basic code BeginTransaction call Proc1 to n > > CommitTransaction if an error occurs anywhere in between rollback > > transaction. The only thing that may be out of the ordinarily is in a > > seperate connection I log events telling me what step it is on, this is > > how I > > know it is taking 2 seconds or more. > > > twice a day. The sequence is such that it is a timing issue.  |
|||||||||||||||||||||||
Other interesting topics