Is SQL Server viable in a scalar environment?
-
Hi
I would appreciate advice if anyone is successfully using SQL Server in an environment with high concurrency.
At the simplest level we have difficulties maintaining performance with simple concurrent table updates. No matter how much tuning we attempt, SQL Server has too strong a tendency to escalate to page locks. This rapid escalation to page locks causes a high frequency of unnecessary deadlocks had the locks remained at row level.
I am beginning to wonder if it is just not possible to do high-frequency, concurrent updates to a SQL Server table. For example just two servers attempting to do only 10 concurrent updates (update x where primary_key_col = y) result in 50% deadlocks. The servers use local transaction which are implemented by the MS jdbc drivers.
It seems the issue can only be resolved if Sql Server can be forced to remain at row level locking. Hints don't be able to manage this.
If anyone has experience of high concurrency running well on SQL Server I would be glad to hear about it.
Thanks
Glyn
-
I have worked with Sql Server in some of the highest concurrency enviroments in the nation. Some of them have been financial systems, some E-commerce, some order placement, some even dating service, believe it or not.....all of them have been fine.
The largest killers of concurrency that I have found, have been poor table structure, bad indexing strategies, or poor application design.
When you say "No matter how much tuning we attempt, SQL Server has too strong a tendency to escalate to page locks.", is this statement coming from an application developer, A Database admin, a database architect, a newbie, what? I cannot believe that someone trained in databases, and having worked with them for a good length of time wouldn't be able to quickly tell you why your queries are locking, and suggest a strategy for changing the behavior your experiencing. This is actually a pretty simple thing to track through. And I sincerely do not mean to offend, I am just asking who is trying this tuning, and do they actually have the skill to be doing it to start with. Perhaps you need to bring in a consultant to evaluate your enviroment, and give you some strategies for going forward. If that is not possible, perhaps you could post the table structure, indexes, queries, and the group could help.
-
If you have excessive locking, then you likely either have poorly written queries, poor indexing (or badly fragmented indexes), or both.
I've fixed numerous procedures that have a tendency to deadlock either by changing the procedure or adding or changing a index on the table.
I'll second the comments about posting the code. It's difficult to do performance tuning at a distance, but we can offer suggestions.
(p.s. to give you some idea, my server handles a million stored proc calls in an hour, averages 24000 lock requests/sec, average lock wait time less than 0.1s and at worst 3 deadlocks a week.)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks very much for your reply. It certainly gives me hope. And I will confess that I am an application developer rather than a DBA but have spent a fair bit of time on db tuning over the years.
I can give you an example of one of the issues: Using JBoss to manage the persistence of an entity. JBoss creates a simple table with a 1 to 1 mapping to the EJB structure. I have made sure that has a primary key on it. As the EJBs are created and destroyed JBoss makes simple insert and delete statements to the table. I can tune this to the extent that it does not try and do a select beforehand (which it will normally try to do to detect whether there is a primary key violation it will need to handle). To create an entity bean and set the properties the application server ends up calling these statements (verified from running a trace). Insert into a_table (x) values (1) .. update a_table set y = 2 where x = 1 .. update a_table set z = 3 where x = 1 etc. for all properties.
For this process there is a PK on column x so sql server should only need to do a rowlock on the record being inserted and then updated. However if we try and do 10 concurrent inserts then somewhere around that point sql server escalates this to a pagelock and we deadlock on records that should not be affecting each other.
In this case the procedures are fairly simple so there is not much tuning we can do to the table or the sql. Instead we have been trying hints etc.
There are other examples in the application using the connection pools and heavy concurrency where this is happening too. I fully acknowledge your experience and the point you make though. We have been looking at our indexing and queries since this problem emerged. I just wanted to get feedback on whether anyone out there was succeeding where we are failing and I am grateful for your feedback. I do think there is a bit of a fundamental issue with sql server here though. For example there are a lot of people having this issue with JBoss and Sql Server who do not have it with Oracle say.
-
Don't use hints unless you've tried everything else, and even then think twice. The optimiser is often better at picking a query plan than you are.
If 10 users each lock a row, then SQL will place 10 row locks. It can't escalate all 10 into a single page lock, cause they're different spids. If 1 user locks 10 rows then he may get a 10 row locks or he may get a page lock, depending where the rows are.
If SQL's taking a lot of table locks, it may indicate insufficient memory
Fow fragmented are your indexes? If the pk clustered or nonclustered?
I notice that it's doing multiple updates for a single row. That's going to take a while and the lock will be held until the end. Can't you do a single update and set all fields in one go?
(UPDATE table_a SET y=2, z=3, a=4 WHERE x=1)
Note that java stuff often uses implicit transactions, and locks taken in a transaction (exclusive locks) are held until the transaction is committed. (I've got some java stuff here and it's the worst behaving stuff on my server). Look at where the tranactions start and where they're committed. Also look at the isolation level that the connections are using.
What are the two processes that are deadlocking, and what does the deadlock graph look like? (written into the error log if you have trace flag 1204 on)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Point taken about the hints. I agree.
The pk in the example here is clustered. And it is a table with no other relationships or indexes.
You may have touched on two of the reasons for the problem here. 1/ that as things stand we don't have control over the fact the updates are not done in one go (that is the way container managed persistence is handled by the application server) and 2/ that java is quite heavy handed with the transactions. A set of procedure calls made from java with a tx isolation of required will hold one transaction across them with (I think) an isolation level of READ_COMMITED.
But all of that notwithstanding is that reason enough for roughly the 10th insert and multiple updating transaction to take a pagelock? Does the combination of insert and multiple updates across one transaction correspond to what you would call poorly designed queries? If so, in this example, we might have to take control of the persistence and manage the sql ourselves.
Thanks.
-
If it thinks it more efficient, and perhaps 10 is the threshold, locks will be escalated.
I'd be interested in why you cannot manage the updates in one statement. Perhaps stating that issue here and someone may have an idea to help there.
-
Thanks Steve. However the decision that Sql Server makes that it is more efficient is what causes the deadlock. Because if it decides at, say, 10 and those 10 are all concurrent, then if there are two pagelocks there is deadlock. And It seems to me that SQL Server is more ready to make that decision than other RDMSs.
You are right about asking why the updates are not in one statement. That is one for JBoss. Certainly if they were in one statement perhaps SQL Server would not make the decision to use a more 'efficient' lock. But it still seems a low threshold to me. Especially as the column is primary keyed and there are no complex queries involved.
-
#include usual and customary warnings about using undocumented features (test outside of production, have good backups, etc.)
...that being said...
There is an undocumented trace flag that will disable lock escalation. I wouldn't use that as the solution so much as a way to dig further into the problem by moving page locks out of the way as an issue. (continue with the warnings about undocumented features - misusing undocumented trace flags can can solve your locking problems by ridding you of data to deadlock)
I'm curious as to why that would be a problem when you are just updating a single row. I've had lots of users pounding away at low-spec servers without ever hitting deadlocks due to any reason other than two processes accessing objects in opposite orders and getting into a deadly embrace. I hope you post your results back to this thread.
I don't have my reference with me right now, so I don't have that trace flag number, I hope you can find it with a little searching.
-Eddie
Eddie Wuerch
MCM: SQL -
Locks are escalated according to memory availability. It's definatly not a hard threshold of 10.
Rules for preventing deadlocks.
1) Keep your transactions as small as possible
2) Access tables as seldom as possible and always in the same order.
The way JBoss works violates both. If you can take control of the updates, putting in into a stored proc then your deadlocks should disappear.
Also check your memory availalbility. Excessive lock escalation can indicate insufficient memory available in the lock buffer.
To give you an idea, I've seen 25000 row locks before, from one connection.
Have you got the deadlock graphs? It would help massively in finding the cause of the problem. Also table structure and complete chain of commands in the connections that get deadlocked.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I agree it's not a hard threshold. Although in testing the threshold number is lower than I expected on the database server we are testing on (2GB RAM). Typically only just in double figures.
I also agree the two tenets described in the post above are key. Calling procedures from java middleware means the duration of the entire procedure will be held in a transaction so it is not possible to perform smaller transactions in the procedure. But with small, atomic procedures called and committed you should be able to adhere to those principles. However even with this we find that in a multi-threaded, multiple connection, test scenario we can get deadlocks fairly quickly as we up the concurrency.
But to make further sense of this we will do as you suggest and show the table structures, chain of commands and deadlock graphs.
Thanks for the responses on this.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply