January 14, 2011 at 7:05 am
I seem to have munched something in my DB. Using SQL Server 2008 R2, with SSMSE and an Access 2007. The database is randomly unwilling to accept updates or deletes. Sometimes it works fine for a while, then starts acting up, sometimes as soon as the app starts it balks. I can add new records to the primary table via a view, but not change or delete them. And I can't even do that when logged onto the machine with an admin account and into SQL Server as sa.
The permissions on the view all seem to be okay and it does work, sometimes. I don't get any error from the server engine, not to the app nor to me as sa when trying to fiddle things by hand. It just sits for 60 seconds, then informs me that the query didn't complete because the timeout period expired, but no indication of why it's refusing to execute the commands.
A sample query is hereSELECT PodrobnostiAutoID, AkcesAutoID, EvidenceLetter, EvidenceNumber, EvidenceExtra, EvidenceGroup, GroupAutoID, OrderAutoID, FamilyAutoID, GenusAutoID,
SubGenusAutoID, SpeciesAutoID, SubSpeciesAutoID, SystemAutoID, SeriesAutoID, StageAutoID, SubStageAutoID, LithographicUnitAutoID, LithographicSubUnitAutoID,
ZoneAutoID, CountryAutoID, DepozitarAutoID, PDAutoID, ODAutoID, OriginAutoID, PocetKusu, OTHER_NO, Inventarizace, Poznamka, Description, RockType, Preserv,
UlozisteDocasne, Original, Authors, Lokalita, IDNeDruheEvidence, ts
FROM Podrobnosti
WHERE (EvidenceLetter = 'g') AND (EvidenceNumber = 3678)I use the "Edit top 200 rows" command on the table, then edit the sql manually by adding the WHERE clause to select the one row I want, try to change the contents of a field, and nothing. Just a timeout, eventually. I've tried restarting the entire computer, but no effect - sometimes a command like this works, usually not.
Can anyone suggest what I might look at? I don't even know where to begin troubleshooting something like this.
January 14, 2011 at 7:49 am
It sounds like it could be contention, probably blocking, from uncommitted transactions. While you're in that stuck position, run this little query:
SELECT der.blocking_session_id,
der.session_id
FROM sys.dm_exec_requests AS der
JOIN sys.dm_exec_sessions AS des
ON der.session_id = des.session_id
WHERE des.is_user_process = 1
This will just let you know that blocking is occurring. To really drill down, I'd suggest you get a copy of Adam Machanic's script called "Who Is Active"
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2011 at 9:06 am
Grant Fritchey (1/14/2011)
It sounds like it could be contention, probably blocking, from uncommitted transactions.
I think you're right. I never have problems when I work on the DB myself, but I'm usually most active evenings and weekends, when nobody else is here. I just now had one of my test users give it a whirl after kicking out everyone else, including myself, and it worked with nary a hitch. I've just run across some tutorials regarding the NOLOCK hint, and it sounds at the very least, like something I should be doing by default. I'm porting this from Access/Jet, so there are a lot of OpenRecordSet actions, and the reading seems to indicate that such a query can cause problems.
I can't dispense with such calls completely, the users are accustomed to being able to scroll through tables of data and don't want to give that up. Nor do I want to try forcing them - my job is to help them with their work, in the manner that suits them best, not dictate unnecessary restrictions to them. But all the scrolling is read-only; only when they select a particular record to edit do I want to lock it, so if I am careful to avoid contentions, this penchant for scrolling through tabular data shouldn't really be a problem, even though it may not be the cleanest way to access data in a DB purist's eyes.
This will just let you know that blocking is occurring. To really drill down, I'd suggest you get a copy of Adam Machanic's script called "Who Is Active"
I'm looking at it now, thanks for the tip. Clearly, I have much yet to learn. In the meantime, can you tell me if there is a simple way for my code to detect a lock? In the bad old days, DB engines (the ones I worked with, anyway) would report back with an error if a lock was encountered. It was then up to me to wait, discuss it with the user or whatever. Surely SQL Server must have some more sophisticated method of dealing with the situation than simply ignoring the request and forcing the user to wait for a timeout to expire.
January 14, 2011 at 9:24 am
I would be very, very, very careful about using NOLOCK. It seems like it solves problems, but it can introduce bad data. By that I mean, repeated rows, missed rows, really bad data. I generally never recommend using it to solve issues. Instead, learn how locking works and program around it. When you have to, use READ COMMITTED SNAPSHOT isolation level to reduce the amount of blocking. Not NOLOCK.
No, there is no reason to get alerts on locks, because they're a normal part of the operation. They're going on all the time, and you want them to. The only situation is when you have a long time blocking issue. Using WhoIsActive will help with that. There are blocking alert processes that you can set up to catch long blocks. Search in the scripts here on SSC.
Other than that, I'd suggest getting a copy of Itzik Ben-Gan's book on TSQL programming and start learning the fundamentals.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2011 at 10:28 am
Grant Fritchey (1/14/2011)
I would be very, very, very careful about using NOLOCK. It seems like it solves problems, but it can introduce bad data.
Hmm, yes, I see what you mean. I didn't realize at first that it could actually read data -partway- through an update cycle. That's definitely not what I want - fortunately, I had only started testing it and have only a few instances to remove.
When you have to, use READ COMMITTED SNAPSHOT isolation level to reduce the amount of blocking. Not NOLOCK.
Okay, I'm looking at that now. I see some squabbling about that as well - I'll have to study it a bit to make some sense of it all.
No, there is no reason to get alerts on locks, because they're a normal part of the operation. They're going on all the time, and you want them to.
Yes, I can see that. The default of 60 seconds should be plenty to let the occasional reading-at-the-same-time clash slip by. I wouldn't want to hear about those, nor do I want to know about a row that someone is editting, unless another user requests that same row for an edit, in which case I'd like to know about that either immediately, or at least, immediately when a save is attempted, rather than waiting for a timeout. JET handled that very nicely, with almost no effort on my part. Are you saying that without some involved interrogation script, there is no way to determine if a record is locked by another user other than to wait for a timeout (and -assume- that's what caused the timeout)? If that's the case, how do I know how long to set the timeout interval? If I put in a short interval for better response, I may time out unnecessarily, but it seems ludicrous to put in a long one 'just in case' and force the user to wait a long time to learn something that the SQL engine could answer instantly; i.e., "You can't have that because someone else is playing with it."
The only situation is when you have a long time blocking issue. Using WhoIsActive will help with that. There are blocking alert processes that you can set up to catch long blocks. Search in the scripts here on SSC.
I've downloaded the script and it installed instantly, no errors. Good initial indication. Looks like its going take some study to learn how to make use of it. I'm feeling a bit overwhelmed by all the stuff that's involved in SQL Server, but sticking my head in the sand is probably not going to advance the situation.
Other than that, I'd suggest getting a copy of Itzik Ben-Gan's book on TSQL programming and start learning the fundamentals.
Well, I've got SQL books coming out my ears, although I suppose another one can't hurt. Long stretch, though, from reading, and even understanding an explanation to putting in place a functioning system. I've been been involved in database programming since around 1975, but this is my first run with a full-blown SQL Server application.
January 14, 2011 at 10:58 am
pdanes2 (1/14/2011)
Grant Fritchey (1/14/2011)
I would be very, very, very careful about using NOLOCK. It seems like it solves problems, but it can introduce bad data.Hmm, yes, I see what you mean. I didn't realize at first that it could actually read data -partway- through an update cycle. That's definitely not what I want - fortunately, I had only started testing it and have only a few instances to remove.
When you have to, use READ COMMITTED SNAPSHOT isolation level to reduce the amount of blocking. Not NOLOCK.
Okay, I'm looking at that now. I see some squabbling about that as well - I'll have to study it a bit to make some sense of it all.
No, there is no reason to get alerts on locks, because they're a normal part of the operation. They're going on all the time, and you want them to.
Yes, I can see that. The default of 60 seconds should be plenty to let the occasional reading-at-the-same-time clash slip by. I wouldn't want to hear about those, nor do I want to know about a row that someone is editting, unless another user requests that same row for an edit, in which case I'd like to know about that either immediately, or at least, immediately when a save is attempted, rather than waiting for a timeout. JET handled that very nicely, with almost no effort on my part. Are you saying that without some involved interrogation script, there is no way to determine if a record is locked by another user other than to wait for a timeout (and -assume- that's what caused the timeout)? If that's the case, how do I know how long to set the timeout interval? If I put in a short interval for better response, I may time out unnecessarily, but it seems ludicrous to put in a long one 'just in case' and force the user to wait a long time to learn something that the SQL engine could answer instantly; i.e., "You can't have that because someone else is playing with it."
The only situation is when you have a long time blocking issue. Using WhoIsActive will help with that. There are blocking alert processes that you can set up to catch long blocks. Search in the scripts here on SSC.
I've downloaded the script and it installed instantly, no errors. Good initial indication. Looks like its going take some study to learn how to make use of it. I'm feeling a bit overwhelmed by all the stuff that's involved in SQL Server, but sticking my head in the sand is probably not going to advance the situation.
Other than that, I'd suggest getting a copy of Itzik Ben-Gan's book on TSQL programming and start learning the fundamentals.
Well, I've got SQL books coming out my ears, although I suppose another one can't hurt. Long stretch, though, from reading, and even understanding an explanation to putting in place a functioning system. I've been been involved in database programming since around 1975, but this is my first run with a full-blown SQL Server application.
You're in the right place. When you hit a snag, post a question. Tons of great people here ready to help out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2011 at 11:33 am
Grant Fritchey (1/14/2011)
You're in the right place. When you hit a snag, post a question. Tons of great people here ready to help out.
Thank you. I did have one more, actually, but I kind of buried it in my last response. Here it is again:
No, there is no reason to get alerts on locks, because they're a normal part of the operation. They're going on all the time, and you want them to.
Yes, I can see that. The default of 60 seconds should be plenty to let the occasional reading-at-the-same-time clash slip by. I wouldn't want to hear about those, nor do I want to know about a row that someone is editting, unless another user requests that same row for an edit, in which case I'd like to know about that either immediately, or at least, immediately when a save is attempted, rather than waiting for a timeout. JET handled that very nicely, with almost no effort on my part.
Are you saying that without some involved interrogation script, there is no way to determine if a record is locked by another user other than to wait for a timeout (and -assume- that's what caused the timeout)? If that's the case, how do I know how long to set the timeout interval? If I put in a short interval for better response, I may time out unnecessarily, but it seems ludicrous to put in a long one 'just in case' and force the user to wait a long time to learn something that the SQL engine could answer instantly; i.e., "You can't have that because someone else is playing with it."
January 14, 2011 at 12:46 pm
To see if a resource is locked, you do have to query the server. To see if a lock is blocking another user, again, you have to query the server. The query doesn't have to be involved, like the first one I showed you. But that's how you find it. You don't have to wait for the timeout either. What you can do is set up a script (search the scripts here on ssc) to check for blocking, record the time, wait 20 seconds, check for blocking again, see if the same process is still blocked, fire off an alert if so. It's not hard, just work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2011 at 4:29 pm
Okay, thanks, I'll look into that. One of the problems I'm running into is that Access locks up while waiting for a query to execute. If I hit one of these deadlocks, I'm out of action until the query request times out, which is frustrating - no way to do anything, except wait for the full timeout period, or abort Access altogether and restart the app.
I'd like to find out ahead of time if there's going to be a conflict and let the user know that, so they don't get stuck making a bunch of changes, waiting a long time with no sign of activity when they try to save the change, then finally being informed that update failed, with no clue as to why.
Locking at the level of single rows would be ideal, with a test to see if updates will be possible -before- handing the record to the update form.
I also tried the READ COMMITTED SNAPSHOT you suggested, but had to leave the building before the change finished. I'll look at it tomorrow to see if it took effect.
Appreciate the advice.
January 17, 2011 at 6:13 am
pdanes2 (1/14/2011)
Okay, thanks, I'll look into that. One of the problems I'm running into is that Access locks up while waiting for a query to execute. If I hit one of these deadlocks, I'm out of action until the query request times out, which is frustrating - no way to do anything, except wait for the full timeout period, or abort Access altogether and restart the app.I'd like to find out ahead of time if there's going to be a conflict and let the user know that, so they don't get stuck making a bunch of changes, waiting a long time with no sign of activity when they try to save the change, then finally being informed that update failed, with no clue as to why.
Locking at the level of single rows would be ideal, with a test to see if updates will be possible -before- handing the record to the update form.
I also tried the READ COMMITTED SNAPSHOT you suggested, but had to leave the building before the change finished. I'll look at it tomorrow to see if it took effect.
Appreciate the advice.
Just a point of clarification, what you're hitting is blocking, not deadlocks. Deadlocks are a completely different situation and have a specific error associated with them (look for "Chosen as a deadlock victim"). Stating deadlock when you mean block can lead to a lot of confusion.
The thing is, transactions should not be running so long as to be causing all these timeouts. I would investigate what's happening within the code to identify why locks are being held for so long.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 17, 2011 at 6:33 am
Generally if you have locks hanging around so long that the timeout (default 30 sec) is hit, you have either badly written code or poor indexing.
If a query encounters a lock, it doesn't mean it'll timeout. SQL will release the lock as soon as whatever locked it completes, and that should not be 30 sec or more.
You're not doing something like starting transactions, running a couple queries then waiting for user input are you?
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
January 17, 2011 at 7:19 am
GilaMonster (1/17/2011)
Generally if you have locks hanging around so long that the timeout (default 30 sec) is hit, you have either badly written code or poor indexing.If a query encounters a lock, it doesn't mean it'll timeout. SQL will release the lock as soon as whatever locked it completes, and that should not be 30 sec or more.
You're not doing something like starting transactions, running a couple queries then waiting for user input are you?
Badly written code would be my guess in this case - there just isn't that much data or such convoluted queries that SQL Server should be bogged down, even with several users on, and when I test the queries in SSMSE, they execute pretty much instantly.
This app is over ten years old, started life as an Excel spreadsheet, got ported to Access, functions and capacity periodically added and the latest move, dictated by constantly increasing content and functionality, porting to a full-blown client-server version. It's all my own work, but the conversion from single-user Access to multi-user Access/SQL Server is really bending my skull around.
I'm discovering all sorts of things where I open a recordset, usually as a form's record source, and that seems to be hanging onto things I don't want, even though I open it as read-only in Access. Read-only from Access apparently isn't enough to make it only glance at the data, but not lock it up. I can't go to only one record at a time in all cases; there are places where the users want to be able to scroll through lists, when they look for something, but are not sure exactly what. However, all such cases are read-only, where they fumble around, eventually find something and then want to edit that one record, in which case I want to open and lock that one record only. That's where I hit the timeouts - they edit the record, try to save and freeze.
I'm not using transactions at all. The changes here are minimal and always deal with only one table at a time. Mostly the users look at stuff or insert single records into a single table. Occasionally they want to change something, but again, it's always only in one table at a time, even though they may be looking at data pulled in from several auxulliary tables linked by foreign keys and relationships.
January 17, 2011 at 7:51 am
Grant Fritchey (1/17/2011)
I also tried the READ COMMITTED SNAPSHOT you suggested, but had to leave the building before the change finished. I'll look at it tomorrow to see if it took effect.
It seemed to hang, but then I found this gem:
ALTER DATABASE PaleoData SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
I ran it, some stuff did get rolled back and then the SET READ_COMMITTED_SNAPSHOT ON worked immediately.
Just a point of clarification, what you're hitting is blocking, not deadlocks. Deadlocks are a completely different situation and have a specific error associated with them (look for "Chosen as a deadlock victim"). Stating deadlock when you mean block can lead to a lot of confusion.
Ah yes, thank you. Certainly, proper terminology is important when discussing technical subject matter.
The thing is, transactions should not be running so long as to be causing all these timeouts. I would investigate what's happening within the code to identify why locks are being held for so long.
Yes, apparently I've got stuff in my code not handling this properly. But that's fine, I can work on that, now that I know what the problem is. When it just sits and does nothing, no error messages, it's sort of difficult to even start describing the problem. "It doesn't work" gets little sympathy in these forums, but now that I know what I'm doing wrong, I can fix it.
Thanks for the help.
January 17, 2011 at 8:01 am
pdanes2 (1/17/2011)
"It doesn't work" gets little sympathy in these forums
No, because it's near-impossible to offer useful advice for that. When we have some indication what's not working, that's a whole 'nother story.
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
January 17, 2011 at 8:47 am
GilaMonster (1/17/2011)
pdanes2 (1/17/2011)
"It doesn't work" gets little sympathy in these forumsNo, because it's near-impossible to offer useful advice for that. When we have some indication what's not working, that's a whole 'nother story.
It gets sympathy, but very little assistance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply