CODEWITHSUNDEEP
phpmysqltransactions
jahajee.com
jahajee.com

Reputation: 3743

MYSQL TRANSACTION alternative?

Using PHP / MYSQL I am trying to create a simple search for my website. I have all pages data in mytable and I am processing thousands of rows in this sequence:

 1. Query for 1 row with a particular id in `mytable` with column `status=0`
 2. Using that id do something in `other` table(s) 
 3. When done, update `mytable` with`status=1`

EDITED

In step 2 I process all data from step 1 e.g.

To speed up the process I thought of running more than 1 script so that I can process all rows faster but then I came to know about concurrency problem. I read Mysql TRANSACTION and that this process is a classic example of TRANSACTION.

For records all tables are INNODB so that I could use TRANSACTION / LOCK. If I use TRANSACTION then the problem is that each script waits till a complete row process (step 2 takes longer) and then the process takes equally same time as 1 row is processed at one time only !

Can someone suggest, how can I proceed with some other alternative to TRANSACTION OR LOCK OR some other table structure so that I can process each ROW in mytable faster / concurrently by running same PHP script multiple times together ?

I have tried my hands on SOLR, LUCENE and SPHINX and am sorry, but all are too technical for me to use, sorry for this limitation on my end :( .

Upvotes: 2

Views: 704

Answers (3)

bestprogrammerintheworld
bestprogrammerintheworld

Reputation: 5520

I'm not sure I understand your exact issue, issue but I give it a try....

You do like this:

 1. Query for 1 row with a particular id in `mytable` with column `status=0`
 2. Using that id do something in `other` table(s) 
 3. When done, update `mytable` with`status=1`

Can't you do like this instead?

 1. Query for all rows in `mytable` with column `status=0`
 2. Save those rows in a temporary table
 3. Iterate the temporary table to do some things in `other` table(s) 
 4. When done, update all rows that should be affected in `mytable` with`status=1` (with TRANSACTIONS)

This way you would only have to use TRANSACTIONS when updating. I think this approach would be much faster because you would only have to do only single query for selection.

UPDATE

You could always do something like this to create a temporary table with id's that has status zero:

(First create a table called temptable manually ONE time)

DROP temptable;
INSERT INTO `temptable`( mytable_id )
SELECT  id FROM `mytable`WHERE status = 0

Upvotes: 1

DBHash.com
DBHash.com

Reputation: 584

You can set the transaction_isolation level to READ UNCOMMITTED to minimize locking. Set it at session level in the php script.

InnoDB by default has REPEATABLE READ isolation level. 

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
SET SESSION tx_isolation='READ-UNCOMMITTED';

To know more about the isolation levels check http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html

Upvotes: 2

sofend
sofend

Reputation: 667

What is being done in the 'other' table. Depending on whether you need external computation you can do this all via a single query with sub selects. If the computation is based on data in the two tables you can likely write it that way. SQL doesn't require iteration in most cases, but acts directly on each cross-product tuple of the joined relations.

In short, tell us more about what precisely you're doing in steps 1 & 2 and then we can see.

(Alternatively, you could switch to a DB that support stored procedures and run it that way.)

Upvotes: 0

Related Questions