Access multiuser / transactions / asp

[Patrick]

Hi all

I'm getting this error message :
The table "MyTable" is already opened exclusively by another user or it is
already open throught the user interface and cannot be manipulated
programmatically
Of course Access User interface is not opened.

This happens when 2 users try to write to the same table simultaneously.
Everything runs fine when the users do not perform the same action at the
very same time, I mean do not try to write to the same table at the very
same time.

Users use an ASP front end.
My connection string :
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and
Settings\xxx\MyDatabase.mdb;User ID=admin;"

My Database properties :
Default Opening Mode = Shared
Default Lock = No Lock

My ASP Code :
Conn.Open
Conn.BeginTrans
Conn.execute "DELETE * FROM MyTable"
Conn.execute "INSERT INTO MyTable ..."
rs.Open "SELECT * FROM MyTable"
response.write(rs.fields(xxx))
rs.close
Conn.Committrans

Is the exclusive lock caused by the transaction ?
If yes, is there a way to make the second user wait ?
If no, is there a solution to solve this problem ?

My general purpose is to use the same table to store some user specific
temporary information and immediately display it.
So 2 users should not write to that table at the same time because i don't
have a userID field.
That's why i use transactions, but maybe this design is not very good.
If i need to create a userID field, i don't know what value i could write :
maybe some session ID ?

Thanks to those who will reach the end of my question(s).
Any advice welcome.

Bye

[Bob Barrows [MVP]]

Patrick wrote:

> Hi all
>
> I'm getting this error message :
> The table "MyTable" is already opened exclusively by another user or
> it is already open throught the user interface and cannot be
> manipulated programmatically

Your answer will be in here:
[url]http://www.aspfaq.com/show.asp?id=2009[/url]

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Mark Schupp]

If you have the data to insert into the table, and you are just going to
retrieve it immediately after you insert it, then why are you inserting it
into the database? Will another page be using it? If so, you need an id to
prevent another user from deleting the data before the first user is done
with it.

--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]


"Patrick" <xxx@xxx.xxx> wrote in message
news:eJIEamLHEHA.2720@TK2MSFTNGP11.phx.gbl...

> Hi all
>
> I'm getting this error message :
> The table "MyTable" is already opened exclusively by another user or it is
> already open throught the user interface and cannot be manipulated
> programmatically
> Of course Access User interface is not opened.
>
> This happens when 2 users try to write to the same table simultaneously.
> Everything runs fine when the users do not perform the same action at the
> very same time, I mean do not try to write to the same table at the very
> same time.
>
> Users use an ASP front end.
> My connection string :
> "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and
> Settings\xxx\MyDatabase.mdb;User ID=admin;"
>
> My Database properties :
> Default Opening Mode = Shared
> Default Lock = No Lock
>
> My ASP Code :
> Conn.Open
> Conn.BeginTrans
> Conn.execute "DELETE * FROM MyTable"
> Conn.execute "INSERT INTO MyTable ..."
> rs.Open "SELECT * FROM MyTable"
> response.write(rs.fields(xxx))
> rs.close
> Conn.Committrans
>
> Is the exclusive lock caused by the transaction ?
> If yes, is there a way to make the second user wait ?
> If no, is there a solution to solve this problem ?
>
> My general purpose is to use the same table to store some user specific
> temporary information and immediately display it.
> So 2 users should not write to that table at the same time because i don't
> have a userID field.
> That's why i use transactions, but maybe this design is not very good.
> If i need to create a userID field, i don't know what value i could write

:

> maybe some session ID ?
>
> Thanks to those who will reach the end of my question(s).
> Any advice welcome.
>
> Bye
>
>

[Patrick]

Thanks for your answer Bob, but i can't find my error message in the page
you mention.

I have no problem accessing the database file : it is not a file error, but
a table error.
What can cause this exclusive lock on the table (and not on the file) ?
Is it the transaction ?


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> a écrit dans le message de
news:%23OI8GUMHEHA.2128@TK2MSFTNGP11.phx.gbl...

> Patrick wrote:

> > Hi all
> >
> > I'm getting this error message :
> > The table "MyTable" is already opened exclusively by another user or
> > it is already open throught the user interface and cannot be
> > manipulated programmatically

>
> Your answer will be in here:
> [url]http://www.aspfaq.com/show.asp?id=2009[/url]
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

[Patrick]

My operations on the table are much more complicated than the example i give
and involves other tables.
That is why is use a table : to use relations to other tables and
referential integrity.

I think a good ID could be the SessionID. Do you agree ?

"Mark Schupp" <mschupp@ielearning.com> a écrit dans le message de
news:OvT4vtMHEHA.700@TK2MSFTNGP09.phx.gbl...

> If you have the data to insert into the table, and you are just going to
> retrieve it immediately after you insert it, then why are you inserting it
> into the database? Will another page be using it? If so, you need an id to
> prevent another user from deleting the data before the first user is done
> with it.
>
> --
> Mark Schupp
> Head of Development
> Integrity eLearning
> [url]www.ielearning.com[/url]

[Mark Schupp]

You should always give examples that accurately reflect what you are trying
to do.

Session ID will be fine if the data is temporary and only one web-server
will be accessing the database (session ids are not unique across servers
and may be repeated if IIS restarts).

If you still need to use a transaction, close it as soon as all data updates
have occurred.

--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]


"Patrick" <xxx@xxx.xxx> wrote in message
news:ePhpsUXHEHA.164@tk2msftngp13.phx.gbl...

> My operations on the table are much more complicated than the example i

give

> and involves other tables.
> That is why is use a table : to use relations to other tables and
> referential integrity.
>
> I think a good ID could be the SessionID. Do you agree ?
>
> "Mark Schupp" <mschupp@ielearning.com> a écrit dans le message de
> news:OvT4vtMHEHA.700@TK2MSFTNGP09.phx.gbl...

> > If you have the data to insert into the table, and you are just going to
> > retrieve it immediately after you insert it, then why are you inserting

it

> > into the database? Will another page be using it? If so, you need an id

to

> > prevent another user from deleting the data before the first user is

done

> > with it.
> >
> > --
> > Mark Schupp
> > Head of Development
> > Integrity eLearning
> > [url]www.ielearning.com[/url]

>
>

[Patrick]

Thanks for your help Mark

"Mark Schupp" <mschupp@ielearning.com> a écrit dans le message de
news:OygvR3XHEHA.828@TK2MSFTNGP12.phx.gbl...

> You should always give examples that accurately reflect what you are

trying

> to do.
>
> Session ID will be fine if the data is temporary and only one web-server
> will be accessing the database (session ids are not unique across servers
> and may be repeated if IIS restarts).
>
> If you still need to use a transaction, close it as soon as all data

updates

> have occurred.
>
> --
> Mark Schupp
> Head of Development
> Integrity eLearning
> [url]www.ielearning.com[/url]

[Bob Barrows [MVP]]

Patrick wrote:

> Thanks for your answer Bob, but i can't find my error message in the
> page you mention.

Really? It's very similar to te first error listed.

>
> I have no problem accessing the database file : it is not a file
> error, but a table error.

I disagree. it is a folder permissions problem. The IUSR account needs
Change permissions for the folder containing your database. Please verify
that this is the case. If it is, then we need to look for other causes of
your problem.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Patrick]

i gave the IUSR account all the rights to the folder.
i did too with my administrator account as my asp pages are not allowed to
anonymous users.
All of these didn't solve the problem.

I have absolutely no problem accessing the database file : my app works fine
with one user or multiple users.

I have trouble when two users try to write/update/delete in the same table
at the same time.
These actions are nested in a transaction because i wanted to create a kind
of queue : the second users wait until first user's transaction is finished.
It used to work before.
I don't know why it doesn't work anymore : i made too much changes to
remember.

What causes the exclusive lock on the table ? Is it the transaction ?
If yes, why doesn't the second user wait ?

I'm lost...



"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> a écrit dans le message de
news:%23UT3tHZHEHA.520@tk2msftngp13.phx.gbl...

> Patrick wrote:

> > Thanks for your answer Bob, but i can't find my error message in the
> > page you mention.

>
> Really? It's very similar to te first error listed.

> >
> > I have no problem accessing the database file : it is not a file
> > error, but a table error.

>
> I disagree. it is a folder permissions problem. The IUSR account needs
> Change permissions for the folder containing your database. Please verify
> that this is the case. If it is, then we need to look for other causes of
> your problem.
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

[Bob Barrows [MVP]]

Patrick wrote:

> i gave the IUSR account all the rights to the folder.
> i did too with my administrator account as my asp pages are not
> allowed to anonymous users.
> All of these didn't solve the problem.
>
> I have absolutely no problem accessing the database file : my app
> works fine with one user or multiple users.
>
> I have trouble when two users try to write/update/delete in the same
> table at the same time.
> These actions are nested in a transaction because i wanted to create
> a kind of queue : the second users wait until first user's
> transaction is finished. It used to work before.
> I don't know why it doesn't work anymore : i made too much changes to
> remember.

Are you using error-handling to allow the second user's transaction to wait
until the first user's transaction is completed?

>
> What causes the exclusive lock on the table ? Is it the transaction ?
> If yes, why doesn't the second user wait ?
>

Access (Jet) does not do row-level locking. It locks data by page, each page
being 2 kb. So it is a possibility that the row the second user is trying to
edit is in the page of data that is locked by the first user. It is also
possible that the changes being done by the first user requires the lock to
be escalated to the table level. This could be as a result of modifying an
indexed field or some other action that requires an index to be rebuilt.

This is the reason that it is best to keep transactions as short as
possible. I suspect that you are going to need to migrate to a real database
server if you are going to need this kind of transaction control.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Patrick]

> Are you using error-handling to allow the second user's transaction to
wait

> until the first user's transaction is completed?

No,
I thought it was a default behavior with transactions.
It worked before, but maybe for an other reason.
Check this :
[url]http://www.mail-archive.com/bdotnet@groups.msn.com/msg11234.html[/url]
It explains what i call the "queue" : "the other
persons ReadRequest continuously Pings the DB until the FirstPerson
Commits/Rollback
his Changes "

> Access (Jet) does not do row-level locking. It locks data by page, each

page

> being 2 kb. So it is a possibility that the row the second user is trying

to

> edit is in the page of data that is locked by the first user. It is also
> possible that the changes being done by the first user requires the lock

to

> be escalated to the table level. This could be as a result of modifying an
> indexed field or some other action that requires an index to be rebuilt.
>
> This is the reason that it is best to keep transactions as short as
> possible. I suspect that you are going to need to migrate to a real

database

> server if you are going to need this kind of transaction control.

[Bob Barrows [MVP]]

Patrick wrote:

>> Are you using error-handling to allow the second user's transaction
>> to wait until the first user's transaction is completed?

>
> No,

Well, I think you are going to have to handle this error, don't you?

> I thought it was a default behavior with transactions.
> It worked before, but maybe for an other reason.
> Check this :
> [url]http://www.mail-archive.com/bdotnet@groups.msn.com/msg11234.html[/url]

dotnet? This is a .Net question? This is a classic ASP newsgroup. You should
be asking this question in one of the dotnet newsgroups. ADO.Net is
different from classic ADO. Try
microsoft.public.dotnet.framework.adonet

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Oh wait. As Emily Litella used to say: Never mind!

I just looked at the code in your first message and it is not .Net code. The
"dotnet" was simply the name of the website ...

Sorry.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Patrick]

Bob, let me thank you for all your answers.

It's time to close this topic because now i understand that i want something
that doesn't exist.

I'll solve this problem with 2 modifications :

1. when possible, use a field to recover User ID and thus having the same
table contain rows for many users instead of one. That will prevent 2 users
to need the same row.

2. when not possible, error handling to make the user wait



See you !



"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> a écrit dans le message de
news:edyi0zaHEHA.3364@TK2MSFTNGP09.phx.gbl...

> Oh wait. As Emily Litella used to say: Never mind!
>
> I just looked at the code in your first message and it is not .Net code.

The

> "dotnet" was simply the name of the website ...
>
> Sorry.
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>