DB Locking and shuttong down CF

Report · Jan 19, 2009

This is a frequent problem for me. CF will shut down...error or on attempting to get to sites. My hosting company is blaming this on db locks. You know that annoying problem where a db locks and you have to clear it. So they are saying that the db lock is affecting all of CF. That is is freezing the process trying to get to the other dbs

HELP!

Sharon

Report · Jan 19, 2009

Sharon,

What type of DB are you using?

It sounds from your post that it might be MS Access, which is not a client-server database and ill-suited for web sites/applications. Access essentially treats a database like a file. For anyone to "use" the DB, it has to be checked out, or in Access' world, locked, so that multiple users are not accessing the database at the same time. By its nature and that of the web itself, Access is not well-designed for the web.

Actual client-server databases, such as MySQL (free) or MS SQL Server, do not experience such locking problems.

Of course, you may not be able to switch the DB or it may not even be Access. In either case, we'll need a bit more detail or some code snippets to possibly help.

Report · Jan 19, 2009

Sigh...I was afraid of that..It is in access.

so how easy is it transfer cf code from access to sql.. I am not that familar with sql.

Report · Jan 19, 2009

It's pretty easy, actually. In fact, you can upsize (I think that's what Microsoft calls it) an Access DB to a SQL Server 2005 database. This is about as painless as it gets. If I recall (it's been years since I had to do this), it's about as simple as "opening" your Access database in SQL Server Management Studio (the GUI application for working with SQL Server 2005).

For a conversion to MySQL (which is a free, open source DB, as opposed to SQL Server), it's also not too difficult. Here's a link to an article on MySQL about converting from SQL Server and/or Access to MySQL. Towards the end of the article, there is a list of utilities that can perform the base conversion for you.
http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html

There are lots of way to get the data across and, ultimately, none are too painful. Plus, once you get to MySQL or SQL Server, you can do a LOT more with your database!!

I hope this helps get you rolling...

Report · Feb 01, 2009

I do not remember having this problem when I was using MX or 5 or 4 or 3.1 But now that we are in 8 this seems to happen all of the time. Yes we have Access databases but there has to be a way around this lock problem other than converting everything to a different type database. I have about 200 Access databases and it does not do it all of the time but at least once per week we have to reset the CF8 ODBC Server Service. and sometimes we have to do the full Administrator depending how locked it gets. Are there any alternatives?