MS Access transactions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Martin Kulov - 01 Sep 2004 10:33 GMT

Hi,

I am trying to implement isolated transaction but could not find
anywhere explicit statement whether MS Access supports or does not
support Serializable transaction level. I am using ADO.NET to create
connection and command object. I can create transactions Read
uncommitted, Read committed, but for RepeatableRead and Serializable I
get exception [1].
Does anyone have more specific information about this?

Thanks,
Martin Kulov

[1] "Neither the isolation level nor a strengthening of it is supported."

Reply to this Message

Mary Chipman - 01 Sep 2004 14:56 GMT

The isolation levels you mention are supported by SQL Server, not
Access. Access does not use the same kind of locking mechanisms to
enforce transactions the way SQL Server does.

--Mary

>Hi,
>
[quoted text clipped - 10 lines]
>
>[1] "Neither the isolation level nor a strengthening of it is supported."

Reply to this Message

Martin Kulov - 02 Sep 2004 08:39 GMT

Ok.

But I have opened Access database using OleDbConnection with transaction
level set to ReadCommited. May be the transactions are not named in the same
way as they are in MS SQL but they still exists, don't they.

Let's rephrase the questions:

What transaction levels are supported by MS Access and where I can find
additional information?

In general I what to run multiple statements and make sure that no one will
change or add new rows between my statements. Since Access does not support
multiple statements in one query I decided that I need isolated
(serializable) transaction. How should I implement this?

Thanks,

Martin Kulov

> The isolation levels you mention are supported by SQL Server, not
> Access. Access does not use the same kind of locking mechanisms to
[quoted text clipped - 16 lines]
> >
> >[1] "Neither the isolation level nor a strengthening of it is supported."

Reply to this Message

Mary Chipman - 09 Sep 2004 19:04 GMT

Sorry for the delay in responding to you, I was out of commission due
to Hurricane Frances. I'd recommend using the syntax shown in the Help
article, "Performing a Transaction Using ADO.NET". If you use saved
Access queries for the multiple operations inside of the transaction,
you can set the querydef properties to pessimistic locking for each
saved query, and execute the queries instead of sending direct SQL
statements. However, be warned -- this could cause significant
bottlenecks in performance, depending on how much data is being locked
by those queries and how well or poorly your database is designed, and
how many concurrent users you have.

--Mary

>Ok.
>
[quoted text clipped - 36 lines]
>> >
>> >[1] "Neither the isolation level nor a strengthening of it is supported."

Reply to this Message

Martin Kulov - 11 Sep 2004 13:35 GMT

Hi Mary,

Thanks for your response!

I read the article that you sent me and it explains exactly what I am doing
right now.

Still I do not know what isolation level I should specify in
BeginTransaction method that will prevent inserting new rows when another
transaction is currently reading and inserting in the table.

Do you have any information?

Thanks,

Martin Kulov

> Sorry for the delay in responding to you, I was out of commission due
> to Hurricane Frances. I'd recommend using the syntax shown in the Help
[quoted text clipped - 49 lines]
> >> >
> >> >[1] "Neither the isolation level nor a strengthening of it is supported."

Reply to this Message