0

Consider we have a table t_log in database Test. The t_log looks like the following

create table `t_log`(
`F_log_id` bigint(20) not null AUTO_INCREMENT,
`F_trans_id` bigint(20) not null,
`F_action` tinyint(3) not null,
primariy key(`F_log_id`)
)engine=innodb

Suppose F_action field can only take several specific values,like 1 and 2. For F_action=1 records, coresponding F_trans_id must differ from each other. While for F_action=2 records, coresponding F_trans_id can take same values.

For example records like the following in table should be valid

F_log_id | F_action | F_trans_id
  1            1          1001
  2            2          1002
  3            2          1002

However records like the following should not be valid

F_log_id | F_action | F_trans_id
  1            1          1001
  2            1          1001
  3            2          1002

Since our unique restriction on F_trans_id depends on the specific value of F_action, we cannot merely build the unique index on F_trans_id and F_action.

For consistency, inserting records into t_log is put into a transaction like this

start_transaction()
select and check if valid
do inserting
commit()

However in high-concurrency environment, there might be two inserting transaction arrived almost at the same time, for example, both entered start_transaction(), suppose two records are both (F_action=1, F_trans_id=222), when select and check, both found record is valid and insert it. Then t_log will have invalid two records.

Is there any good way two prevent this kind of invalid records? Thank you.

Junjie
  • 469
  • 1
  • 4
  • 14
  • How do you pick the value for F_trans_id?. It's not mentioned in the question and it is important. – LHristov Jul 30 '14 at 11:01
  • @LHristov, F_trans_id is picked from other tables. For example, this procedure may served as an interface called by other program， and F_trans_id will be passed as input parameters, generated or picked by external program – Junjie Jul 30 '14 at 11:35
  • Cant you clear that picking algorithm? Or just set value to F_trans_id in the transaction, I mean create a table where it is autoincrement, make an insert to that table and get it back with `last_insert_id`. Look [here](http://stackoverflow.com/questions/3837990/last-insert-id-mysql) – LHristov Jul 30 '14 at 11:40
  • @LHristov, there's no such picking algorithm. you may think F_trans_id was pass by some external app, and the external app may pass any kind of F_trans_id(which we may hardly predicted). One of our target is to avoid repeatly simultaneous call. by saying *repeatly*, I mean two simultaneous call by the external app with F_action=1 and F_trans_id with same value – Junjie Jul 30 '14 at 12:33

1 Answers1

0

Use arithmetic to generate unique values as required:

  • create a new column, say action_hash int
  • create a unique index on (F_trans_id, action_hash)
  • create a trigger that populates action_hash with distinct values when needed

In your example, the simplest thing I can think of is:

new.action_hash = new.F_log_id * (new.F_action - 1)

Which will be zero when F_action is 1, this requiring F_trans_id to be unique, but otherwise not.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Thank you, @Bohemian, but this rather tricky(action_hash formula totally depends on specific value interpretation of action!).. Is there some more general way to this problem(I think this should be a very common requirements in mysql)? for example I tried to do select for update specfic row, but the delay between select and insert, and the RR transaction Isolation level in innodb make this won't work. thanks any way. – Junjie Jul 30 '14 at 10:57
  • Yes it is a solution for specific values of `F_action`, but isn't that the whole point of the question? Only when `F_action` is 1, must the other column be unique? If that's not the question, please clarify. – Bohemian Jul 30 '14 at 16:28