Ms-Jet Engine 4.x Databases (5 billion records, 10,000 users) Custom Record Locking & User Security Levels Strategies RRS feed

  • General discussion

  • Hello MS-Access, MS-Jet, MS SQL Server developers.

    I am working on a MS-Jet database GUI user-interface (written in Win32 Perl - i.e. ActiveState/ActivePerl) for a MS-Jet database I have created that has 5 billion rows loaded to it.  I might takes this further in the future to 10's of billions, but for now, I am limiting myself to 5 billion records (1 Terabyte).  That is 500 times 2-Gig files, each containing 10 million rows.  I have compression and encryption turned on with this database, which I created via ODBC Administrator utility on Windows 7 Home Premium.  I created the Tables/Indexes via ODBC/SQL from within Win32 Perl.

    My ODBC connection strategy is to NOT KEEP persistent database connections open to any of the 500 *.MDB files in my file system, but rather, only have my GUI user-interface make a connection to any 1-of-500 MDB files just long enough to read a row into a screen form for edit/save, or add/insert a new record, or delete a record, or load a ListView or TreeView of rows for inspection, etc.


    Instead of using a [user security level] database,  such as is provided by the MS-Access software interface to MDB files,  I plan on making my own table within a MDB file I reserve for this purpose within my MDB database file system (500 files,  which represent tables or partial tables, and not databases in and of themselves).  I want to have Admin (level 1), Super User (level 2), Maintenance User (level 3), and Query Only User  (level 4).    When a user attempts to login to the database user-interface, they will have to provide a username/password to do so.  This is confirmed against that stored in the [user security level] table I create for this purpose. Also in that table will be the "level" that particular user is granted within the database.   This ADMIN table, I refer to it as, will also have a column to "lock out" a user from the user-interface system.   Also will be a column to lock out all users from the database should that be needed for BATCH PROCESSING, FILE COMPACT/REPAIR, ETL OPERATIONS, etc.

     ADMIN table:     username=????,   password=????,  userlevel=????,   userlockoutflag=Yes/No,   systemlockoutflag=Yes/No

    Admin (Level 1)  Can update/insert/delete rows in all tables in the database.   Super User (level 2) Can update/insert/delete rows in all tables in the database with the exception of the ADMIN table.   Maintenance User (level 3) Can update/insert rows (no deletes) in all tables in the database except ADMIN table and LOOKUP tables.   Query User (level 4) Can only query tables within the database.  Additionally, for levels 2-4, no queries which access the secure ADMIN table.


    MS-Access users are likely use to having a persistent database connection to their single *.MDB database file.   Microsoft, I believe, recommends no more than 255 users connected to this single file, 20 being maintenance users, and 235 being query users, in order to maintain good performance, but also acknowledging that some customers may be exceeding these limits.

    I have proven to myself that through ODBC connectivity via Win32 Perl, to a single MS-Jet 4.x format MDB file, I can have at least 510 open connections concurrently/simultaneously, from my Windows 7 Laptop, all connections running independent SQL queries concurrently, and producing reliable report output, as long as I bump the Threads property in my ODBC FILEDSN (MS-Access Driver) from default of 3 Threads to 510 Threads.   I have also proven to myself, this same way, that I can have 66 SQL UPDATE processes running independently updating a single MDB file, producing reliable and complete database table row/column data updates.

    That said, I am not sure if I need a custom record locking strategy OR NOT?    To prevent errors and/or concurrency issues which might corrupt any 1-of-500 MDB files in my database. 

    If a custom record locking strategy is best to have, not relying on MS-Jet Engine to do the record locking, my thought is to add a column to my tables to store the username of the user requesting a lock to update the data in a particular row.   Once the user hits SAVE or CANCEL, the username is removed from that column containing username, within that particular row of the table.   Should another user wish to edit that same row/record whilst the record is being edited by another user, the user requesting the new lock will be denied until the user currently editing that record releases the lock by either hitting SAVE or CANCEL, thus removing his/her username within the special column for that particular row.

    My thought is that this would prevent multiple folks from editing the same record in the database at the same time, and there being a race to see who hits SAVE first.  In this scenario, the others users would STEP ON the changes SAVED by the user who first saved their changes.  The other users not having populated in their screen form, the most current changes saved to the database for that row.  My LOCKING strategy would prevent this.   What I also propose to do, since database connections are not persistent, is to fetch the most current copy of the row saved within the database, at the time a user selects a row to edit (and receives the lock), whenever they click on a row populated within a GUI ListView widget, which may not be up-to-date at the time edit mode is entered into.     

    With this custom record locking strategy, I believe my only concern would be trapping Jet Engine errors (not ODBC errors) which might? occur should a limit on the number of SQL requests the Jet Engine is attempting to HANDLE/process, at any given moment in time, be surpassed.  Not sure if this can even happen in a database system of 500 MDB files where ODBC connections are only open just long enough, for any single end-user to any single MDB file, to retrieve a single row for edit, update a single row, delete a single row, or add a single row.  Any BATCH update/inserts/deletes are going to be done on the SERVER SIDE, during off hours when not end-users, other than OPERATIONS FOLKS, are using the database system.  





    Thursday, April 16, 2015 4:01 PM

All replies

  • Wow. I'm not sure that it can work.

    You're working basically off-line. This means that you need to drop a semaphore indicate which row is processed. Then you disconnect. Now another user cannot tell whether your still working on that row or if the application was closed before. Leaving a "false" lock in the system.

    I would give SQL Server (Express will do it also) a chance and evaluate it. This would imho also decrease the normal corruption issues (database files open on unreliable network shares).

    Friday, April 17, 2015 11:35 AM
  • Stefan, I have used this strategy I am showing here for several companies successfully.

    I am now just attempting to take it to the extreme to hundreds of MDB files and tons of users.

    So I know it works for small departments of employees.  

    This is a NO SQL     MS-Jet database file system.

    Only interface is my Perl/ODBC  application which dynamically build SQL statements to execute on the end-user's behalf.    I do not allow end-users access to the MDB files to perform SQL.

    The files will be located on a Windows O/S Server Network, in a directory having permissions to prevent copying, moving, or deleting files.  In order to avoid SQL access, I may have to build into my interface assigning the logged in users to a specific GROUP to have write permissions on the MDB file system.  I'll have to talk that over with a System Admin person. 

    At the other jobs, I created PASSWORD protected MDB files to prevent userd from logging in to use SQL.  They could only log in to the MDB from my Perl/ODBC application interface, where I had the PASSWORD hard coded in my COMPILED executable Perl application (EXE). 

    Friday, April 17, 2015 8:01 PM