mysql concurrency

Question

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.

there should be locks in a DBMS to handle these cases of concurrency — ascanio
– ascanio, Commented Jul 14, 2011 at 15:36
The problem is when two concurrent scripts wants to insert new item. They both will read that such item does not exist and both will try to insert it. You cannot lock not existent record in this case, but locking all table is just not cool. — Gedrox
– Gedrox, Commented Jul 14, 2011 at 15:39
In PostgreSQL I have used function pg_advisory_lock to overcome such issues. Before select I obtain the advisory lock by integer value I'm working with, then I'm sure the block isn't executed simultaneously. — Gedrox
– Gedrox, Commented Jul 14, 2011 at 15:41

Maxim Krizhanovsky · Accepted Answer · 2011-07-14 15:46:37Z

1

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

answered Jul 14, 2011 at 15:46

Maxim Krizhanovsky

26.8k5 gold badges62 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jaydee · Accepted Answer · 2011-07-14 15:46:47Z

1

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.

answered Jul 14, 2011 at 15:46

Jaydee

4,1481 gold badge21 silver badges20 bronze badges

2 Comments

Gedrox Over a year ago

It won't help in case the record does not exist. There is nothing to lock then.

Jaydee Over a year ago

Innodb uses "next key" locking and "gap locking" on the index to get round that problem (the phantom problem) techsoftcomputing.com/MySql%20ref/innodb-next-key-locking.html

Collectives™ on Stack Overflow

mysql concurrency

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related