Index locking questions - Unspecified error [ 2, 1716968486, 1737055254, {table name}, ix lock (x blocks), TAB ]

Question

The error
Unspecified error
[ 2, 1716968486, 1737055254, {Table Name}, ix lock (x blocks), TAB ]

The platform
SQL CE 3.5 SP1
dot NET 3.5 SP1
ADO.NET Sync RC1 (Assembly Version 2.0.0.0 File Version 2.0.1215.0)
Windows Mobile 6.1
(This is recreatable on the emulator as well as a real device)

What the software is doing
Thread 1 : ADO.NET sync is performing a bi-directional sync with the server, uploading and downloading 3 tables with updates.  The database tables are using a Guid as the primary keys.  This process takes 2-3 minutes on a good day, with a 10 second down time.
Thread 2 : Perform a update on these three tables in one transaction, and yes this can be the same row of data that Thread 1 is processing.  (Before you ask, Thread 2 is using its own SqlCeConnect and Transaction, completly independant of Thread 1's connection and transaction)

I usually see this error in Thread 2, however because Thread 1 is a background process and the update is controlled by Microsoft.Sync it is difficult to see errors in thread 1.

Solution in place at the moment
Remove all indexes (except for the primary key index) on these three tables...  YUK.  I really dont like this solution as these tables total over 12MB and I dont need to go into detail how indexes make a huge difference loading records on a 400Mhz mobile phone!!!

* The QUESTIONS I would like answered *

1.  I am having difficulty finding the meaning to the error codes, is there a link available describing the meaning?

2.  This is a Index Lock Timeout error
2.1  How do I change the length of time to time out?  (example connection string)
2.2  The lock seems to hang around indefinetly most of the time, any ideas how to reset this lock?

3.  Any reason as to why removing the index causes the code to function error free?  (Preferable an answer that doesnt say the timeout for index locks is shorter than the database locks, due to the fact the lock doesn't seem to release)

4.  (Best Case Scenario) When the error is shown, the index stay's locked causing errors while trying to load the row affected.  The only way to stop this error is by loading another row that isn't locked, then the row that errored becomes unlocked.  Keep in mind when the transaction is used in the failed commit I close the transaction, but I am still using the same connection.  Any ideas?  Should I be creating a new connection after this error?

5.  (Worst Case Scenario) Database corruption.  I am unable to load anything from the table causing the app to bomb out.  Copying the database to a PC and opening it in Visual Studio/SQL Server Manager allows the database to work again.  From what I understand this is because SqlCe for ARM, and SqlCe for x86 use different indexing methods.  Loading a database in each OS checks to see the build version of the index (in other words, was it the current OS that last opened this database).  If it doesn't match it will rebuild the index for that version.  Ok, so I understand this is corruption in the index, not the data itself.  Any explination or preventative steps?


Thankyou all for those that read this far!  Any information / ideas is greatly appreciated.

Damian

Answer 1

Hi,

May you post a thread in the Windows Mobile Developer Forums, there are more experts who have development experience 

Forum link here: http://social.msdn.microsoft.com/Forums/en-US/category/smartdevicedevelopment/

---

ChunSong Feng
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Hi,

Since the same thread have been posted in the Windows Mobile Developer Forum here, I am closing this thread. Thanks for your understanding.

---

ChunSong Feng
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 3

Hi Omad,

 

Thank you for your posts!

 

Based on your description, the problem probably was caused by indexes. Since it is a multithreading (at least there are two threads) application, one thread for ADO.NET sync and the other one for query transactions (update). I think the problem may happened when your updating your table meanwhile the sync thread is trying to uploading/downloading tables.

 

My suggestions:

1.     Do a simple test to see whether the problem still happens, if it happens, it is easy to address the cause.

2.     Debug the application step by step to find the place where the problem happens.

 

Besides, in the database, are there any unnecessary indexes to make confusion? Here is a article about Optimizing SQL Server Indexes:

http://www.sql-server-performance.com/tips/optimizing_indexes_general_p1.aspx

 

If you have any problems about SQL Server Compact, you can post your issue in this forum. I'm willing to help you on that.

 

Hope this helps! If you have any concern, please feel free to let me know.

---

ChunSong Feng
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 4

As suggested, for testing pupposes, serialize access to the database, so do not update data while doing sync.

---

http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.

Answer 5

Thankyou for your replies.

To Chunsong : The thread that is not ADO.NET sync produces an error while committing the transaction.  The error is not re-creatable straight away...  It requires to repeat the process of syncing and updating / inserting tasks many times (say up to 30 mins, but has been known to happen within the first or second sync) before it finally starts to error.

But I would like to understand what can cause (and what can fix) index corruption on the mobile.  I have done lots of reading, and the link seems to be that the database is stored on a external memory card, and the device is going to standby alot (though this can be created without going to standby mode.)

To ErikEJ : Removing the multi-threaded updates are in the works, for the mean time I am sticking with my current solution of removing all index's (!primary keys) from the tables where both apps update/insert records.  At least this way I have multi-threaded behaviour less speed + error.

To All
Why does this error (IX LOCK) happen when I am INSERTING a new record?  Shouldn't a lock compatability error happen when two threads update the same record?

Damian

Answer 6

I think TAB indicates a table lock, o INSERTS and UPDATEs would conflict. Have you seen these related threads:

http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/d9676677-c04d-4a32-bb92-502174cf5e94

http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/859fdf43-3114-49c6-a4e0-a83bb4479d3e/

Make sure the device driver for the storage card is updated, have seen errors if this had issues, and updates from the device manufacturer fixed this.

---

http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.

Answer 7

Hi Guys,

No updates for the storage card, and I can recreate this problem on the emulator... I doubt there are updates for the storage card for the emulator.

The first thread you linked : http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/d9676677-c04d-4a32-bb92-502174cf5e94

This one is closely related to my problem.  Summary :
 - Run two apps against same database
 - Get an index lock on the inserting application
 - Index corruption on the database afterwards

Now I understand the index lock timeout, that is consistant here.  The database (index) corruption is the worrying consistancy.  Yes, this should not happen.  In my findings the corruption has only been replicated on demand after a late night of coding.  It seems to happen randomly, and I cannot figure out the exact conditions.  All I know is once apon a time I could get this corruption on command, now willy nilly.


Well, I like my solution of removing the index's.  It removes the chance of error and corruption by a factor of 100%.  I cannot trust that SQL CE with index's in a multi-threaded environment WILL NOT corrupt my database, and I have modified my deployed databases to reflect this.  I would rather deploy an application thats slow than a unreliable one.


Serializing the database access is not an option as the sync thread can take up to 10 minutes before it stops downloading / updating.
 - Making the customer wait 10 minutes to do a insert / update to the table is not acceptable.
 - Caching the database changes and commiting them when the sync thread is complete has two side affects.
   1. Increases the memory usage on the mobile, until Win. Mo. 7 this is a bad idea.
   2. Power failure could result in loss of work.


Thanks

Damo

Answer 8

Waiting for 10 minutes (or was it 2-3 minutes as you mentioned originally) - are you sure that Sync services is the right choice for you? It is known to not perform well on devices, unless you do some extra work, with batching and compression.  See this codplex project: http://synccomm.codeplex.com/ 

Have you considered Merge replication?

---

http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.

Answer 9

Hey ErikEJ,

I must say I really appreciate you reading all my posts, it means alot!  Yeah I did say 2-3 minutes.  See, in the country this particular app is running in, the mobile's are running on a older mobile signal and the CPU's are 400Mhz, which makes the sync nice and slow.  So the 2-3 minutes is on a good day (in my city, with this brand new 800Mhz thing), or 10 minutes (overseas, using more affordable 400Mhz devices).

What happens is I have 90 tables to sync which takes 45 mins in total (on a good day).  I sync them 10 at a time so if the connection drops out I only loose 10 tables worth, not 90.  The process is alot more complicated than this, its just a summary.

Compression doesn't make much of a difference in my scenario, and batching is a dead end which I hope is solved in the next cut of ADO.NET Sync...
http://social.msdn.microsoft.com/Forums/en-US/uklaunch2007ado.net/thread/ee7f0c44-f6c5-4dcc-b12e-d67b79ef6b0a

I will read up more on Merge Replication, but im hesitant to move to it as it doesn't sound like "The Next ADO.NET Sync".  I have invested 18 months into this ADO.NET Sync technology and have worked around some extremly complicated issues.  However I will still read up on Merge Replication.

Thankyou again.