Problems, problems

Quinino wrote: For the last couple of days I keep receiving the following error message when accessing album's pages on the DB "Microsoft SQL Server Native Client 11.0 error '80004005' Transaction (Process ID 600) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. /artist.asp, line 13 " Is it only me ?  I wonder if any OS update (or other change on my PC I'm not aware of) is responsible - or else was it some modification on the site ... Any clarification would be useful, thanks for your answers.

It keeps happening, now more as a rule than as an exception - again, are you experiencing the same ? It could be widely useful to understand if it's a site problem...

Really ???? Makes no difference to me, just checked to confirm.
How long do you notice this, for me it's about a week now and seems to be getting worse
(another inconvenience to bring us down, it seems...)

I, too, have gotten it from time to time lately. 30% of the time sounds about right.

I almost wished it was only me, at least there was a chance to be able to repair on my own...
Now, I'm just guessing, but which browser are you guys using - mine is Firefox 56 (with NoScript, Mcafee WebAdvisor, Colorful Tabs and Download StatusBar add-ons), could this somehow be the culprit ?

Yes, but only the first album I try to access, then it stops for the test of my session.

Now I am getting the error, started yesterday, worse today.  Occurred on iPhone and on Windows 7 laptop using Google Chrome.  First Crapcha, now this....  :(

Bad News are back
Four more hours have passed with intense use of the DB and now the error made an unexpected comeback
Provisional conclusion to be made: all points to the cause being on the site, not a permanent situation but dependent on some condition that varies with time ... volume of user traffic creating some kind of bottleneck ? m@x doing some update / backup / whatever ?

From my limited understanding it's a relatively common database error, but there should be an error handler in the SQL code that retries the transaction a certain number of times if it fails.

I just replied to the "deadlock" matter on another thread. 

I'm an ex database administrator, on Oracle, not SQL server, which is what this site is hosted on. However, the same principles apply as Oracle and SQL (and most relational databases) operate in much the same way. 

When a query to a database object fails.... ie, when you try to look something up and the network drops, for example, you end up with a lock. Locks are part of DBA life, the database basically marks data as "not writeable" for a while as it stops multiple data updates to the same location. You get a certain number of locks available to the system.

Normally, a background process clears up locked data after a while, but in some cases, if you get lots of locks, the background process has failed or a serious lock occurs. If you get shed loads of locks, the DBA intervenes, overrides the manual process and does it all manually. Except Prog Archives won't have a DBA looking after it, it costs too much. 

Since lots of locks are occurring, a sensible thing to do is for the ISP to (a) shut the SQL server database down and then (b) re-start it. This should resolve all the locks. Probably. ;-)

Longer you leave a locking up database, worse it gets. I'd schedule in an auto shutdown / reboot of the database at least once a fortnight, to be honest. That's easy to do. 

No chance. ;-)

Prog Archives will be hosted on a small(ish) SQL server database in an ISP's server room. There will be a very large number of servers hosting masses of websites and databases. No DBA. DBA's cost big money and are solely for "mission critical" database systems. I used to work for IKEA, Maersk Sealand, Seawheel, the UK NHS Service, and for people who paid thousands per month for an expert to keep an eye on their data. 

So no one will be looking. 

Whoever owns the site can request a database reboot, of course. They'll hardly fall over themselves at the ISP unless the site is completely down and they may lose money. 

This is an almost inevitable progression of databases. As they get older and more bloated with rubbish, and rarely rebooted, internal errors occur, locks start mounting up, performance starts decreasing. So it goes.