Database locked

Questions and Answers about the POS Pizza 6 and newer Database
Post Reply
User avatar
durbancic
Veteran Member
Veteran Member
Posts: 249
Joined: Sun Sep 30, 2007 9:13 pm
Location: Ohio
Contact:

Database locked

Post by durbancic »

Today I tried to run a report today for customer mailing lists of customers ordering this month and only 1 order. About 3-4 minutes into the report loading the screen was still blank and the phone started ringing. So I exited building the report and we took an order. After taking the order, I started the report again and it was just a few minutes again until we got another phone call. So I exited building the report again but this time the database was still locked. We were unable to log in to the system. I waited about 5+ minutes, still the database was locked and unable to log in. I stopped and tried to restart the server but was unable to restart because there was a file open in the TEMP folder. I think it may have been called TEST...???? but I can't recall for sure. I did not see any file in the TEMP folder at all, even w/ hidden files shown in windows. After a reboot everything was fine and we have not had any problems since.


By the way, I am continually getting a "Eror Step error=21" In the server log file. What is this?
User avatar
Scott
Site Admin
Site Admin
Posts: 2564
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Database locked

Post by Scott »

I would do 1 of two things. Run that report after hours (it has to look through a lot of data). Or try running it by running the management utility directly (not launching from POS) and then minimizing. It will lock the database during certain phases, but other things should still work while it is running (I will have to verify this). I could remove the exclusive access from the report, but it will slow it down even more (about 3 to 4 times slower).

A general rule of thumb would be not to run reports that cover large ranges of time, or reports like the customer labels (since it covers all ranges of time) when you are using the system. It should have closed the connection each time you aborted however. I am not sure what happened the second time, but I suspect that somehow Windows didn't close the connection. You could go to the server manager and close the connections manually, but waiting for it to do this itself will take up to the default timeout (30 min) if the connection isn't closed properly by the OS. Rebooting the PC will force all connections to close.

The error 21 messages are timeout messages when the server is waiting to read row data from a table that is in use by another connection. This is perfectly normal and isn't a "critical" error. It will retry after it times out until it gets the required data. The default timeout value for this is 3 seconds on the server side.

Scott
User avatar
durbancic
Veteran Member
Veteran Member
Posts: 249
Joined: Sun Sep 30, 2007 9:13 pm
Location: Ohio
Contact:

Re: Database locked

Post by durbancic »

I am now sitting on 10+ minutes of waiting for this report to load. The status bar at the bottom says: 1 of ? Building 1. Nothing is showing up on the screen, it is blank. The report is easy to minimize, and I can even go back to the management utility and after a few seconds the screen will redraw the icons, etc and I can access other sections in the management utility.
The report is: customer mailing labels within the last month and only 1 purchase. This is being ran on the server computer through the management utility only.

Shouldn't I be seeing some sort of progress that it is building more than the first page by now?
User avatar
Scott
Site Admin
Site Admin
Posts: 2564
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Database locked

Post by Scott »

It really depends on the number of customers in the database and the number of orders in the order history. The process is this: the report takes each customer and then locates the last few orders for that customer and then averages the time between orders and then determines whether or not the customer meets the requested criteria. This is done one customer at a time for each customer in your database. This could be a long process if you have a lot of customers and a lot of orders. It will build labels as customers are found that meet the selected criteria.

Again the time before you see anything happening depends on many factors. In some cases you may end up where no customers meet the selected criteria.

That report built very quickly here, only took about 3 seconds and from a remote computer (only 4500 customers in test database). I will load up the database with 53,000 customers and test it again as I am sure it will be much slower in that case.

Were you able to take orders okay while the report was building? If not then I will have to remove the exclusive access sections from the report building process which will unfortunately slow it down a lot more.

Scott
User avatar
Scott
Site Admin
Site Admin
Posts: 2564
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Database locked

Post by Scott »

I sent a test dll file to you that balances some of the searching on the (with 1 order) versions of the report. The new dll will cause the server to do some of the searching, and the client to do some of the searching at the same time. This should narrow down the number of items that the client has to look through in this one variation of the label generations.

Scott
User avatar
durbancic
Veteran Member
Veteran Member
Posts: 249
Joined: Sun Sep 30, 2007 9:13 pm
Location: Ohio
Contact:

Re: Database locked

Post by durbancic »

Just ran the report. It took 8 minutes to load 63 labels. While it was building it showed me 1 of 2, 1 of 3. And a couple minutes in I was able to view page 1 in its entirety. This database has 8013 customers.
User avatar
Scott
Site Admin
Site Admin
Posts: 2564
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Database locked

Post by Scott »

Ok, I will use that method in the update coming out at the end of the week.

Scott
vitospizza
Member
Member
Posts: 6
Joined: Sat Mar 05, 2011 2:03 pm

Re: Database locked

Post by vitospizza »

Is this still an issue in POS pizza? Is it common for the database to lockup?
User avatar
Scott
Site Admin
Site Admin
Posts: 2564
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Database locked

Post by Scott »

No the issue has been resolved. There was a conflict at the driver console which was resolved awhile back. I have had no reports of any further database lockups.

Scott
vitospizza
Member
Member
Posts: 6
Joined: Sat Mar 05, 2011 2:03 pm

Re: Database locked

Post by vitospizza »

I figured it was since there have been no messages here in over a month. Thanks for the information.
mrk90
Member
Member
Posts: 23
Joined: Thu Aug 28, 2014 3:28 am

Re: Database locked

Post by mrk90 »

Hi Scott,

My database locked up last night for the first time ever (in 11 months of fairly heavy usage on 4 licensed local terminals plus remote management access). The first indication on a user terminal was:

"...The above request was unsuccessful because the database is currently locked...."

Stopping the server and starting it again was unsuccessful on both occasions. Error message:

"Unable to open Test file in the Temp folder. Error=File is open"

Had to restart the service to get the server up and running. The log file returned "Step error=5" on both occasions.

Security log shows nobody logged in to the Management Utility at the time of either crash, so no heavy reports could have been running. There is one 'BAD LOG IN' on the POS Module which corresponds exactly to the first error entry at the time of today's lockup, but the same thing is not apparent for the lockup yesterday.

The server is a dedicated unit (2.9GHz Pentium 2GB RAM), however I was running a DVR software for 2 cams on it. I have shut this program down now to decrease demand on system resources and will continue to monitor through the evening.

Any thoughts on why this may be happening or steps I can take to troubleshoot it?


Database Log (crash 205856 11 Dec)
141211201924 Conn #1058 SK 1032 **********CS-4172.16.96.129
141211201949 Dcon #1058 Dropped
141211202235 Dcon #929 Dropped
141211205856 Eror Step error=5
141211211504 Eror Step error=5
141211211616 Conn #1115 SK 1032 **********CS-4172.16.96.129
141211211626 Eror Step error=5
141211211659 Admn Stop ==========<[ Stop Server ]>==========
141211211745 Admn Strt ==========<[ Start Server ]>==========
141211211745 Admn Flgs TrimLogManually=No
141211211745 Admn Flgs LogConnDcon=Yes
141211211745 Admn Flgs LogInvalidInMessage=No
141211211745 Admn Flgs CreateDatabaseAllowed=No
141211211745 Admn Data MaxChunkSize=500
141211211745 Admn Data ConnectionTimeout=30
141211211745 Admn FACT Non To Load


Database Log (crash 160541 12 Dec)
141212143541 Conn #838 SK 1032 UserACCOUNTS192.168.9.200
141212144150 Dcon #838 Dropped
141212160541 Conn #928 SK 1032 POSMANAGER192.168.3.103
141212160541 Eror Step error=5
141212160549 Eror Could not Send -- Device I/O error
141212160549 Dcon #618 Dropped
141212160552 Eror Step error=5
141212160604 Eror Step error=5
141212160640 Dcon #689 Dropped
141212160920 Admn Stop ==========<[ Stop Server ]>==========
141212161031 Admn Strt ==========<[ Start Server ]>==========
141212161031 Admn Flgs TrimLogManually=No
141212161031 Admn Flgs LogConnDcon=Yes
User avatar
Scott
Site Admin
Site Admin
Posts: 2564
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Database locked

Post by Scott »

"Unable to open Test file in the Temp folder. Error=File is open"
This almost sound like the OS locked files on the drive that shouldn't have been. This file is not a shared file, and is used only by the server service. Again it is not shared, so it should not be locked, nor should it be open which is what it was indicating. I would recommend a full reboot of that computer.

The only thing that could cause a lock issue would be running one of the UTILITY parts of the management tool like the DB Maintenance Tool, or an Import tool. These have to have exclusive rights to the data, and should not be run while the system is operational.

Scott
User avatar
Scott
Site Admin
Site Admin
Posts: 2564
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Database locked

Post by Scott »

One more thought, if you have AV software running on this computer, you should probably exclude the C:\POSPizza6\Server folder on the AV software. The server creates temp files and folders which the scanner could be holding open while it is scanning them.


Scott
mrk90
Member
Member
Posts: 23
Joined: Thu Aug 28, 2014 3:28 am

Re: Database locked

Post by mrk90 »

Thanks Scott - I actually did do a full reboot of the PC after each crash. Nor was the DB maintenance tool being, or importer being used at the time. However, I was using Avira AV on this PC. I have added an exception to the scanner. So far, so good now after 36 hours. Will advise if the problem resurfaces.
User avatar
Scott
Site Admin
Site Admin
Posts: 2564
Joined: Wed Apr 16, 2003 5:35 pm
Location: Western PA

Re: Database locked

Post by Scott »

There should be no other reasons that I can think of unless you're running an older version of the client like 6.1x or the server 1.0x. The file locks issue was resolved long ago.


Scott
mrk90
Member
Member
Posts: 23
Joined: Thu Aug 28, 2014 3:28 am

Re: Database locked

Post by mrk90 »

UPDATE: Within 2 hours of my last post the databased locked up again - but it looks like I may have found the problem. Stupidly, I was running automatic Windows updates on this machine. There were a bunch of updates released on the 9th of December - right about when this problem started. Most were MS Office related. However, I uninstalled the following updates and the system has been running absolutely flawlessly now for the past 120 hours:

Security Update for Windows 7 (KB3013126)
Cumulative Security Update for Internet Explorer 11 for Windows 7 (KB3008923)
Windows Malicious Software Removal Tool - December 2014 (KB890830)

Note - I am running Server v1.23 & Client v6.8.
Post Reply