Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
R0M
Starting Member
2 Posts |
 Posted - 2010-11-14 : 23:13:20
|
| Hi all,First of all apologies if this question has already been answered before, I did read through the FAQ use the search but my Google-Fu has failed me.I am ‘supporting’ an application that links into a Microsoft SQL Server 2005 server. The application keeps giving an error: [Microsoft][ODBC SQL Server Driver]Timeout expired when people try to update/insert data (select still works). Before this would happen every couple of months or so when someone had a record open and someone else tried to update it. Recently we had a lot of minor changes to the application and now it’s happening 2 or 3 times a week. When no one else is looking at the record. It just starts to lock up and the only way to unlock it is to restart the ‘SQL Server (MSSQLSERVER)’ service.I am pretty sure the application is at fault here but I would like to check out the server and see what quires are running or what/why data is locked. I have looked under SQL Server Agent>Error Logs but that is basically event viewer with a filter and there is nothing in there except stuff about backups. I also looked under Management>SQL Server Logs>Activity Monitor but I didn’t have much joy (To be honest I didn’t 100% understand what I was looking at there).Anyway I am looking for some advice on trouble shooting, where should I start? Are there any good tools you recommend? (I don’t have the source code for the application) One other thing that might be helpful is that our application uses one username and password to connect to the DB. It grabs the username of whoever is logged in and then checks it against a table to see what access they should have. So when I look on the server all the connections come with the user name Generic_User. (Yes I know this is the wrong way to do things but the application was started around 92, so pre-windows/SQL Server and has just had bits tacked on to it since then. I have been asking management to let me change this… but it costs)Cheers,MichaelP.s sorry for such a long first post. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-15 : 04:30:39
|
I don't think that the application managing the permissions is wrong.Sounds like the Application is holding a lock on a record at a physical level. If that is the case then it leads to all sorts of problems.The Current Processes will show what is being blocked, and by what other process. It is then possible to see what the other process is doing - which may enable you to work out "who" that person is.To fix it you are probably going to have to change the way that application "locks" resources. Either by changing to a Virtual locking system (store the UserID (i.e. the user as known to the application, not the database connection user) and the Table/Primary key in a table. Then you can have an Admin routine to "release" locks (and the application itself can say "Sorry, you cannot save that record because it is locked by Joe Blogs" - and then the users can probably resolve such issues beetween themselves - provided Joe Bloggs hasn't gone to lunch!Or you use an optimistic locking system - whenever a record is saved a column is updated (either you can increment a VersionNumber column, or you can use a ROW_VERSION datatype (which SQL will change at every update) - then the application has to retreive the value of this VersionNumber column with the SELECT and at the UPDATE it has to also add a check to the WHERE clause - e.g.UPDATE MyTableSET Col1 = ...WHERE MyID = 1234 AND VersionNumber = 5678 then if the current VersionNumber is NOT 5678 zero rows will be updated (which the application will need to detect and report back to the user - the user then has to have another go)Pros and Cons for both approaches. |
 |
|
|
R0M
Starting Member
2 Posts |
Posted - 2010-11-16 : 22:57:09
|
| Hi Kristen,Thanks for the quick response. The Idea of a Virtual locking system sounds good, Is it possible to set a lock timeout (say 2 hours?) but I don’t think the Issue is that Joe Blogs has the file locked and hasn’t closed it. But that Joe Blogs opened the file, closed it but the application didn’t disconnect him correctly and now the file is still locked and no one can unlock it.I will be getting the source code for the application soon so that may shed some light.Cheers,MichaelP.S I guess the application is not doing it the ‘wrong’ way but it’s just that I come from more of a computer security back ground (*sarcasm* Who would have guessed I’m not an SQL pro hahah…) I don’t like the idea of having one account that we can’t disable with read/write access and a hard coded password. Some of the staff know the password. If they knew how / wanted to they could open Management Studio (or any other SQL client I guess) use the Generic_User account to start changing things and we couldn’t see who it was or be able to stop them without breaking the application. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-17 : 03:41:50
|
| "The Idea of a Virtual locking system sounds good, Is it possible to set a lock timeout (say 2 hours?) "You can build it how you like, but in that scenario you are creating the additional complexity that Joe Blogs THINKS he has the record locked, but actually it has been released. That's also true of releasing it by other means (e.g. an Admin user can delete an entry from the virtual-lock table) - so you just need to be sure that your application is bullet-proof on that score (lock is auto-released at 2 hours but a record update started at 1hr 59mins 59seconds and is still in progress ...)Optimistic locking doesn't need any of that paraphernalia, but does mean that people will have their changes disallowed, and have to repeat them, when there is a collision - if that is once a year fine! if every few minutes then it will annoy the operators intensely of course ..." I don’t like the idea of having one account that we can’t disable with read/write access and a hard coded password."It should be hardwired in the program and not used by anyone else (which is a bit difficult to people knowing the details ... someone has to set it up, and be able to set up a replacement if the database is recovered etc - but that's not an excuse for using it - if "lots of people know the password" then maybe treat that as a security breach/risk and change it in the App? and restrict the number of people it is known to)Nothing wrong with the old system of having a sealed envelop in the safe - if it used for "emergency login" then a new password has to be created, and a new envelop locked in the safe ...) |
|
|
|
|
|
|
|
|
Looking for some trouble shooting suggestions