Which the best transaction isolation level can be used for this case?-CodePudding

We are trying to delete and then insert the user related data while login.

Please find the below:

Delete from user_data where user_id=5; // user_id is not PK/Unique Key 
insert into user_data(id,data,user_id)
select id,data,5 from default_data;

If user login into application simultaneously 2 different request (separate transaction per request) Case 1 If Isolation Level = Read Committed

Request 1 (Transaction 1) -Delete the data for user 5
Request 2 (Transaction 2) -Delete the data for user 5
Request 1 (Transaction 1) -Insert the data for user 5
Request 2 (Transaction 2) -Insert the data for user 5

In this scenario data becomes duplicate. Then i used Isolation level as Serializable Case 2 If Isolation Level = Serializable

Request 1 (Transaction 1) -Delete the data for user 5
Request 2 (Transaction 2) -Delete the data for user 5 -- could not serialize access due to concurrent delete
Request 1 (Transaction 1) -Insert the data for user 5
Request 2 (Transaction 2) -Failed

In this case, it is working fine. I could see most of the article or document prefered Isolation Level = Read Committed.

Please let me know how to handle this case

Please try the code from your end: Execute the query:

create table user_data(id int primary key,data varchar(20),user_id int);
insert into user_data(id,data,user_id) values (1,'John',5),(2,'Tom',5),(3,'Jerry',5),(4,'Test',5);
select * from user_data;

Then create new connection and execute the below code:

Isolation Level = Read Commit

-- Transcation 1

BEGIN;
DELETE FROM user_data WHERE user_id=5; 
INSERT INTO user_data(id, data, user_id)
SELECT 22,'John1',5
union all
SELECT 23,'Tom1',5
union all
SELECT 24,'Jerry1',5

-- Transcation 2

BEGIN;

DELETE FROM user_data WHERE user_id=5; 

INSERT INTO user_data(id, data, user_id)
SELECT 32,'John2',5
union all
SELECT 33,'Tom2',5
union all
SELECT 34,'Jerry2',5

Then release the transaction 1 by executing commit;

-- Transcation 1

commit;

Then Transaction 2

-- Transcation 2

commit;

My Expected result is mentioned below :

id	data	user_id
32	John2	5
33	Tom2	5
34	Jerry2	5

But the coming result is

id	data	user_id
22	John1	5
23	Tom1	5
24	Jerry1	5
32	John2	5
33	Tom2	5
34	Jerry2	5

CodePudding user response：

After some searching I could see LOCK TABLE is the option to lock row.

Please find the below update query:

LOCK TABLE user_data IN SHARE ROW EXCLUSIVE MODE;
Delete from user_data where user_id=5; // user_id is not PK/Unique Key 
insert into user_data(id,data,user_id)
select id,data,5 from default_data;

CodePudding user response：

BEGIN;

DELETE FROM user_data WHERE user_id=5; -- record will be locked

INSERT INTO user_data(id, data, user_id)
SELECT id, data, 5 FROM default_data;

COMMIT; -- lock will be released, other transactions can now continue