Hi,

I have this code....

DataAccessAdapter adapter = new DataAccessAdapter();

adapter.StartTransaction(IsolationLevel.RepeatableRead, "trans1");

EntityCollection<FileToDeleteEntity> collection = new EntityCollection<FileToDeleteEntity>();
adapter.FetchEntityCollection(collection, null);

If I break just after the last line of code, I can go to SQL Server Management Studio and list the rows from the FileToDelete table just fine.

I think this query should be blocked as a result of setting the isolation level to repeatable read. So why isn't is working like that?

Cheers, Ian.

RepeatableRead: Locks are placed on all data that is used in a query, preventing other users from updating the data. Prevents non-repeatable reads but phantom rows are still possible.

So, you can still select the data but no update it.

OK, is it possible to only allow one person to read a table?

I've got a table filled with files to delete. I want to avoid two users from trying to delete the same files at the same time so I want other users to wait until the first user has tried deleting. Hopefully when other users get to the table its then empty.

The IsolationLevel enum have only these items :

        // Summary:
        //   A different isolation level than the one specified is being used, but the
        //   level cannot be determined.
        Unspecified = -1,
        //
        // Summary:
        //   The pending changes from more highly isolated transactions cannot be overwritten.
        Chaos = 16,
        //
        // Summary:
        //   A dirty read is possible, meaning that no shared locks are issued and no
        //   exclusive locks are honored.
        ReadUncommitted = 256,
        //
        // Summary:
        //   Shared locks are held while the data is being read to avoid dirty reads,
        //   but the data can be changed before the end of the transaction, resulting
        //   in non-repeatable reads or phantom data.
        ReadCommitted = 4096,
        //
        // Summary:
        //   Locks are placed on all data that is used in a query, preventing other users
        //   from updating the data. Prevents non-repeatable reads but phantom rows are
        //   still possible.
        RepeatableRead = 65536,
        //
        // Summary:
        //   A range lock is placed on the System.Data.DataSet, preventing other users
        //   from updating or inserting rows into the dataset until the transaction is
        //   complete.
        Serializable = 1048576,
        //
        // Summary:
        //   Reduces blocking by storing a version of data that one application can read
        //   while another is modifying the same data. Indicates that from one transaction
        //   you cannot see changes made in other transactions, even if you requery.
        Snapshot = 16777216,

I don't see a way to allow oly one person to read a table.

maybe you should use DotNet transactions :

http://www.microsoft.com/downloads/details.aspx?familyid=AAC3D722-444C-4E27-8B2E-C6157ED16B15&displaylang=en

Ian wrote:

OK, is it possible to only allow one person to read a table?

I've got a table filled with files to delete. I want to avoid two users from trying to delete the same files at the same time so I want other users to wait until the first user has tried deleting. Hopefully when other users get to the table its then empty.

That requires a table lock, which is not really recommended, as it blocks all other users from doing anything with the table.

A serialized transaction which first selects all data will lock the table as long as the transaction is running as it locks all rows.

I could still SELECT the rows from Management Studio whilst the app was broken in the middle of a serialized transaction.

Hello,

If the app doesn't break, is the SELECT works? If your app was broken in the middle of a serialized transaction, the transaction is released, no?

Hi,

jbb wrote:

Hello,

If the app doesn't break, is the SELECT works? If your app was broken in the middle of a serialized transaction, the transaction is released, no?

Its too fast to check if the app doesn't break and breaking an app during a transaction doesn't as far as I know release a transaction.

Would you please provide the SQL Server version, LLBLGen Pro runtimeLibrary version, .NET Framework number. Also would you please repeat the experiment using the query analyzer, with a another account than the SQL admin account.

I've got a table filled with files to delete. I want to avoid two users from trying to delete the same files at the same time so I want other users to wait until the first user has tried deleting. Hopefully when other users get to the table its then empty.

Maybe you'd want to consider using a critical section in code. ref: http://msdn2.microsoft.com/en-us/library/ms682530.aspx

Ian wrote:

I could still SELECT the rows from Management Studio whilst the app was broken in the middle of a serialized transaction.

Hmm. Did the app already select all the rows? It has to do that in a serialized transaction to place locks on all rows. BEFORE The commit you have to break the app so the tranaction/connection are still open and THEN you should test your select on a different connection. That should block.