Simultaneous execution of subs in the hub script

[Gaborone]

Hi, I have a database connected to my hub, and it generates some problems:

If an OP gives a command that access the database, simultaneously as a user loggs on to the hub (which also generates a call to the database) there is a problem.

I thought that everything was serial in the hub script, so that the arriving information was put in a queue, and executed one after another.

However, it seems as if the dataarrival sub, and the newuserconnected sub can be executed at the same time.

Is there anyone who knows anything about this?

How to solve the problem?

/Gaborone

[ButterflySoul]

If you're using AdoDB to access and write to your database, you can :

1) The default isolation level for a connection object (ConnectionObject.IsolationLevel) is set to adXactChaos, which means that you cannot overwrite pending changes from more highly isolated transactions. You can change it to some other value, if you'd like to read changes/records while another sub is preparing to update them

2) The default mode for a connection object (ConnectionObject.Mode) is adModeUnknown.
You can change it to adModeShareDenyWrite for your subs that actually update records (which prevents others from opening connection with write permissions), and to adModeRead for you subs that only read records.

If you want several write access at the same time, you might want to set it to adModeReadWrite instead of adModeShareDenyWrite. But the point is : use adModeRead for the parts of your code that only read info. That way, they shouldn't be denied access to anything =)

3) ADO supports two basic concepts for the addition, deletion, and modification of rows of data.

The first notion is that changes aren't immediately made to the Recordset; instead, they are made to an internal copy buffer. If you decide you don't want the changes, then the modifications in the copy buffer are discarded. If you decide to keep the changes, then the changes in the copy buffer are applied to the Recordset.

The second notion is that changes are either propagated to the data source as soon as you declare the work on a row complete (that is, immediate mode), or all changes for a set of rows are collected until you declare that the work for the set is complete (that is, batch mode). These modes are governed by the CursorLocation and LockType properties.

In immediate mode, each invocation of the Update method propagates the changes to the data source. In batch mode, each invocation of Update or movement of the current row position saves the changes to the Recordset, but only the UpdateBatch method propagates the changes to the data source.

So if you're using an access to the database to save changes about the last time a given user loged in, their IP, etc etc, you could use a counter, work in batch mode, and commit changes to the database every 5 users in NewUserConnected, and every 7 in DataArival for example, to limit collisions

4) The connection and recordset objects both support a State property (ConnectionObject/RecordsetObject.State). If that property is set to anything else than adStateOpen, it means you should enter a loop and wait till it is (if you're sure it will give a connection/recordset when the database is not being kept busy by another sub). Something like

Code: Select all

Do Until RecordsetObject.State = adStateOpen
  frmHub.DoEventsForMe
Loop

5) Eventhough ADO will generally feed your VBS Err object if something goes wrong, the connection object has an error collection that saves problems in case they couldn't be forwarded to the Err object. Check your Err number in VBS, and the Errors collection of your connection object to eventually re-run a step that went wrong.
(the Errors collection can be counted, so simply check if YourConnectionObject.Errors.Count is equal to 0)

6) RecordSets have an EditMode property. It returns a value that indicates the state of editing for the current record. If the value is dbEditNone, go play with it =p If it's DbEditInProgress or dbEditAdd don't =)

7) When you open a recordset with the OpenRecordset method, you can set a lockedit parameter. I've never used it myself, but it looks quite promising and can take the following values :

dbReadOnly

Prevents users from making changes to the Recordset (default for ODBCDirect workspaces). You can use dbReadOnly in either the options argument or the lockedits argument, but not both. If you use it for both arguments, a run-time error occurs.

dbPessimistic

Uses pessimistic locking to determine how changes are made to the Recordset in a multiuser environment. The page containing the record you're editing is locked as soon as you use the Edit method (default for Microsoft Jet workspaces).

dbOptimistic

Uses optimistic locking to determine how changes are made to the Recordset in a multiuser environment. The page containing the record is not locked until the Update method is executed.

dbOptimisticValue

Uses optimistic concurrency based on row values (ODBCDirect workspaces only).

dbOptimisticBatch

Enables batch optimistic updating (ODBCDirect workspaces only).

-----

That's all I can think of for now, but I'm sure there's more =)