Concurrent inserts in a multi user environment.

[RichardF]

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.

 

[johnherman]

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

 

[svanels]

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

 

[RichardF]

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.

 

[johnherman]

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

 

[ceh4702]

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.

 

[alvechurchdata]

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

http://www.alvechurchdata.co.uk

 

[RichardF]

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.

 

[stefanhei]

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