maria-discuss team mailing list archive

[Federico Razzoli <federico_raz@xxxxxxxx>]

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@xxxxxxxxxxxxx> escribió:

 Asunto: Re: [Maria-discuss] newbie question
 Para: "Pantelis Theodosiou" <ypercube@xxxxxxxxx>
 CC: "Maria Discuss" <maria-discuss@xxxxxxxxxxxxxxxxxxx>
 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@xxxxxxxxx>:
 
 
 
 
 On Thu, Jun 12, 2014 at 9:57 AM,
 Federico Razzoli <federico_raz@xxxxxxxx>
 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@xxxxxxxxxxxxxxxxxxx
 
 Unsubscribe : https://launchpad.net/~maria-discuss
 
 More help   : https://help.launchpad.net/ListHelp
 
 
 
 
 _______________________________________________
 
 Mailing list: https://launchpad.net/~maria-discuss
 
 Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
 
 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@xxxxxxxxxxxxxxxxxxx
 Unsubscribe : https://launchpad.net/~maria-discuss
 More help   : https://help.launchpad.net/ListHelp