Lock problem when inserting and deleting records

Hi all.

I have an application that is using a SQL compact edition database to save/process information.

I run a test that is creating two threads:

1. one is inserting data in Table1

2. the other one is deleting records from Table1

When I run the application, I get some exceptions on both threads saying that the insert/delete could not aquire a lock on the table.

After a while, when I try to connect to the database I get an exception saying that the database file might be corrupted.

Any thoughts?

Thanks.

Hello,

Can you send me the database, and if possible, tell me (or give me) the app, so that, I can see what the issue is? Send it to goteti.udaya.bhanu@.gmail.com. And also, let me know which version of SQL CE are you using (3.1 or 3.0 etc...). Are you using ADO.NET or a native app to do this. Can you send me the code.

Thanks

Udaya.

|||

The db has a very simple structure:

2 tables with strings and datetimes as columns data types.

I am using SQLCE 3.1 version and ADO.NET. So, there is no native call from my code.

Thanks.

Mircea

|||

Hello,

I have tried to repro the issue, with SQLCE 3.1, but, could not. I need one more detail for that, I think. Which version of .NET are you using? Are you using .NET CF or .NET (on a device or on the desktop)?

Thanks

Udaya.

|||

If your both threads are doing DML operations on the same thread at the same time, there is a good chance that you experience locking issues. It could be that both operations need a lock on the same index page and one of them is denied lock for a long time (2000 msec or some thing like that). This looks okay to me. We are trying to improve the experience.

How to solve it? You might solve the above problem by either handling the error or serializing your actions. In the first approach you might want to just catch the error and retry the operation.

But why file corruption? This should not happen. Unless we look into your code, it's difficult to explain. Can you please try using different connection objects in these two threads?

Thanks

Raja [MSFT]

P.S: If this solves your problem, please mark it as answered.

Lock problem when inserting and deleting records

Hi all.

I have an application that is using a SQL compact edition database to save/process information.

I run a test that is creating two threads:

1. one is inserting data in Table1

2. the other one is deleting records from Table1

When I run the application, I get some exceptions on both threads saying that the insert/delete could not aquire a lock on the table.

After a while, when I try to connect to the database I get an exception saying that the database file might be corrupted.

Any thoughts?

Thanks.

Hello,

Can you send me the database, and if possible, tell me (or give me) the app, so that, I can see what the issue is? Send it to goteti.udaya.bhanu@.gmail.com. And also, let me know which version of SQL CE are you using (3.1 or 3.0 etc...). Are you using ADO.NET or a native app to do this. Can you send me the code.

Thanks

Udaya.

|||

The db has a very simple structure:

2 tables with strings and datetimes as columns data types.

I am using SQLCE 3.1 version and ADO.NET. So, there is no native call from my code.

Thanks.

Mircea

|||

Hello,

I have tried to repro the issue, with SQLCE 3.1, but, could not. I need one more detail for that, I think. Which version of .NET are you using? Are you using .NET CF or .NET (on a device or on the desktop)?

Thanks

Udaya.

|||

If your both threads are doing DML operations on the same thread at the same time, there is a good chance that you experience locking issues. It could be that both operations need a lock on the same index page and one of them is denied lock for a long time (2000 msec or some thing like that). This looks okay to me. We are trying to improve the experience.

How to solve it? You might solve the above problem by either handling the error or serializing your actions. In the first approach you might want to just catch the error and retry the operation.

But why file corruption? This should not happen. Unless we look into your code, it's difficult to explain. Can you please try using different connection objects in these two threads?

Thanks

Raja [MSFT]

P.S: If this solves your problem, please mark it as answered.

Local drive doesn't appear more !

Hi,
I have a SQL Server 7.0 (SP4) with 1 phisical disk drive and 3 logical
partitions (C, D, E). This happened unexpectedly when I was deleting a
database.
I can see normally the letter E in windows explorer, but it doesn't appear
and permit me more create a database em that letter.
Any idea ?
Regards,
Vitor Mauricio
Check file permissions to ensure the SQL Server service account has full
permissions to the root folder.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vitor Mauricio de N. Silva" <vitor_mauricio@.hotmail.com> wrote in message
news:e4yjo36ZEHA.3112@.tk2msftngp13.phx.gbl...
> Hi,
> I have a SQL Server 7.0 (SP4) with 1 phisical disk drive and 3 logical
> partitions (C, D, E). This happened unexpectedly when I was deleting a
> database.
> I can see normally the letter E in windows explorer, but it doesn't appear
> and permit me more create a database em that letter.
> Any idea ?
> Regards,
> Vitor Mauricio
>
|||Vitor,
Adding to Dan's post, deleting a database would not have done anything
to your partitions, or your file system security settings. This would
have been a separate operation.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Dan Guzman wrote:
> Check file permissions to ensure the SQL Server service account has full
> permissions to the root folder.
>
|||Check event viewer for any information on this behaviour.
--
Satya SKJ
"Vitor Mauricio de N. Silva" wrote:

> Hi,
> I have a SQL Server 7.0 (SP4) with 1 phisical disk drive and 3 logical
> partitions (C, D, E). This happened unexpectedly when I was deleting a
> database.
> I can see normally the letter E in windows explorer, but it doesn't appear
> and permit me more create a database em that letter.
> Any idea ?
> Regards,
> Vitor Mauricio
>
>
|||All,
I check the file permission and everything is ok. In the event viewer I
cant see any error.
I never see this before, but the problems continue.
Thanks in advance for any suggestions,
Vitor Mauricio
"Vitor Mauricio de N. Silva" <vitor_mauricio@.hotmail.com> wrote in message
news:e4yjo36ZEHA.3112@.tk2msftngp13.phx.gbl...
> Hi,
> I have a SQL Server 7.0 (SP4) with 1 phisical disk drive and 3 logical
> partitions (C, D, E). This happened unexpectedly when I was deleting a
> database.
> I can see normally the letter E in windows explorer, but it doesn't appear
> and permit me more create a database em that letter.
> Any idea ?
> Regards,
> Vitor Mauricio
>

Local drive doesn't appear more !

Hi,
I have a SQL Server 7.0 (SP4) with 1 phisical disk drive and 3 logical
partitions (C, D, E). This happened unexpectedly when I was deleting a
database.
I can see normally the letter E in windows explorer, but it doesn't appear
and permit me more create a database em that letter.
Any idea ?
Regards,
Vitor MauricioCheck file permissions to ensure the SQL Server service account has full
permissions to the root folder.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vitor Mauricio de N. Silva" <vitor_mauricio@.hotmail.com> wrote in message
news:e4yjo36ZEHA.3112@.tk2msftngp13.phx.gbl...
> Hi,
> I have a SQL Server 7.0 (SP4) with 1 phisical disk drive and 3 logical
> partitions (C, D, E). This happened unexpectedly when I was deleting a
> database.
> I can see normally the letter E in windows explorer, but it doesn't appear
> and permit me more create a database em that letter.
> Any idea ?
> Regards,
> Vitor Mauricio
>|||Vitor,
Adding to Dan's post, deleting a database would not have done anything
to your partitions, or your file system security settings. This would
have been a separate operation.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Dan Guzman wrote:
> Check file permissions to ensure the SQL Server service account has full
> permissions to the root folder.
>|||Check event viewer for any information on this behaviour.
--
--
Satya SKJ
"Vitor Mauricio de N. Silva" wrote:

> Hi,
> I have a SQL Server 7.0 (SP4) with 1 phisical disk drive and 3 logical
> partitions (C, D, E). This happened unexpectedly when I was deleting a
> database.
> I can see normally the letter E in windows explorer, but it doesn't appear
> and permit me more create a database em that letter.
> Any idea ?
> Regards,
> Vitor Mauricio
>
>|||All,
I check the file permission and everything is ok. In the event viewer I
cant see any error.
I never see this before, but the problems continue.
Thanks in advance for any suggestions,
Vitor Mauricio
"Vitor Mauricio de N. Silva" <vitor_mauricio@.hotmail.com> wrote in message
news:e4yjo36ZEHA.3112@.tk2msftngp13.phx.gbl...
> Hi,
> I have a SQL Server 7.0 (SP4) with 1 phisical disk drive and 3 logical
> partitions (C, D, E). This happened unexpectedly when I was deleting a
> database.
> I can see normally the letter E in windows explorer, but it doesn't appear
> and permit me more create a database em that letter.
> Any idea ?
> Regards,
> Vitor Mauricio
>

Local drive doesn't appear more !

Hi,
I have a SQL Server 7.0 (SP4) with 1 phisical disk drive and 3 logical
partitions (C, D, E). This happened unexpectedly when I was deleting a
database.
I can see normally the letter E in windows explorer, but it doesn't appear
and permit me more create a database em that letter.
Any idea ?
Regards,
Vitor MauricioCheck file permissions to ensure the SQL Server service account has full
permissions to the root folder.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Vitor Mauricio de N. Silva" <vitor_mauricio@.hotmail.com> wrote in message
news:e4yjo36ZEHA.3112@.tk2msftngp13.phx.gbl...
> Hi,
> I have a SQL Server 7.0 (SP4) with 1 phisical disk drive and 3 logical
> partitions (C, D, E). This happened unexpectedly when I was deleting a
> database.
> I can see normally the letter E in windows explorer, but it doesn't appear
> and permit me more create a database em that letter.
> Any idea ?
> Regards,
> Vitor Mauricio
>|||Vitor,
Adding to Dan's post, deleting a database would not have done anything
to your partitions, or your file system security settings. This would
have been a separate operation.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Dan Guzman wrote:
> Check file permissions to ensure the SQL Server service account has full
> permissions to the root folder.
>|||All,
I check the file permission and everything is ok. In the event viewer I
can´t see any error.
I never see this before, but the problems continue.
Thanks in advance for any suggestions,
Vitor Mauricio
"Vitor Mauricio de N. Silva" <vitor_mauricio@.hotmail.com> wrote in message
news:e4yjo36ZEHA.3112@.tk2msftngp13.phx.gbl...
> Hi,
> I have a SQL Server 7.0 (SP4) with 1 phisical disk drive and 3 logical
> partitions (C, D, E). This happened unexpectedly when I was deleting a
> database.
> I can see normally the letter E in windows explorer, but it doesn't appear
> and permit me more create a database em that letter.
> Any idea ?
> Regards,
> Vitor Mauricio
>