The problem is the dbgrid creates a cursor - which causes all sorts of locks
to be placed on the db. One workaround (if the dataset is not too large) is
to scroll to the end of the query and then back to the first record - this
will release all locks. You can try this manually and see all locks
disappear. (The sp_lock stored procedure will show you the locks in the
database). The TransIsolation level only kicks in once the query is
complete. In a dbgrid, the query is not complete because the grid only
reads data as needed and needs to keep the cursor open so that if the user
scrolls down, more data can be read.
The other workaround is to select the data into a temporary table, and hook
that up to the dbgrid. It will still generate locks, but these will not
affect the data that is to be updated.
The best workaround is don't use a dbgrid!
Regards,
Dennis.
> > I've just upgraded a Delphi 3 / SQL Server 6.5 application
> to Delphi 5 /
> > SQL
> > Server 7 and have encountered locking problems with queries
> that prevent
> > users from updating data that other users are viewing in a read-only
> > datagrid (am using BDE).
> >
> > The application used to work fine in the original versions.
> >
> > Looking at the current activity SQL Server 7 Enterprise
> Manager, for each
> > row of data retrieved in the read-only query (some of which
> are displayed
> > in
> > a TDBGrid) there are various page and key shared locks on
> the tables used
> > in
> > the query join. These locks do not disappear until another
> query is run
> > which is connected to the same database component. While
> the first user
> > has
> > run the query and is viewing the data, the second user
> cannot update data
> > rows that hae been retreived from the read-only query.
> >
> > The database component has TransIsolation Level of
> tiReadCommitted, which
> > according to the Delphi documentation should release the
> locks as soon as
> > the query is executed, and not hold onto them.
> >
> > I'm probably missing something obvious here. Any help
> gratefully accepted.
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
