CODEWITHSUNDEEP
mysql
Outhrics
Outhrics

Reputation: 79

mysql concurrency

sorry if my question has been asked before or if it's too obvious but i really need to clear this. thanks for your help.

in a multi-user interface, if the same transaccion from different users arrive to server at the same time, what will happen?

i have the next table:

create table table_x (
  cod char(2) not null,
  desc varchar(45) not null,
  primary key (cod)
);

where these calls arrive at the same time to the server:

call sp_s('1','a');
call sp_s('1','b');
call sp_s('1','c');

what sp_s (in param_a char(2), in param_b varchar(45)) does is:

declare var_count tinyint default 0;

set var_count=
(select count(*) 
from table_x 
where cod=param_a);

if(var_count=0) then
insert into table_x values (param_a, param_b);
else
update table_x set desc=param_b
where cod=param_a;
end if;

if the registry doesn't exists it inserts it, but if it exists the sp updates it.

is this a good way to do it without using autoincrement? whats the best way to avoid concurrency if i'm not using auto_increment?

thanks very much for your time.

Upvotes: 0

Views: 1343

Answers (2)

Jaydee
Jaydee

Reputation: 4158

You could check out SELECT... FOR UPDATE which issues row level locks it may help you out, but I can't remember the details.

http://dev.mysql.com/doc/refman/5.1/en/innodb-locking-reads.html

Alternatively instead of using INSERT and UPDATE you could use

REPLACE into table_x values (param_a, param_b);

Which will insert a row if one doesn't exist, or replace the values in the existing row.

Upvotes: 1

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26699

This depends on transaction isolation level. You can however use insert into... on duplicate update, and unique index on cod.

Upvotes: 1

Related Questions