transaction restart

[Christian Welzel]

Hi there,
currently i'm looking for a way to repeat a rolled back transaction
in a nice way.
my problem: in my webapp (a browser game) i get a lot of deadlocks
in the mysql innodb tables. mysql then terminates the transaction
and tells me to restart the transaction.
for this i have to rebuild my queries and send them to the database
(and probably get the next deadlock).

Does anybody know a nice way to build some logic into the php code
to handle this?

--
MfG, Christian Welzel aka Gawain@Regenbogen

GPG-Key: http://www.camlann.de/key.asc
Fingerprint: 4F50 19BF 3346 36A6 CFA9 DBDC C268 6D24 70A1 AD15

[Good Man]

Christian Welzel <ga****@camlann.dewrote in news:666rulF2icci8U1
@mid.individual.net:

Hi there,
currently i'm looking for a way to repeat a rolled back transaction
in a nice way.
my problem: in my webapp (a browser game) i get a lot of deadlocks
in the mysql innodb tables. mysql then terminates the transaction
and tells me to restart the transaction.
for this i have to rebuild my queries and send them to the database
(and probably get the next deadlock).

Does anybody know a nice way to build some logic into the php code
to handle this?

do you really need transactions for this?

it seems like you're having a MySQL issue, in particular so many
"deadlock"s?

[Christian Welzel]

Good Man wrote:

do you really need transactions for this?

I dont know.
We some other problems due the update of some tables what we think
we can solve using transactions. But now we are in some kind of
discrepancy.

it seems like you're having a MySQL issue, in particular so many
"deadlock"s?

This could be the case. The game code is rather old and the access
pattern is a real nightmare.

--
MfG, Christian Welzel aka Gawain@Regenbogen

GPG-Key: http://www.camlann.de/key.asc
Fingerprint: 4F50 19BF 3346 36A6 CFA9 DBDC C268 6D24 70A1 AD15

[Jerry Stuckle]

Christian Welzel wrote:

Hi there,
currently i'm looking for a way to repeat a rolled back transaction
in a nice way.
my problem: in my webapp (a browser game) i get a lot of deadlocks
in the mysql innodb tables. mysql then terminates the transaction
and tells me to restart the transaction.
for this i have to rebuild my queries and send them to the database
(and probably get the next deadlock).

Does anybody know a nice way to build some logic into the php code
to handle this?

While the other comments are correct about correcting the way you access
your tables, sometimes it's not practical. If not, about the only way
you can do it is to set up an array holding all of your SQL statements
and re-execute them. It's not bad if you have all of the information,
but if you need to fetch data (i.e. last_insert_id) and use it in the
next statement, it gets a little harrier. Then you almost need a SQL
templating engine.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[NC]

On Apr 10, 9:01 am, Christian Welzel <[e-mail]:

>
currently i'm looking for a way to repeat a rolled back transaction
in a nice way.
my problem: in my webapp (a browser game) i get a lot of deadlocks
in the mysql innodb tables. mysql then terminates the transaction
and tells me to restart the transaction.
for this i have to rebuild my queries and send them to the database
(and probably get the next deadlock).

Does anybody know a nice way to build some logic into the php code
to handle this?

Well, it's possible that the reason you're getting deadlocks in the
first place is that too many clients are trying to access the
database
simultaneously. So you may be attacking the wrong bottleneck; rather
than thinking about code, you should think about upgrading the
database
server or at least change some configuration settings...

Failing that, you could try batch processing:

1. A PHP script writes a complete set of queries into a text file
located in a pre-defined directory.
2. Every, say, minute, a cron job starts a shell (or PHP) script that
searches the pre-defined directory for text files, executes SQL in
them, and deletes ones that have been executed.

You may need to do some equilibristics to make sure that only one
instance of batch processor runs at a time.

Cheers,
NC

[Christian Welzel]

C. (http://symcbean.blogspot.com/) wrote:

Yes - write deadlock free code.

This is not that easy as we inherited the code from the former
programmer of that game. Now we try to extend it a bit ...
but that brings the problems with the database.
We are currently initiating a rewrite of the code, but until than
the old code as to run for another round...
So we try to fix the old code to get more time to write the new one.

But @all, thanks for your hints.

--
MfG, Christian Welzel aka Gawain@Regenbogen

GPG-Key: http://www.camlann.de/key.asc
Fingerprint: 4F50 19BF 3346 36A6 CFA9 DBDC C268 6D24 70A1 AD15

[Rik Wasmus]

On Thu, 10 Apr 2008 22:27:53 +0200, C. (http://symcbean.blogspot.com/)
<co************@gmail.comwrote:

On 10 Apr, 17:01, Christian Welzel <[e-mail]:

>Hi there,
currently i'm looking for a way to repeat a rolled back transaction
in a nice way.
my problem: in my webapp (a browser game) i get a lot of deadlocks
in the mysql innodb tables. mysql then terminates the transaction
and tells me to restart the transaction.
for this i have to rebuild my queries and send them to the database
(and probably get the next deadlock).

Does anybody know a nice way to build some logic into the php code
to handle this?

Yes - write deadlock free code.

Forget about using database bound transactions unless you can
encapsulate every transaction by locking all the tables right at the
beginning - but this is kind of horrible too - what happens if your
lock fails.

You can't fix the problem in PHP. You can't fix it in MySQL - you need
to fix it in how your application hangs together and how it uses
resources.

Don't use transactions at the database level:
1) Never delete rows if you can possibly avoid it
2) Never update rows if you can avoid it, try to use replace instead
of update

Euhm, never ever use REPLACE instead of UPDATE if not needed... It WILL
remove the previously existing row first, possibly ending in an ON DELETE
CASCADE nightmare...
--
Rik Wasmus