ATTEMPT #2: CDatabase/CRecordset observations
Erik Westland -- erikw@individual.com
Thursday, January 25, 1996
Hi,
I'm posting this again because I believe that someone out there can provide
some insight into this siuation. Come on, atleast it's a non-beginner MFC
question.
Thanks,
Erik
On Wed, 17 Jan 1996, Erik Westland wrote:
> Hi,
>
> I've observed some rather strange behavior using MFC's CRecordsets
> (VC++2.2) with SQL server 4.2. I'm not blaming these problems on MFC, I'm
> just wondering how best to use these classes. I also figured that others
> might gain from my sufferage. Please note that all applications discussed
> below are single user apps.
>
> First oddity:
> ~~~~~~~~~~~~
> I developed an application that REPEATEDLY presents the SAME query to a SQL
> Server database. The CDatabase object I created is not readonly and the
> CRecordset nOpenType is CRecordset::snapshot. I loop through the record
> set updating a field in each record (I'm guarenteed that the field changed
> because it was part of the where clause in the query). Occasionally the
> update throws an exception "No rows affected by update." The record that
> it refers to was previously updated, but the subsequent re-query retrieved
> the record again. So when I tried to update it, my target record
> values are already in the DB, EXCEPTION.
>
> One of my associates claims that there are caching issues that complicate
> matters when you use pipes (Database pointers) bi-directionally. My
> workaround was to use two pipes: one to select the data, and the other
> call a stored procedure to update the record (in a transaction). This didn't
> solve the problem. I still occasionally retrieve a record that I
> previously updated. This makes it difficult to keep track of the actual
> number of updates.
>
> Second oddity:
> ~~~~~~~~~~~~~
> I developed an application that presents a SINGLE query to a SQL
> database. The CDatabase object I created is not readonly and the
> CRecordset nOpenType is CRecordset::snapshot and dwOptions is set to
> CRecordset::readOnly. I loop through the record set updating a field in
> EACH record using a second pipe.
>
> The problem is, that I reach a deadlock state. My DBA observed that the
> query doesn't complete running, on the server, until it has looped through
> ALL the records. What appears to be happening is that the query is putting an
> "intent lock" on a page in the server and when the stored procedure tries
> to update a record on that page it is blocked by the lock. Since I can't
> do the update, I don't scroll past the page, DEADLOCK.
>
> I found a tip in "Programming with MFC and Win32" (Recordset:More about
> updates p.446) that indicates you should MoveLast() to "force caching of
> an entire snapshot at once." I tried this and it fixed my problem. Note
> that they weren't discussing the issue of deadlock in this section.
>
> Are there any other solutions to this problem. I've been told that in VB
> you can indicate that you want all the data to be transfered at once.
>
> Thanks and good luck,
> Erik
>
> Erik Westland
> erikw@individual.com
> 800-766-4224 x378
>
>
>
Mike Blaszczak -- mikeblas@interserv.com
Sunday, January 28, 1996
On Thu, 25 Jan 1996, Erik Westland wrote:
>On Wed, 17 Jan 1996, Erik Westland wrote:
>I'm posting this again because I believe that someone out there can provide
>some insight into this siuation. Come on, atleast it's a non-beginner MFC
>question.
If a week is too long for you to wait to get an answer from your peers and
people who volunteer as they have time, you should consider using an official
mode of support. If you use the "Technical Support" command in the "Help"
menu of the IDE, you'll find lots of ways to contact Microsoft to get an
answer.
>> One of my associates claims that there are caching issues that complicate
>> matters when you use pipes (Database pointers) bi-directionally.
It sounds like you and/or your associate are working with a flawed definition
of "pipes". A pipe is an interprocess communication mechanism which connects
your software to the database server. Pipes have nothing to do with "database
pointers".
Pipes aren't inherently bidirectional, but almost every application of pipes
_will_ be bidirectional. When talking to SQL Server, you'll use one pipe to
send your SQL and and another to receive the result set. These pipes are
encapsulated deeply inside of SQL Server's DBLibrary, which is reached from
your application via ODBC. Pipes aren't things that you need to worry about.
So, conjecture about problems with bi-directional pipes sounds like bunk to
me.
>> The problem is, that I reach a deadlock state. My DBA observed that the
>> query doesn't complete running, on the server, until it has looped through
>> ALL the records. What appears to be happening is that the query is
>> putting an
>> "intent lock" on a page in the server and when the stored procedure tries
>> to update a record on that page it is blocked by the lock. Since I can't
>> do the update, I don't scroll past the page, DEADLOCK.
When you do a SELECT from SQL Server, you always put a lock on the rows you
touch. If you think about it, the database _must_ work this way because it
needs to assume you're going to select the data, do something to it, and
update it. (It sounds like this is exactly what your application is doing!)
If you're selecting records that you don't need, you're hurting concurrency
of your application--and your whole server! You need to try and select data
that you'll need and release the locks by doing a COMMIT. Or, if data you're
selecting won't be updated, you should SELECT it using the appropriate
read-only modifier on the SELECT statement.
The database is protecting you becaus you've made two connections to the
database; the database sees this as two users. It needs to make sure User #1
doesn't do something that will affect the integrity of data given to User #2.
You can observe this behaviour using ISQL/W, even.
>> Are there any other solutions to this problem. I've been told that in VB
>> you can indicate that you want all the data to be transfered at once.
Any decent book on database programming should explain these issues, and
books on SQL Server will _certainly_ cover it. I remember material on it in
Microsoft University's "Developing Applications with SQL Server" course from
back when I was certified to teach it, but the courseware may have changed
since then. You should call a Microsoft Education Partner and see if they
have a class for you.
.B ekiM
--
TCHAR szMyronCope[] = _T("He did move to the left, did he not, Richard, to
catch that ball! Yoi!");
| Вернуться в корень Архива
|