We use the following code to run sync from application on mobile device:
OracleSync sync = new OracleSync();
sync.UserName = "test";
sync.Password = "test";
sync.ServerURL = "192.168.71.208";
DateTime now = DateTime.Now;
On some devices sometime we get error "timeout when waiting for a lock <table_name>"
All previous operations (including sync) on this device are successful.
At the same time the msync does not show any errors and runs sync process ok.
I can run insert and select statements for this table.
Explain to me what locks may appear in single-user database and how to avoid them
I get a fair amount of this happening when I am doing a fair amount of development and I am going against an oracle lite database. Even though I commit my changes and I am pretty sure I don't have any uncommited transactions, I still see it. I can't reproduce it every time. I usually have to restart my PC to release the lock. Ideally though, I would like to be able to see what transaction is locking the table and either kill the process or perform a commit on the open transaction. From metalink
During synchronization of a Webogo/Win32 Client the following error can occur :
"ERROR",POL-3264,"01/09/2007 19:06:40","Timeout when waiting for a lock "
"ERROR",POL-3264,"01/09/2007 19:12:08","REPROC:Timeout when waiting for a lock "
A snapshot object was locked
POL-3264 means another SQL connection locked the snapshot object.
For example, on S11U1.
Run mSQL "select * from ORD_MASTER for update;" and do not commit.
Then run mSync, mSync will finish with the POL-3264 error.
Because the ORD_MASTER table is locked by mSQL with exclusive block mode other connections cannot update ORD_MASTER.
1- Before performing a sync, commit all changes made.
2- Exit your application and retry sync manually with the msync tool
3- Restart your application and sync
This might be obvious, but it not clear from the problem description above, but you have exited from msql before running msync (or closed the connection to the application database within your app before calling the sync APIs). You cannot connect to both at the same time.
If you have come out of msql, then where are the databases stored on the mobile device? main memory or storage card? if on storage card, do you have flush after write set in the polite.ini file.
Msql should do an automatic roll back on exit if no commit, but may be different in application code
The same message i got when im using auto sync. It gave me lot of such issues. I wonder you have any autosync enable publication items. Have you enable any conditional based sync options in publication. ie. When AC power detected,When network band with >.
But when i swtch off the autosync and revert back this issue dissapear.
this is an old thread but i get the same issues with timeout when waiting for a lock:c$info and i dont know what is causing it.
i am using win ce on windows mobile 6.1 ppc60 with olite 10.3.0.3
i dont have a table c$info.. so i assume it is a olites one
in the beginning i though the it was happening because we added some new snapshots (new pub items) in the existing app.
so we unistalled all clients, deleted all users dropped the publication and recreated all from start.
i still get the timeout error though.
can you please offer some advice?
let me assist a little
1)there is no msql running on the handheld (it is not even installed)
2)i am using the c sharp interfaces (synchronize(args)..)
3)the timeout when waiting for a lock comes when the sending bar ends but the receiving bar doesnt start
4)i am not using transactions in my app, i am doing normal inserts selects, updates but i do not create transactions (ofc each query executed is a transaction by itself but i mean that i do not set the autocommit to on of ..rollback etc)
5)the database is saved in the sd card, you said to set the flush after write gary, what exactly must i write in the polite.txt of the client on the handheld?
6)if i run msync then i get the same issue
7)if i restart the handheld still the same
8)our clients use gprs to synchronize, it is ofter that they try to synch, and it fails due to connectivity issues. does this have to do with anything?
9)in our c sharp code after executing a query you dont have to execute a commit. the data are stored normally
i apologize for not creating a new thread but because the issue is similar its best to keep all solutions in one thread than making multiple threads.
i tried many things, the thing that actually made the it work was:
1)delete all bin files in orace folder
2)open msql run an update query on all rows in order to reaply them
3)sync (successfull this time)
5)i had records in the error queue because i had performed update and the data did not exist so i changed the update to insert and execute
6)entries went in in queue and applied.
ok so the question is what do the bin files have to do with the timeout when waiting for a lock:c$info table? why did i have to delete them in order to make it work?
the exact message i got was
cns 9024:10054 error timeout when waiting for a lock:c$info
cns 9024 stands for network problem which is wrong because network was operational and i could access the server at all times.the 10054 dont understand, if it is cons 10054 i think it has to do with window size (according to the manual) but again i dont understand what that means. i use window size in my sequences but that doesnt have to do with c$info.and my window size in sequences is 500.
is this a bug?
Edited by: vasileios on 05-Aug-2010 07:20
c$info is one of the control tables in the conscli database (not the application database)
It has two columns name and value, and is used to store system values/flags as parameters. when you do a sync, concli is opened first to retrieve some of this data, and then closed and the application db file opeend for the actual sync.
Probably the most used flag is name=SendFileExist This will normally be set to 0, but if a previous sync had successfully completed the composing phase (where it extracts the changed data from the client database) but failed on the sending (upload) phase (for us mainly newtork connection issues) then a file snd<nnnnnnn>.bin where <nnnnnn> is normally a number will have been created. This is the extracted data. At the end of the composing phase the SendFileExist value is set to 1 to indicate that this file already exists.
next sync processing phase sees the 1 in SendFileExist and appends to the existing snd file rather than creating a new one which would lose the data previously extracted.
we normally have the problem where the snd file is lost or corrupted or is missing an EOF marker (causes syncs to hang on upload of c$seq_clients). If the snd file is removed then you need to update the conscli database to reset SendFileExist back to 0 otherwise you get an internal error message (need to update the application db as well to recover the changed data).
As your fix seems to be
a) delete snd files
b) update the application db
it looks to me like something in your sync calls are not correctly commiting the connection to conscli before it closes, and this is giving your lock and leaving SendFileExist set to 0 even though there is a file present.
thks gary once again,
you are refering to SendFileExist . you have refered in this flag in another thread also.
there is no such flag in my c$info i am using 10.3.0.3 on windows mobile 6.1
the only thing that exists in my c$info is:
the synctimes increases every time i sync.
1)are you sure that that flag exists on win ce client?
2)i was talking to a oracle support rep and he told me that this problem might be caused by having opened database connections from my app while performing synchronization throug the
c sharp synchronization apis. i dont know if that is the case. in your applications you close all database conenctions before synching? is there a case in your systems that a database connection is opened (from an application) while the sychronization takes place?
what i think is definetly a bug is the fact that the lock doesnt get unlocked after a handheld restart.
3)i am interested in that bin file which seems to be causing the issue. you said "we normally have the problem where the snd file is lost or corrupted or is missing an EOF marker (causes syncs to hang on upload of c$seq_clients). " first of all i am not sure if the win ce creates snd or something else files but in any case i have some other cases with problems in the c$seq clients table. i have already raised a bug on that with a ora error that is thrown in the server when the client performs some unsuccessfull synchs while increasing the sequences and then the c$seq throws an error in the server. but in any case i dont think that these issues are connected to the timeout when waiting for a lock.
4)finally something also weird. although my msync shows the error i mentioned, the olsync log shows POL-3264 timeout when waiting for a lock. i know that this is related to some autosync things but i am not using autosync in my application whatsoever
As good practice, you should be closing your database connections before you sync. Also, do you know what table is locked? Check your code and see where you are doing a commit or not doing one...
we are still on 10.2 due to our antiquated app servers and even more antiquated legacy apps that will not let us upgrade to the right version for 10.3
even in 10.3 unless you are using background syncs that happen in thier own way, i would always make sure that you close the db connections before initiating the sync apis, if nothing else this ensures that you have committed and flushed the data.
as you spotted one of the other values in c$info is the sync increment counter which looks to be still there in 10.3. If you get the timeout due to lock often, it migh be worth looking at the value before the sync and see if it has changed when you get the message, this may help narrow things down.
The 10.2 problem (may be fixed in 10.3) we get every couple of months is fairly obvious when it happens. User starts sync, which normally takes 5-10 minutes (we use queue based refresh so there is a processing gap between the upload and download). an hour later the mobile manager is still showing them as an active session, on upload (user has cancelled or thrown his PDA at a wall ages before this). the publication being shown is c$seq_pub_items (happens to be the last PI to upload normally). The session will remain stuck like this until the server is reset, killing the session makes no difference.
From the effects (no documented proof on this) we are sure there is a problem in the physical upload file missing the EOF marked and causing the upload to go into a loop. The only solution is to run scripts on the client device to clear the upload file, reset the data and then sync again.
No idea if fixed in 10.3, but when we were using normal publication items we used to get sequence problems due to some poor code in c$default_pub_qpkg (this is where the sequences are processed) and also due to the fact that there is no error queue for the system tables, so sequence updates were lost if the data went to the error queue
We mainly use windows CE devices (HP PDAs, with some old Dells hanging around) at the moment, but are likely to move to eon or viliv small tablets with windows 7 by the end of the year, mainly driven by the database size limitations ce imposes
thank you all for your answers.
i never do a commit or rollback in my code. i use the c sharp interfaces with .net compact framework 3.5. i execute the sql queries but never execute a commit. i dont use a transactions so i dont need one. i guess that by executing a query , sql lite commits it immediatly. the sql queries work correctly.
btw we use intermec cn4 and motorola mc55 handheld devices with windows mobile 6.1 and .net cf 3.5 combined with olite 10.3.0.3 and oracle 11g
Edited by: vasileios on 16-Aug-2010 01:00
by using the debug option 4 debug txts are created the 4rth one has the entries below:
Start of debug.txt Jun 16 2010 SP=1d9ef6cc
17:11:13.000 *** InitCCC env=27237160
17:11:13.000 okConnect0()... 17:11:13.000 okConnect0(\SD Card\conscli.odb)=0
Connect nopass okEnv=1a00f74
Error at C:\ADE\omeprod_ol103030\olite\db\build\win\ocapi\..\..\..\src\ocapi\username.cpp line:1453 rc:-3264
Build date Jun 16 2010
okErr=(Timeout when waiting for a lock)
AddLog(-3264 "ERROR",POL-3264,"08/16/2010 17:11:14","Timeout when waiting for a lock:C$INFO","myuser1")
ROLLBACK OKAPI conscli
anyone that can possibly understand what this debug is telling us?
Does this happen on the initial sync with your device or does this happen after a few syncs?
So, 1st sync is successful, but then you make some changes and try to sync again and then it gives you the locking message.