Roberto, Aria cannot lock rows. With SELECT FOR UPDATE you would get no errors (because MariaDB and MySQL, for some reason, don't like errors) but you would have no lock. Lock the whole table, or switch to InnoDB. The standard isolation level guarantees the protection you need. Federico -------------------------------------------- El jue, 12/6/14, Roberto Spadim <roberto@spadim.com.br> escribió: Asunto: Re: [Maria-discuss] newbie question Para: "Pantelis Theodosiou" <ypercube@gmail.com> CC: "Maria Discuss" <maria-discuss@lists.launchpad.net> Fecha: jueves, 12 de junio, 2014 17:36 hi guys! thaks for explanations and ideas, well the first problem is ok, change to -1 and -2 and after 2 and 1, that was what i'm considering at the first time i see primary key duplicated, and since mysql don't suport defered insert/update/delete, ok let's workaround :) now the full problem....think about a list with 'locked' and 'unlocked' positions, and important fields that make that entry information unique... every position (id column) must be >0, all positions <=0 i'm considering as 'inserted but not completed transition' |id|locked|other important data||1|false|blabla||2|false|blabla2||3|true|blabla3||4|false|blabla4||5|false|blabla5||6|true|blabla6| my problem now is: change it 5 to position 2, and reposition the list, position 2 will become position 4, and position 4 will become position 5, position 3 will not change since it's locked='true' i'm considering something like, change position 5 to -5, change position 4 to 5, change position 2 to 4, change -5 to 2, but how to do this only with sql language? and the second problem is: change position 2 to position 5, position 4 will become position 2, position 5 will become position 4 any other idea about how to do this? i think that's the easiest way, considering that i will need a lock at positions 2,3,4,5 before start updates, right? maybe i could do this with select * from test where id in (2,3,4,5) for update, or lock table ideas are wellcome 2014-06-12 8:32 GMT-03:00 Pantelis Theodosiou <ypercube@gmail.com>: On Thu, Jun 12, 2014 at 9:57 AM, Federico Razzoli <federico_raz@yahoo.it> wrote: Duplicate data cannot be inserted in MariaDB, in no cases. PostgreSQL has a different behaviour: transactions can optionally be "deferred", which means that integrity checks will be done on commit. This means that you can temporarly insert inconsistent data, and fix them before commit.The same option is available for foreign keys and other features that I don't remember. Correct. Standard SQL behaviour is checking constraints at the end of a statement. The optional ability to defer a constraint check means that the check is done at the end of the transaction and not at the end of statement. (Postgers and Oracle can do that, SQL-Server does not.) No duplicate data will be inserted either, in both cases, deferred or not constraints. Any inconsistent data is only visible during the transaction, by the transaction - with the MVCC model no other transaction can see them before that one is committed. What MySQL and MariaDB does is different than standard/common SQL behaviour, it is checking the constraints after every row update. That is causing the problem that Roberto faces. But we are talking about MariaDB, so... what you can do, in you example, is: UPDATE ... SET id = NULL WHERE id = 1; -- does not exist, right? UPDATE ... SET id = 1 WHERE id = 2; -- change other values UPDATE ... SET id = 1 WHERE id IS NULL; This is fine - as long as there are not any NULL values already on the table in the id column. If there are, the 3rd UPDATE will convert all of them to 1. You can use negative values - of course if and only if all your ids are either positive or NULL: UPDATE ... SET id = -2 WHERE id = 1; UPDATE ... SET id = -1 WHERE id = 2; UPDATE ... SET id = -id WHERE id < 0; This can easily be extended for more complex updates and we only need two UPDATE statements - and I suppose better inside a transaction, for InnoDB engine: UPDATE ... SET id = CASE id WHEN 1 THEN -2 WHEN 2 THEN -1 WHEN ... END WHERE id IN (1,2) ; UPDATE ... SET id = -id WHERE id < 0; Pantelis Or, if your case is more complex than this: 1) lock the table 2) drop the pk 3) update 4) unlock Ciao Federico _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp -- Roberto Spadim SPAEmpresarialEng. Automação e Controle -----Adjunto en línea a continuación----- _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp