Tim

Is there a chance to this setting (TableMaxReadLockCount) become 0 (zero) ?
Why should the data from two or three years ago be locked ? The table has
around 1M records and it still used by POS system everyday. When some users
are selecting data from past it locks the table to the users adding data
from current date.

It can be fixed by separating things and create two databases or split the
data into another table but this is not the question. I do not know if I am
being clear but...

I would apreciate any comments.

Eduardo

Eduardo,

<< Is there a chance to this setting (TableMaxReadLockCount) become 0 (zero)
? Why should the data from two or three years ago be locked ? >>

Setting the TableMaxReadLockCount value to a very low value increases
concurrency at the expense of making bulk queries and/or filters fairly
slow.

As for the second question - how would DBISAM know if you're going to update
that data or not ?  The answer is that it doesn't, therefore it has to
assume that it may be updated while it is reading the data for the purposes
of executing a filter or query.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

> Setting the TableMaxReadLockCount value to a very low value increases
> concurrency at the expense of making bulk queries and/or filters fairly
> slow.
Yes, I agree with it. But when I say 0 (zero) I intend to say no locks and
therefore no concurrency.

> As for the second question - how would DBISAM know if you're going to
> update that data or not ?  The answer is that it doesn't, therefore it has
> to assume that it may be updated while it is reading the data for the
> purposes of executing a filter or query.
I know TableMaxReadLockCount is a engine property but I know when the data
will be updated of not. For example, if you create another runtime property
to TDbisamquery to override this setting, then you left to developed decide
where the data need to be locked or not. The default value could be -1
(works without change) or even a boolean property, LockData bring True as
default value. I understand your point of view.

I am asking this question because since I update from 4.22 b4 to 4.25 b7
some users are complaining about system lock and I saw at customer site what
he is talking about.

The situation is 20 users acessing the table for update (inserts, update and
delete) and just one is selecting data without update using SQL. When this
user start a query, it locks the other users to update and they can´t even
start a transaction (restricted). I am planning to downgrade just this site
to 4.22b4 and see if it happens because customer swear it is a new problem.

Eduardo

Eduardo

>>The situation is 20 users acessing the table for update (inserts, update and
>>delete) and just one is selecting data without update using SQL. When this
>>user start a query, it locks the other users to update and they can´t even
>>start a transaction (restricted). I am planning to downgrade just this site
>>to 4.22b4 and see if it happens because customer swear it is a new problem.

Hummm....Why a select would lock a table? There is something wrong here.

Mauricio Campana Nonino
Nonino Software

Mauricio

Yes, select performs some lock according the above setting.

If you increase the value then it will increase the number of locks. The
default value is 100 and if you have 1000 records in your select then DBISAM
will lock the table 10 times. It will lock 100 records, then unlock and lock
again "walk" another 100 records unlock and lock again and so on.

That is what I am talking about.

Eduardo

Mauricio,

<< Hummm....Why a select would lock a table? There is something wrong here.
>>

Reads always result in locks if data has to be read from the table.  The
engine must make sure that it is reading a consistent view of the table in
terms of the indexes, blobs and rows.

--
Tim Young
Elevate Software
www.elevatesoft.com

Eduardo,

<< Yes, I agree with it. But when I say 0 (zero) I intend to say no locks
and therefore no concurrency. >>

DBISAM can't read a table without acquiring read locks unless it is
absolutely sure that no one will be updating the table during that time.
The only way to do that is to flag the table files as read-only at the file
system level.

<< I know TableMaxReadLockCount is a engine property but I know when the
data will be updated of not. For example, if you create another runtime
property to TDbisamquery to override this setting, then you left to
developed decide where the data need to be locked or not. The default value
could be -1 (works without change) or even a boolean property, LockData
bring True as default value. I understand your point of view. >>

I understand that you would like to control this, however this is a loaded
gun that could really shoot us in the rear if the developer doesn't
understand what they are doing completely or, for some reason, a user uses
the ODBC driver or the DBSYS utility to update the table without the
developer's (or his/her application's) knowledge.

<< I am asking this question because since I update from 4.22 b4 to 4.25 b7
some users are complaining about system lock and I saw at customer site what
he is talking about.

The situation is 20 users acessing the table for update (inserts, update
and delete) and just one is selecting data without update using SQL. When
this user start a query, it locks the other users to update and they can´t
even start a transaction (restricted). I am planning to downgrade just this
site to 4.22b4 and see if it happens because customer swear it is a new
problem. >>

This is not a new issue, and the TableMaxReadLockCount property was added
specifically to address this issue.  Previously, without this property,
DBISAM would grab one read lock and hold it for the duration of the bulk
table scan.   Of course, the easiest way to avoid this situation is to avoid
forcing DBISAM to perform bulk table scans during SELECT or filter
operations by optimizing the SELECT queries and filters.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

> I understand that you would like to control this, however this is a loaded
> gun that could really shoot us in the rear if the developer doesn't
> understand what they are doing completely or, for some reason, a user uses
> the ODBC driver or the DBSYS utility to update the table without the
> developer's (or his/her application's) knowledge.
Ok, I understand the point.

> This is not a new issue, and the TableMaxReadLockCount property was added
> specifically to address this issue.  Previously, without this property,
> DBISAM would grab one read lock and hold it for the duration of the bulk
> table scan.   Of course, the easiest way to avoid this situation is to
> avoid forcing DBISAM to perform bulk table scans during SELECT or filter
> operations by optimizing the SELECT queries and filters.
I know this feature is not new but I am starting to investigate this
situation because the application is running since December, 2005 and after
I upgrade to the last version of DBISAM, the customer starts to complain
about this issue, that´s all.

I will downgrade this customer to 4.22b4 (my last version) and see what
happens and let you know about the situation.

Eduardo

Tim

One more question related with this subject.

The locks we are talking about are made in the database (all tables) ?
If not which tables DBISAM locks ?

I) A simple select involving just one table it locks only this table ?
II) A select involving two joins, DBISAM locks driver table and joined too ?

What is happening at this customer is very weird because I am using restricted transactions and this issue is happening with different tables. IOW,
the select is using just one table and the other users (with restricted transactions) are using another tables. To be honest, when I try to find a
solution to fix the problem, sometimes I miss the main idea and try to see this things from other ways. That´s is the reason to ask these things.

Thanks for any comments.

Eduardo

Eduardo,

<< The locks we are talking about are made in the database (all tables) ?
If not which tables DBISAM locks ? >>

The locks are made in the dbisam.lck file.

<< I) A simple select involving just one table it locks only this table ?
II) A select involving two joins, DBISAM locks driver table and joined too
? >>

Yes, and yes.

<< What is happening at this customer is very weird because I am using
restricted transactions and this issue is happening with different tables.
IOW, the select is using just one table and the other users (with restricted
transactions) are using another tables. >>

That's very weird because you are correct in assuming that one won't affect
the other because they are using different tables.  I would double-check and
make sure that the restricted transactions are all being used correctly.
That's the only way you could affect the locking in a way that would, in
turn, affect the read-locking going on during the SELECT.

--
Tim Young
Elevate Software
www.elevatesoft.com