August 23, 2012 at 2:41 pm
Hello Experts,
I am newbie when it comes to SQL server. I am currently working on SQL server 2008 and would like to a know few details about it.
In our env, when we try to read and write records out of a table at same time, we face issues of deadlock. Hence, we have to wait when one of the process is running before we read/write with another one. I am not sure if this is a problem with our env only or is it a limitation with SQL server. I dont think its a limitation as there must be OLTP systems outside that are built on SS 2008 and are working fine.
If that is the case, what are those possible settings that we are missing which avoids us to read and write at the same time. Any inputs are appreciated.
August 23, 2012 at 3:05 pm
mailrahul.p (8/23/2012)
Hello Experts,I am newbie when it comes to SQL server. I am currently working on SQL server 2008 and would like to a know few details about it.
In our env, when we try to read and write records out of a table at same time, we face issues of deadlock. Hence, we have to wait when one of the process is running before we read/write with another one. I am not sure if this is a problem with our env only or is it a limitation with SQL server. I dont think its a limitation as there must be OLTP systems outside that are built on SS 2008 and are working fine.
If that is the case, what are those possible settings that we are missing which avoids us to read and write at the same time. Any inputs are appreciated.
Well, that would not cause a deadlock. A deadlock is caused when 2 different processes have each done something that prevent the other from completing; i.e. they are each waiting on each other and therefore would wait infinitely. Reading and writing, writing and writing, etc. in the simplest sense will not cause a deadlock. If you can post the code you are using, we can help. Otherwise, it is near impossible for us to identify what is causing the deadlock. Although, since you are new to SQL Server... How do you know there is a deadlock?
Jared
CE - Microsoft
August 23, 2012 at 3:15 pm
Thanks for a quick reply. Although, I am new to SQL server, I have worked on other DBMS platforms.
Its not actually a deadlock that is happening, but the processes(read/write) go in a hang state. Neither of the process completes even after waiting for a long time.
This question is more generic sort of and not a specific code issue that I can paste.
I am more keen to know how could this happen and is that a limitation with SQL server.
August 23, 2012 at 3:18 pm
mailrahul.p (8/23/2012)
Thanks for a quick reply. Although, I am new to SQL server, I have worked on other DBMS platforms.Its not actually a deadlock that is happening, but the processes(read/write) go in a hang state. Neither of the process completes even after waiting for a long time.
This question is more generic sort of and not a specific code issue that I can paste.
I am more keen to know how could this happen and is that a limitation with SQL server.
It is not a limitation of SQL Server, it is a limitation of bad coding and design. Something is keeping each from finishing. It is up to you to figure out what that is. Do you ever get an error returned in this "general" sense? There are so many situations that could create this situation, all with different reasons as to why.
Jared
CE - Microsoft
August 23, 2012 at 3:36 pm
Not a limitation of SQL Server. Usually caused by inadequate indexing, inefficient code, poor design, mixture of them. That's not a deadlock, what you're describing is simply blocking.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2012 at 2:43 am
A deadlock will throw this exception:
Error Number: 1205
Error Severity: 13
Error State: 13
Error Message: Transaction (Process ID 78) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Are you getting those errors anywhere?
As Gila said, this is usually due to inefficient code.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply