Atomicity of the data in mysql

Question

One of the client has asked us to write a store procedure in mysql, which states that one data should be accessed by only one resources (Even if their happens to be Multiple resources ready to read the data, whomsoever will come first will take the lock first and would change its flag so that not other resources should be able to take a lock on this data row in a table. Store procedure is to be written for it, i believe it to be similar to bank transaction management, but i have no clue how to write a stored procedure for it, any help will be highly appreciated, Thanks well in advance.

You are asking about fundamental concepts. You should find a tutorial about stored procedures and transactions, *not* ask for quick answers in SO. SO is a Q&A site, you can't expect people to post multipage tutorials. If you *don't* understand about transactions or locking, you'll end up freezing the entire application. — Panagiotis Kanavos, Dec 15 '17 at 08:43
@PanagiotisKanavos not like that i have not written the Store procedure, its like i have not implemented for this kind of scenario which involves transaction and asks for ACID property to be in place — Mavericks, Dec 15 '17 at 08:46
As I already said, fundamental concepts. Find a tutorial or course. — Panagiotis Kanavos, Dec 15 '17 at 08:49

score 0 · Accepted Answer · answered Dec 16 '17 at 16:18

    Step : 1

CREATE TABLE `test_db`.`Jobs` (
  `id` INT NOT NULL,
  `JOB` VARCHAR(45) NOT NULL,
  `status` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC));

Step : 2


DELIMITER $$
create procedure aabraKaDaabra(IN ids INT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;
START TRANSACTION;
select id from Jobs where id=ids for update;
update Jobs set status = 'Submitted' where id=ids;
commit;
END$$; 

Step : 3


select * from test_db.Jobs order by id desc;

Note:
 Make sure that you have inserted a few of the value for the table.

Step : 4
call test_db.aabraKaDaabra(1);

This is what i was expecting and solved it , it worked like a charm

Atomicity of the data in mysql

1 Answers1