Re: parallel interpreter/db problem. transaction didn't fix.

mysql_query("START TRANSACTION", $link2);
$q2=mysql_query("SELECT pictures.pid AS pid
FROM pictures,counter
WHERE pictures.pid>counter.pid
LIMIT 1", $link2);
if ($row2=mysql_fetch_assoc($q2)) {
mysql_query("UPDATE counter SET pid=$row2[pid]", $link2);
$n=$row2['pid'];
} else { //reached end of table.
mysql_query("UPDATE counter SET pid=1", $link2);
$n=1;
}
mysql_free_result($q2);
mysql_query("COMMIT", $link2);

Check to see whether your queries WORK. If they do not work,
print out the query and the value of mysql_error(). (For actual
production code, log it somewhere the user won't see it).

the transaction makes no difference in the outcome.

The transaction may be counterproductive. For certain applications,
having uncommitted changes be NOT visible to other connections until
they are committed can bite you big time. One example of this is
the "last modified" timestamp being used to identify recently changed
stuff, to actually make the changes in the real world (e.g. create
mailboxes). When it can take a long time to commit a transaction,
the "last modified" date can go from months old to hours old without
ever having been only a few minutes old, fooling provisioning software
trying to see all the changes and not miss any.

I've even tried locking the tables, but that only results in invalid
resource errors.

Then find out why your query didn't work, and fix it (print
mysql_error() for the failing query). You need to lock ALL the
tables you're going to use, and if you use aliases, you may have
to lock them under the name of the alias.

when this picture code is called twice in sequence, (2 separate
interpreters, possibly by separate processors), I get the same image. I
shouldn't be getting the same image twice. this is one of those
hair-pulling sessions.

Everything I've tried results either in no pictures at all (picture
placeholders)

Please explain how that can happen in terms of the generated HTML.
Are you generating numbers for which there is no image file?

with both the same sizes due to errors, or with the same
pictures on the same page.

transactions, no transactions, using a counter is out of the question.
I don't understand what's even happening here. the transaction *should* fix
this! Am I missing something

Gordon L. Burditt
.

Relevant Pages

  • Re: struck database query
    ... but the sybase server seems to take forever to ... dump transaction with no_log ... The most likely reason is a lock - your query is waiting on a lock that is ...
    (comp.databases.sybase)
  • Re: Working Transactions somehow started not to work
    ... Transaction and locking are not properties of recordsets but are properties ... Opening a served-based cursor will put a lock on ... If a connection is closed, ... SL> and are not associated necessarily with server-based cursors. ...
    (microsoft.public.access.adp.sqlserver)
  • RE: Combo box bound field change
    ... Modify the row source to include both fields. ... Make the bound column the company name column. ... now you can change the company name any time and your query ... Make a copy of your transaction table, but copy only the structure, not the ...
    (microsoft.public.access.forms)
  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • RE: Combo box bound field change
    ... now you can change the company name any time and your query ... Make a copy of your transaction table, but copy only the structure, not the ... Modify the New transaction table and change the company name field to ... I'd run a report to show what all that company purchased. ...
    (microsoft.public.access.forms)