MySQL Table Locks etc

intellix · 6 Jul 2009 at 21:38

Hi, Having a problem in SQL where multiple sessions are trying to do the same thing at once, causing a duplication method of what should happen...

for example:

(assuming this_value is 1)

old_value = 0

SELECT this_value FROM t1 WHERE id = 1

old_value += this_value

DELETE this_value FROM t1 WHERE id = 1

old_value should be 1... but then other sessions are SELECTING the row before it has been deleted, and is getting performed more than once... so the old_value could end up 2,3,4,5,6 depending on the amount of sessions that get ahold of it!

I've tried SELECT.... FOR UPDATE but that doesn't seem to lock the SELECT statement at all

This is transaction stuff in InnoDB with the SELECT + DELETE within the single transaction :/

Beansprout · 6 Jul 2009 at 22:12

Try this

intellix · 6 Jul 2009 at 22:41

thanks

was a good read, but honestly! that FOR UPDATE just does not work lol

I have SELECT... FOR UPDATE which should apparently stop the stuff being read until a COMMIT, but it still gets done :s

JBod · 7 Jul 2009 at 22:40

Can always do a table/row readlock.

intellix · 8 Jul 2009 at 18:28

Tried a Write lock, which is in atm but STILL getting duplicated rows... I just don't get it.... everything that is supposedly meant to fix it... doesn't

How can a database engine be transactional if it doesn't even work?

intellix · 9 Jul 2009 at 00:50

There is 1 row in this table... calling the same thing twice from seperate connections still pulls up 1 row... shouldn't this lock it and display 0 rows for the second connection? Instead it displays:
1
1

instead of
1
0

<?php
function db() {
$user = "user";
$pass = "pass";
$db = "this";
$server = "localhost";
if(is_null($link)){
$link = @mysql_connect( $server, $user, $pass );
}
mysql_select_db( $db, $link ) or die ( "Couldn't open $db: ".mysql_error() );
return $link;
}

function test(){
$link = db();
mysql_query('SET autocommit=0',$link);
mysql_query('BEGIN',$link);
$result = mysql_query('SELECT move_id FROM movements LOCK IN SHARE MODE',$link);
print mysql_num_rows($result);
print "<br/>";
$link2 = db();
$result2 = mysql_query('SELECT move_id FROM movements LOCK IN SHARE MODE',$link2);
print mysql_num_rows($result2);
}

test();
?>

I get the same result if I try LOCK movements WRITE for the first connection