Hello there,
two guys of our developer team ( Lorenzo and Federico )
have seen a strange behaviour (in 8.4 and 9.1.1 ) on update, and I think is
a bug or something really strange or I not understand correctly this
behavior .
I explain now ( begin transaction or auto commit is the same):
create table testup ( a int ) ;
alter table testup add primary key (a ) ;
insert into testup values (1);
insert into testup values (2);
update testup set a=a+1 ;
ERROR: duplicate key value violates unique constraint "testup_pkey"
DETTAGLI: Key (a)=(2) already exists.
by the way :
test=# update testup set a=a-1 ;
UPDATE 2
SUCCESFUL
-- REVERSE ORDER --
Now create the same table with rows in reverse physical order:
create table testup2 ( a int ) ;
alter table testup2 add primary key (a ) ;
insert into testup2 values (2) ;
insert into testup2 values (1);
update testup2 set a=a+1 ;
UPDATE 2
SUCCESFUL
by the way :
test=# update testup2 set a=a-1 ;
ERROR: duplicate key value violates unique constraint "testup2_pkey"
DETTAGLI: Key (a)=(1) already exists.
I have tested in Oracle 11gR1 and 11gR2 without the same behaviour :
Oracle :
SQL> create table a ( b number ) ;
Tabella creata.
SQL> alter table a add primary key (b) ;
Tabella modificata.
SQL> insert into a values (1 ) ;
Creata 1 riga.
SQL> insert into a values (2) ;
Creata 1 riga.
SQL> commit ;
Commit completato.
SQL> update a set b=b+1 ;
Aggiornate 2 righe.
SQL> commit ;
Commit completato.
SQL> update a set b=b-1;
Aggiornate 2 righe.
SQL> commit;
Commit completato.
In MySQL 5.1.58 with InnoDB the behaviour is more strange (always for +1
and indipendent from the reverse order O_o) :
mysql> create table testup ( a int ) engine innodb ;
Query OK, 0 rows affected (0.21 sec)
mysql> alter table testup add primary key (a) ;
Query OK, 0 rows affected (0.34 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into testup values (1) ;
Query OK, 1 row affected (0.12 sec)
mysql> insert into testup values (2) ;
Query OK, 1 row affected (0.15 sec)
mysql> commit ;
Query OK, 0 rows affected (0.00 sec)
mysql> update testup set a=a+1 ;
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY' (-- like
postgres!)
mysql> update testup set a=a-1 ;
Query OK, 2 rows affected (0.16 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> commit ;
Query OK, 0 rows affected (0.00 sec)
mysql> update testup set a=a+1 ;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select * from testup ;
+---+
| a |
+---+
| 0 |
| 1 |
+---+
2 rows in set (0.00 sec)
-- REVERSE ORDER --
mysql> truncate table testup ;
Query OK, 0 rows affected (0.11 sec)
mysql> insert into testup values (2) ;
Query OK, 1 row affected (0.12 sec)
mysql> insert into testup values (1) ;
Query OK, 1 row affected (0.17 sec)
mysql> update testup set a=a+1 ; (-- O_O is tottaly different from
postgres!)
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> update testup set a=a-1 ;
Query OK, 2 rows affected (0.16 sec)
Rows matched: 2 Changed: 2 Warnings: 0
In MySql with Myisam is tottaly different and similar to Oracle :
mysql> create table testup_myisam ( a int ) engine myisam ;
Query OK, 0 rows affected (0.17 sec)
mysql> insert into testup_myisam values (2) ;
Query OK, 1 row affected (0.00 sec)
mysql> insert into testup_myisam values (1) ;
Query OK, 1 row affected (0.00 sec)
mysql> update testup_myisam set a=a+1 ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> update testup_myisam set a=a-1 ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
-- REVERSE ORDER --
mysql> truncate table testup_myisam ;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into testup_myisam values (1) ;
Query OK, 1 row affected (0.00 sec)
mysql> insert into testup_myisam values (2) ;
Query OK, 1 row affected (0.00 sec)
mysql> update testup_myisam set a=a+1 ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> update testup_myisam set a=a-1 ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
The problem for us is when we develop or migrate applications between
different databases.
By the way I think is not right that an update on the same set of rows will
be successful or failed if the rows are ordered or not, no?
I think it is something in correlation with visibility of rows in MVCC
(update=>insert + delete tuple).
What do you think about?
See you soon
Regards, Mat
