0

I got a table of users looking like this:

| id | username | active |

There may be multiple entries with the same username and active=0, but only one unique entry with the same username which has got active=1 set.

What's the best way to ensure that no duplicates can be created by two simultaneous requests? (Session1:SELECT;0 rows, Session2:Select;0 rows, Session1:Insert, Session2:Insert => duplicate entry)

I found out that I could set active=NULL instead of active=0 and set username&active as unique, which would achieve what I am looking for - but this seems to be a rather hacky solution and doesn't reliably work in all DB engines.

I've already seen recommendations to move active=0 entries to a second table, but this seems very messy too and would create overhead in all the select queries.

Zulakis
  • 7,859
  • 10
  • 42
  • 67
  • put in a trigger to check if the update would create a dupe. unfortunately, php triggers can't really abort an insert/update without some ugly hacks. – Marc B Feb 21 '14 at 20:47
  • The best is using transactions. A transaction guarantees that no one else is going to change the contents of your table till you are finished. – Danchoys Feb 21 '14 at 20:51
  • @Danchoys Maybe you could add an example of how you would accomplish this? – Zulakis Feb 21 '14 at 21:09
  • Well, it turned out that transactions are certainly not the right tool in mysql as they don't themselves provide the mechanism for locking the table, which is pretty weird. Anyway it provides it as a separate command: "LOCK TABLES tbl WRITE". – Danchoys Feb 21 '14 at 21:34

3 Answers3

2

You may use table locking. The command locks the table and prevents everyone from writing into it.

<?
    $dbh->exec("LOCK TABLES tbl WRITE");

    // Check whether you have an active element.
    // You can make it inactive or whatever. You
    // are the only one who has access to this table.

    $dbh->exec("UNLOCK TABLES");
?>

You can read more on this in the documentation.

Danchoys
  • 739
  • 1
  • 8
  • 14
  • What are simultaneous insert's going to do? Throw a PDOException? Also, what happens if there is an critical error after the locking and the table isn't unlocked again? This could turn out pretty nasty. – Zulakis Feb 21 '14 at 21:46
  • There are no simultaneous inserts as only one client can have the table locked for him at a time. So the code between the LOCK and UNLOCK command is executed one after another. The table is automatically unlocked when the client closes the connection. From the documentation: "If the connection for a client session terminates, whether normally or abnormally, the server implicitly releases all table locks held by the session (transactional and nontransactional). If the client reconnects, the locks will no longer be in effect." – Danchoys Feb 21 '14 at 21:50
  • So the `LOCK TABLES` command is going to produce a PDOException on one of the simultaneous connections? – Zulakis Feb 21 '14 at 21:57
  • No, other connections will wait for the table to be unlocked. – Danchoys Feb 21 '14 at 22:05
0

Putting this information apart is not messy. If you now a record has active set to true, you don't really need to write that other entries have it set to 0 : the rules say so. This means you're repeating information, which is far worse than having 2 tables (which is IMO perfectly fine).

This means your database is not in second normal form, I think.

Basically : you're saying "row 3 is the active row, and row 4 is not, and row 5 is not, and row 6 is not" ... and so on. "row 3 is the active row" is sufficient

greg0ire
  • 22,714
  • 16
  • 72
  • 101
  • Well, a) "Moving" a row to another table is far more complex then just setting `active=0`, b) as already said, this is going to bloat all the select queries where rows are being joined based on userID (which happens quite regular) c) I don't think that a simple boolean qualifies as "repeating information". Follow that logic, you would have to create 2 tables for each boolean column you got. – Zulakis Feb 21 '14 at 21:08
  • a) b) granted c) I beg to disagree. Just because you're male doesn't mean everyone else with the same name is female. Just because you are administrator doesn't mean everyone else with the same name isn't. But if you are active, everyone else with the same name is not. It's a fact *you* stated. So yes, yes, yes, you're repeating information many, many times. – greg0ire Feb 21 '14 at 22:10
  • a) It's not moving a row, it's changing an id in a table. The id of the active user for this username. That's all. – greg0ire Feb 21 '14 at 22:24
  • Oh, I think I know what you mean now. I thought of putting all inactive rows into a second table, you mean a second table like `| username | activeID |`, right? – Zulakis Feb 21 '14 at 22:30
  • @Zulakis: yes, exactly, I was wondering what was "moving a row" about... glad you figured it out. – greg0ire Feb 21 '14 at 22:34
  • This table should have a unique constraint on username. – greg0ire Feb 21 '14 at 22:37
  • 1
    Yeah, I quite understand now. That really is an interesting idea! Very nice, I will definately think about the positive/negative effects of this change and evaluate if it would have a overall positive outcome. – Zulakis Feb 21 '14 at 22:50
  • related : http://stackoverflow.com/questions/637894/constraint-for-only-one-record-marked-as-default/15776793 – greg0ire Feb 21 '14 at 23:02
0

If you want to stick with your setup, make all INSERTs use active=0. You can then run an UPDATE to set active=1 with a LIMIT of 1. This is messy, but should work.

UPDATE table SET active=1 WHERE username="<name>" ORDER BY active DESC LIMIT 1

The ORDER BY will make sure the active=1 record floats to the top and the LIMIT will make sure only 1 record is "selected" for the update. The UPDATE LIMIT is on how many rows CAN be affected, not how many WILL be affected.

Brent Baisley
  • 12,641
  • 2
  • 26
  • 39