Sql Compact and __sysobjects

Question

I have some SQL Compact queries that create tables inside of transaction. This is mainly because I need to simulate temporary tables, which SQL Compact does not support. I do this by creating a real table, and then dropping it at the end of the transaction.

This mostly works. Sometimes, however, when creating the tables Sql Compact will try to acquire PAGE level locks on the __sysobjects table. If there are several concurrent queries running that create "temp" tables, the attempt to acquire a page lock can result in a dead lock followed by a SqlLockTimeout exception.

For normal tables I could fix this using a "with (rowlock)" hint. However, because I'm not writing the query to insert into __sysobjets (SQL server does that in response to "create table") I can't do this.

Does anyone know of a way I could get around this?

I've thought about pulling the table creation out of the transaction, but that opens up the possibility of phantom temporary tables that I'd then need to clean up regularly. Ideally I'd like to avoid that if possible.

Answer 1

I think you are bound to have concurrency issues with creating regular tables instead of #temp tables.

I had no idea that SQL Server Compact edition does not let you create #Temp tables.

How about using Table variables instead? Will that work for you?

Answer 2

I think you might have to serialize your transactions...