mySQL transaction problem / question

[joozt]

I got the following table called actions, it is a queue table in which actions are stored (the DB engine of this table is InnoDB).

These actions are picked up my multible servers using diffrent database connections  (each server will have its own connection), also each server will have its own server_id.

 

The table actions has the following fields:

+----------+----+----------+------+

| action_id|lock|server_id|action|

+----------+----+----------+------+

 

Example contents:

|1| 0 |null|bla1 |

+-+-+----+----+

|2| 0 |null|bla2 |

+-+-+----+----+

|3| 0 |null|bla3 |

+-+-+----+----+

|4| 0 |null|bla4 |

+-+-+----+----+

 

The servers will constantly pull actions from this table and execute them, but the main problem I am trying to prevent is that diffrent servers execute the same action (which will screw up the end result).

 

I need to write a solid mySQL transaction to prevent servers from picking up the same action.

 

I have never worked with Transactions in MySQL, I read some stuff about it but I can't really make sence of it.

 

I though about the problem and came up with a query something like this:

UPDATE actions SET server_id=$server_id WHERE action_id = (SELECT MIN(action_id) FROM actions WHERE sevrer_id IS NULL AND lock = 0)  limit 1;

SELECT action FROM actions WHERE server_id = $server_id and lock=0;

Commit;

update actions set lock=1 where action_id = $action_id

 

I am sure there is an easier way or better way to solve this problem, so thats why I am posting here.

I really lost on this problem, so any help would be greatly apriciated!

 

Sorry for my crappy english and thanks in advance!

[fenway]

I'm sorry, I don't understand.

[Mchl]

Transactions basically work like this

 

START TRANSACTION;
QUERY1;
--rollback if it fails
QUERY2;
--rollback if it fails
...
QUERYn
--rollback if it fails
COMMIT;

 

Oh, and 'lock' is MySQL's reserved word, so not really a good name for column.

[joozt]

Transactions basically work like this

 

START TRANSACTION;
QUERY1;
--rollback if it fails
QUERY2;
--rollback if it fails
...
QUERYn
--rollback if it fails
COMMIT;

 

Oh, and 'lock' is MySQL's reserved word, so not really a good name for column.

 

Thanks I think this is what i was looking for, btw do you think my queries are oke?

 

The only thing i want to do is get the first record from the que (actions) table and lock it so other servers (connections) don't pick up the same action.

[Mchl]

Not sure you need transactions at all here. MySQL queries are atomic, so there is no possibility that if two servers run first query at once they will select same row.

[joozt]

Ya thats what i though, but just wanted to make sure, since i wasn't sure.

 

Thanks alot again

[kickstart]

Hi

 

You basic idea of :-

 

UPDATE actions SET server_id=$server_id WHERE action_id = (SELECT MIN(action_id) FROM actions WHERE sevrer_id IS NULL AND lock = 0)  limit 1;
SELECT action FROM actions WHERE server_id = $server_id and lock=0;
Commit;
update actions set lock=1 where action_id = $action_id

 

Looks fine. I take it the potential issue you are worried about is selecting a record to process on one server, processing it and then finding that in the mean time another server has also picked it up to process. Your solution should fix that. I might be tempted to have a column to indicate what state it is in being processed rather than a lock column (eg, Status with values indicating available to be processed, being processed or processing complete), which might make it easier to spot any errors (especially if the processing gets more complex).

 

All the best

 

Keith

[joozt]

Ya thanks again, my table is bit more complex then I posted it here, but didn't want to post it here since it will only make this more confusing

 

atm I got it working

 

 

 

[joozt]

Well now i need to use a transaction.

 

I have got a tabel task (a task is build up out of a number of actions):

 

The task table looks the following:

id, name, numer_of_actions, actions_completed

 

So after and action is completed I want do add +1 to actions_completed.

 

But since different servers handle the actions, I can't just update the number with +1, in this case i need to use a transaction I think.

 

Can't seem to make the correct transaction though atm, perhaps because I can't think straight after 8 hours of work

 

But if anyone is willing to help or point me in the right direction , thanks alot dam i thank you guys alot

 

(I am still kind of new to mySQL, so I hope I don't seem lazy)

[kickstart]

Hi

 

No need for a transaction for that. Just update it in one statement.

 

You would use a transaction if you are doing some complex processing with seperate table updates, and should some of the later updates fail you want to back out those updates already completed.

 

Eg, imagine recording a payment and debiting it from one account and crediting it to another account.

Update table of incoming payments to say it is being processed

Subtract the payment from the source account.

Add it to the destination account

Update table of incoming payments to say it has been processed.

 

If any of these stages failed you would want to reverse the previous stages.

 

In your case it is a single SQL statement:-

 

UPDATE task SET actions_completed = actions_completed + 1 WHERE id = $someid

 

The only issue you have is stopping 2 servers from processing the same action, and a transaction wouldn't help with that. You just need to mark the action as being processed.

 

All the best

 

Keith

[joozt]

Yes so what your saying is that this couldn't fail because it is 1 update statement?

 

 

What if task is build up out of action 2 actions

 

server 1 handels action 1

server 2 handels action 2

 

 

And both servers run the query at the same time:

UPDATE task SET actions_completed = actions_completed + 1 WHERE id = $someid

 

Running this at the same time would not result in: actions_completed = 1 instead of 2?

 

Hmm I clearly have to learn some more about mysql and will ask for soem time once i made this deadline

 

Thanks for the help without it I probably wouldn't make my deadline

 

 

[joozt]

 

The only issue you have is stopping 2 servers from processing the same action, and a transaction wouldn't help with that. You just need to mark the action as being processed.

 

 

Ya my scrips work the following:

 

I have 1 daemon constantly running. There will only be 1 daemon running on each server.

 

The daemon is allowed to start a static number of child proccesses.

 

If the number of processes running < allowed number running.

 

I check if there is an action in que with the following function:

 

 

function get_conversion_queue_item()
{

	global $db_connection;

	require_once('Db.php');	
	//die(mysql_get_server_info ($connection));
	$DB = new Db($db_connection);

	$sql="UPDATE mcv_queque 
					SET mcv_id='".MCV_ID."'
					WHERE mcv_queque.mcv_id = 0 
					AND locked = 0 
					AND mcv_status_id = 1 order by priority, mcv_task_id limit 1";

	//get query result (2)=update and returns the number of rows updated
	$result=$DB->query($sql,2);

	if($result==1)
	{

		$sql="SELECT id FROM mcv_queque WHERE mcv_id = ".MCV_ID." and locked=0 limit 1";


		$queue_id=$DB->getCell($sql);

		if($result!==false)
		{
			$sql="UPDATE mcv_queque 
					SET locked='1'
					WHERE id = $queue_id"; 

			$result=$DB->query($sql,2);

			return $queue_id;
		}
		else
		{
			return false;
		}
	}
	else
	{
		return false;
	}
}

 

 

I think the above function is pretty solid in selecting an item from que.

 

If there is and item fround in que then the daemon wil start a chill process to execute the action and in this child procces if the conversion is succesfull it will update the action_completed +1;

 

If the conversion fails I count the number of fails to a static number of retries (number of retries is a number set in a settings table in my database).

[joozt]

Just read this article about transactions and is saying about the same as you kickstart

 

http://www.databasejournal.com/features/mysql/article.php/3382171/Transactions-in-MySQL.htm

 

Thanks

 

 

 

[kickstart]

What if task is build up out of action 2 actions

 

server 1 handels action 1

server 2 handels action 2

 

 

And both servers run the query at the same time:

UPDATE task SET actions_completed = actions_completed + 1 WHERE id = $someid

 

Running this at the same time would not result in: actions_completed = 1 instead of 2?

 

I don't think that the 2 statements could actually run at the same moment. I would expect that even if submitted at precisely the same moment one would be processed before the other.

 

All the best

 

Keith

[joozt]

What if task is build up out of action 2 actions

 

server 1 handels action 1

server 2 handels action 2

 

 

And both servers run the query at the same time:

UPDATE task SET actions_completed = actions_completed + 1 WHERE id = $someid

 

Running this at the same time would not result in: actions_completed = 1 instead of 2?

 

I don't think that the 2 statements could actually run at the same moment. I would expect that even if submitted at precisely the same moment one would be processed before the other.

 

All the best

 

Keith

 

 

nice just the answer I was hoping for I gues we will find out if your right in the test phase of my project

[Mchl]

I don't think that the 2 statements could actually run at the same moment. I would expect that even if submitted at precisely the same moment one would be processed before the other.

 

All the best

 

Keith

 

Exactly. Depending on storage engine used, either whole table, or a single row will be locked for the execution of one statement. Any statements trying to modify same table/row have to wait until current statement finishes it's job.