Issue: get_or_create causes duplicates with multiprocessing
Author: jspayne
State: closed
Created: 2017-11-10 19:43:58

Name: peewee
Version: 2.10.2

I've tried implementing multiprocessing with peewee in two different ways.

Example 1:

```
import peewee as pw
from playhouse.pool import PooledMySQLDatabase
import multiprocessing as mp


db = PooledMySQLDatabase("options_db", threadlocals=True, user="opts_user", passwd="opts_pass", max_connections=10)


def main():
    with db.execution_context():
        db.create_tables([TestFile], safe=True)
    set1 = ['foo', 'bar', 'baz', 'bun']
    set2 = ['foo', 'bar', 'bun', 'bog', 'buz', 'baz']
    data = [set1, set2, set1, set2, set1, set1]
    p = mp.Pool(mp.cpu_count())
    # The map creates a list of Counters, one for each result file
    p.map(worker, data)


def worker(files):
    for f in files:
        with db.execution_context():
            TestFile.get_or_create(file_name=f)


class TestFile(pw.Model):
    file_name = pw.CharField(unique=True, index=True)

    class Meta:
        database = db


if __name__ == "__main__":
    main()
```

Example 2:

```
import peewee as pw
import multiprocessing as mp


db = pw.Proxy()


def main():
    main_db = pw.MySQLDatabase("options_db", threadlocals=True, user="opts_user", passwd="opts_pass")
    db.initialize(main_db)
    db.connect()
    db.create_tables([TestFile], safe=True)
    db.close()
    set1 = ['foo', 'bar', 'baz', 'bun']
    set2 = ['foo', 'bar', 'bun', 'bog', 'buz', 'baz']
    data = [set1, set2, set1, set2, set1, set1]
    p = mp.Pool(mp.cpu_count())
    # The map creates a list of Counters, one for each result file
    p.map(worker, data)


def worker(files):
    worker_db = pw.MySQLDatabase("options_db", threadlocals=True, user="opts_user", passwd="opts_pass")
    db.initialize(worker_db)
    for f in files:
        with db.atomic():
            TestFile.get_or_create(file_name=f)


class TestFile(pw.Model):
    file_name = pw.CharField(unique=True, index=True)

    class Meta:
        database = db


if __name__ == "__main__":
    main()
```

I can see how the first might not work because of the object cloning magic that happens with multiprocessing.  But in the second case, each process should have its own connection to the DB, and have its own local data, no?

In both cases I get exceptions due to duplicate entries even though I'm using get_or_create (`peewee.IntegrityError: (1062, "Duplicate entry 'bog' for key 'testfile_file_name'")`).  I also sometimes get `TestFileDoesNotExist: Instance matching query does not exist:
SQL` or save point does not exist errors.

Thoughts?

`get_or_create()` is not a silver bullet...race conditions abound.

Your best bet is to use database constraints (as you are) and do something like select for update or create->get.

```python
with db.atomic():
    try:
        test_file = TestFile.select().where(TestFile.filename == f).for_update().get()
    except TestFile.DoesNotExist:
        TestFile.create(filename=f)
```

Or, better, since it seems you'd rather create them:

```python
try:
    with db.atomic():
        TestFile.create(filename=f)
except IntegrityError:
    pass
```

Ok, good to know.  I went with the latter solution and it seems to be working.  Thanks!