transaction restart

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

Re: transaction restart

Christian Welzel wrote 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?

Re: transaction restart

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

Re: transaction restart

On 10 Apr, 17:01, 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?
>

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

If you must use locking then use MySQL named locks - they can be
queried without locking and a lock requested with a sensible timeout.

With a bit of thought in your database design it should be possible to
build a system which is performant, consistent, normalized and dead-
lock free.

C.

Re: transaction restart

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.
jstucklex@attglobal.net
==================

Re: transaction restart

On Apr 10, 9:01 am, Christian Welzel wrote:
>
> 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

Re: transaction restart

NC wrote:
> On Apr 10, 9:01 am, Christian Welzel wrote:
>> 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...
>

Proper design of the program and it doesn't matter how many people are
accessing the database - deadlocks will never occur.

> 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
>



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

Re: transaction restart

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

Re: transaction restart

On Thu, 10 Apr 2008 22:27:53 +0200, C. (http://symcbean.blogspot.com/)
wrote:

> On 10 Apr, 17:01, 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?
>>
>
> 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