Another way to prevent repeated insertion of business data

Cut to the chase

Recently there was a lot of rain, and I came up with another solution (Plan 3) for preventing repeated insertion.

A scene

In an e-commerce project, a commodity can be bound to multiple labels, and a label can be bound to multiple commodities. Therefore, there must be an intermediate table for the association of commodities and labels. Assume that the structure of the associated table GOOds_label is as follows:

A and B perform label binding on commodities at the same time:

A -> Bind commodity 1 and label 1 B -> Bind commodity 1 and label 1Copy the code

So our expectation is that one of A’s and B’s operations will only succeed, and the other will remind him of his failure.

There will be several options to achieve our desired expectations

Solution 1: Distributed lock

Pseudo code:

var goods_id = 1
var label_id = 1
var id = getId()
var count = select count(0) from goods_label where goods_id = $goods_id and label_id = $label_id and is_delete = 2
if count > 0{
    return "Existing association"
}
var l = lock.try(GOODS_LABEL_LOCK_KEY + goods_id)
ifl ! = nil{ try{ var row = insert into goods_label(id, goods_id, label_id, is_delete) values ($id.$goods_id.$label_id, 2)
        if row > 0{
            return "Success"
        }
    return "Failure"
    } finally{
        l.release()
    }
}else{
    return "Operation timed out"
}
Copy the code

It’s common, it’s common… Without a soul

Scheme 2: federated unique index

With goods_id and label_id and is_delete set as joint unique indexes, the pseudocode can be written as follows:

var goods_id = 1
var label_id = 1
var id = getId()
var count = select count(0) from goods_label where goods_id = $goods_id and label_id = $label_id and is_delete = 2
if count > 0{
    return "Existing association"
}
var row = insert into goods_label(id, goods_id, label_id, is_delete) values ($id.$goods_id.$label_id, 2)
if row > 0{
    return "Success"
}
return "Failure"
Copy the code

The code is much cleaner, but the index structure of the table can be complex, resulting in TPS degradation, and the IS_DELETE field is limited to only having 1 and 2 results for the same record.

Solution 3: Pre-delete (self-naming)

var goods_id = 1
var label_id = 1
var id = getId()
var count = select count(0) from goods_label where goods_id = $goods_id and label_id = $label_id and is_delete = 2
if count > 0{
    return "Existing association"
}
var trans = beginTrans()
// 1
var row = insert into goods_label(id, goods_id, label_id, is_delete) values ($id.$goods_id.$label_id1),if row > 0{
    // 2
    row = update goods_label set is_delete = 2 where id = $id and (select t.count from (select count(0) as count from goods_label where goods_id = $goods_id and label_id = $label_id and is_delete = 2) t) = 0   
    if row > 0{
        trans.commit()
        return  "Success"
    }
    trans.rollback()
    return "Failure"
}

return "Failure"
Copy the code

This scenario executes two operation transaction SQL:

• Tag 1: Inserts data first and sets the state to deleted.
• Tag 2: Updates the status of previously inserted data to undeleted, but only if the current association does not exist.

As you can see, if tag 2 fails, the entire transaction is rolled back and the tag 1 operation is undone, so no dirty data is generated.

Scheme comparison

• Plan one: traditional, but practical, soulless.
• If there are too many unique conditions, the index will be more complex. In addition, is_delete some scenarios may also allow the same resource to be deleted multiple times, so that the effect of a unique index cannot be achieved. The advantage is the simplicity of the business code!
• Plan three: I occasionally whim, do not know whether good or bad, but can achieve the purpose xD