5

First of all, my question has nothing to do with this one.

When querying a MySQL database for insertion: suppose several users try to insert rows on the same time on the same database table: does MySQL RDBMS lock the insertion process when one insertion is in progress ? Or does it allow multiple insertions at the same time ?

I ask this question because I am naming images inserted by users using the timestamp. However, timestamp uses seconds. So if 50 users insert images in the same seconds: how MySQL will treat them ? Will I have 50 images with the same name ?

Community
  • 1
  • 1
  • possible duplicate of [how do i handle concurrent inserts in mysql table and fetch the correct insert id](http://stackoverflow.com/questions/2124200/how-do-i-handle-concurrent-inserts-in-mysql-table-and-fetch-the-correct-insert-i) – Mark Silverberg Jul 17 '14 at 13:14
  • 2
    Also see this link about concurrent inserts from dev.mysql.com: http://dev.mysql.com/doc/refman/5.0/en/concurrent-inserts.html – Mark Silverberg Jul 17 '14 at 13:14
  • 3
    It's a bit unclear what you're asking. Can you detail what exactly you may be worrying about? Yes, several processes can `INSERT` into a table at once, no problem. – deceze Jul 17 '14 at 13:14
  • @Skram Thank you for the link, but we are not dealing about the same question. I use timestamp to name insertion files that can be done by several users. timestamp works in seconds. You can guess the problem –  Jul 17 '14 at 13:16
  • Why cant you use a autoincrement key to name your images? No duplication of names this way even if inserted in same time. – bansi Jul 17 '14 at 13:19
  • @bansi Because I need to search for files by their names, in certain cases –  Jul 17 '14 at 13:20
  • You're not making much sense. Why does the name have to be a timestamp, if that's causing problems? Why can't you use an auto increment id *and* a filename to search by? – deceze Jul 17 '14 at 13:23
  • 4
    if 50 users insert images in the same seconds, if you don't have unique value constrain on the file name you will have 50 images with same name, otherwise first insert will succeed and all other 49 will fail. – bansi Jul 17 '14 at 13:25
  • @bansi Thank you. So you are saying MYSQL RDBMS allows simultaneous insertions ? That is my main question. –  Jul 17 '14 at 13:26
  • 4
    This isn't really about MySQL at all, that's an irrelevant detail. Even if insertions aren't simultaneous but all nicely one after the other... a computer can do millions of individual one-after-the-other insertions per second. Your problem is that you're using timestamps as a unique id, which actually isn't unique at all. Use something else as id, period. – deceze Jul 17 '14 at 13:28
  • @deceze I am not using timestamp as an ID. I use it just to name files, but each file has its auto increment and unique ID. –  Jul 17 '14 at 13:33
  • Then I'm really confused what exactly your question is. – deceze Jul 17 '14 at 13:35
  • @deceze My question, in simple German words: can I have 50 pictures with the same name if they are inserted in the same time (the same second), each one inserted by a different user, and each one having its ID (auto increment) ? –  Jul 17 '14 at 13:37
  • 2
    Of course, yes. Why wouldn't you? Only if you set a `UNIQUE` constraint on the filename column will this *not* work if the filenames are not unique, but if you set that constraint you'd already know that. – deceze Jul 17 '14 at 13:45
  • @deceze Thank you very much for your precious comments. I understand now. –  Jul 17 '14 at 13:46

1 Answers1

1

Yes, you will (almost certainly) get multiple images with the same timestamp.

You can use AUTO_INCREMENT, UUID(), or another approach to generate unique values, but timestamps (even microsecond precision ones) are not unique.

Vatev
  • 7,493
  • 1
  • 32
  • 39
  • 1
    Could you give me an official link to prove what you say ? I really need a precise information. –  Jul 17 '14 at 13:24
  • 3
    I added some links to the manual for the builtin methods. As to the non-uniqueness of timestamps - I think its pretty obvious: nothing prevents 2 threads from inserting 2 records at the same time, it is actually a desired feature. – Vatev Jul 17 '14 at 13:28
  • 3
    @begueradj Forget the "at the same time" idea - you're not talking "in the same cpu cycle", you're talking "in the same second". I don't know of any RDBMS that can't insert 2 rows/second, whether it's truly in parallel or not – Basic Jul 17 '14 at 14:42