×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Concurrent inserts in a multi user environment.

Concurrent inserts in a multi user environment.

Concurrent inserts in a multi user environment.

(OP)
Hi,

The system we are developing is intending for intial use with standard Microsoft Access databases.  However for future versions we plan to move over to Oracle (v 9 or 10).  

The system should cope with concurrent inserts to the same table from different users.  This is giving us problems in dealing with numeric primary keys (or ID fields).  If the target system was just MS Access, you could use Auto-Numbers.  As far as i know Auto-Numbers are not available in oracle.  Although I know Oracle has something called sequences it would be better to find a solution that fits both dbms's.

Developers here have come up with an "ID Control" table.  The idea behind this is there is a record per ID column. Each record tracks the max (or next available) ID.

Applications can then read the required column , increment it by the number of records and then update it before inserting the actual data.  The only problem with this solution is transactional processing since the updates are not sent to the table until it is committed.

Is there another way ?  or is there a way around the transaction issue ?

Thanks in advance.
Rich.




 


RE: Concurrent inserts in a multi user environment.

The good news is that Oracle handles data concurrency issues in an excellent manner.

I would create a separate table (as you have proposed), but would take the control number and immediately commit. If the transaction rolls back, that number will never be used. Thus your only delay in the concurrent processing is for the time to lock the control file, get the next sequence number, increment, and unlock.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

RE: Concurrent inserts in a multi user environment.

Hmm, start developping a system in Access and upscale it to Oracle?

I would avise to read http://databases.about.com/b/a/164849.htm
to see the differences in both systems first.

The so called Client-Server systems (Oracle, Interbase, SQL-Server etc.) are totally different animals from the desktop systems (access, paradox, dbase etc..)

I am familiar with Interbase which work with triggers and generators. Autonumbers are not available in Interbase either, you define a trigger (before insert) to handle it. Oracle does have a similar system.
If the application must generate the number, in a multi-user environment, this can/will give problems.
With the trigger the Database Server will handle this, and much more.

Steven

RE: Concurrent inserts in a multi user environment.

(OP)
Hi, Thanks for you replies.

Johnherman - yes that is true.  But would that mean begintrans and commit on every record to be insert ?

I wanted one whole transaction for all the inserts - thus if there was an error and the user wanted to cancel - then it is likely they would want to cancel the whole batch.


svanels - thanks for the link. Yes i am aware oracle uses this triggering system (before the insert) - however Access does not have a triggering mechanism (i believe).


These are the ideas I came up with: (comments welcome).

- Have a seperate connection to the database which handles only the ID Control updating etc.  The 'main' connection could then be used in a batch transaction.  If the user wanted to rollback all those records then either the ID can be set back to its original value (or it could be just left and there will be holes in the ID numbers).

- Use a global system flag or conditional compilation arguments (e.g Oracle = TRUE).  This way the application knows that Oracle will handle the ID numbers its own way.  This also means using AutoNumbers in Access.  Will Access handle AutoNumbers for multiple inserts in a multipe user environment ?


Regards,
Rich.








RE: Concurrent inserts in a multi user environment.

Sorry. I was not aware that you were doing a "Bulk Insert" with all or nothing. With bulk inserts, my tendency has been to assign some control fields to each record added, including a batch number or similar identifier. Then, using my autonumber process, the "rollback" becomes a delete of all records with the specified batch number.

I should add that I also like your idea about a global system flag (or .ini file) which contains info related to the database (Oracle / Access) and anything else unique to that processing environment.

The dual connection solution I find to be the least appealing, may be more difficult to implement, and lacks the flexibility of the previous two solutions.

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

RE: Concurrent inserts in a multi user environment.

Why dont you just say what you are autonumbering?  Our new database uses auto numbering.  It also uses a procedure with weighting to determine if a new record is a duplicate for people.  It looks at several factors and assigns a numeric value for true/false conditions and if the number is high enough it adds the record.  We do this for adding people.  We force the system to look for a SSN among other things.  

Wonder if you can run a bulk batch process during a backup, or right after when the database is completely locked down.

If you do not like my post feel free to point out your opinion or my errors.

RE: Concurrent inserts in a multi user environment.

Quote:

Why dont you just say what you are autonumbering?
You'll need a different approach depending on whether you are  using the number simply as a primary key or whether you are creating a sequence. Missing numbers don't matter in a primary key but you'll get complaints if you start skipping invoice numbers.

Geoff Franklin
www.alvechurchdata.co.uk

RE: Concurrent inserts in a multi user environment.

(OP)
Hi,

Unfortunately this approach wouldnt work in this situation since the ID fields are primary keys which would are also become foreign keys in a variety of other tables.

Regards,
Rich.

RE: Concurrent inserts in a multi user environment.

Hi.
Usually tables are objects which are created once and thereafter simply written to or read from.
So the problem only occurs in your DB-creation scripts, which will differ for both systems anyway.
If you implement the trigger/sequence solution in oracle it will react during runtime the same way as autonumbers in access.
So if you do not use tables as temporary objects the implementation of the autonumbering mechanism should not bother you.

Stefan

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close