Postgresql lock shared table with multiple users

Question

So, I'm having a synchronization issue here; Let's explain the situation:

I have a table called Sendings, that consists of these columns:

id   sending_object_id    type   destinatary

The id is the automatic serial id postgresql gives to each table. The sending object_id is a custom identifier, and type and destinatary...don't really matter for this particular question.

This is an example of how the table would look like with data:

  id   sending_object_id    type   destinatary
  1          1               1         A7
  2          1               1         B1
  3          2               2         A1
  4          2               2         A2
  5          3               1         B8
  6          4               1         N1

The same "object_id" can be sent to multiple destinataries, as you can see. Now my synchronization problem comes when two different users, with two different computers, try to insert a new sending.

User A introduces the new sending information and the DB engine makes a SelectMax(sending_object_id) in order to create the new sending_object_id for the registry.

User B, at the same time, introduces a new sending, making another SelectMax(sending_object_id).

How could I avoid duplicated sending_object_ids when inserting new data, at the same time?

Thanks in advance.

Richard Huxton · Accepted Answer · 2013-10-29 11:49:55Z

1

You either need to lock the table while creating a new sending_object_id, or, if you don't care exactly what the number is create your own sequence and call nextval on it.

answered Oct 29, 2013 at 11:49

Richard Huxton

23.4k5 gold badges42 silver badges53 bronze badges

What would be the better way to lock a table? And, if I write a sequence, can it be called only for the first insert? (sending_object_id can be repeated for the same series of sendings). Thanks in advance
– aran
Commented Oct 29, 2013 at 12:12
1

I'd just call nextval() in the application at the start of a batch - ignore the result and use currval() for the inserts. That way they all have the same (new) value.
– Richard Huxton
Commented Oct 29, 2013 at 13:10

Add a comment |

alexius · Accepted Answer · 2013-10-29 11:50:20Z

1

I think need to use sequences for this. They guaranteed to be unique in any cases.

answered Oct 29, 2013 at 11:50

alexius

2,58620 silver badges24 bronze badges

Same question than I did to Richard, the nextval for the sequence, can be called only for the first insert of the sending_id set? Thanks in advance
– aran
Commented Oct 29, 2013 at 12:13
1

yes, you can call it only when you need. or even just fiil nextval('sequence_name') as default value for column. so first insert will have default value and all others has same number (you can get it by using RETURNING id clause in insert query).
– alexius
Commented Oct 29, 2013 at 12:22

Add a comment |

Collectives™ on Stack Overflow

Postgresql lock shared table with multiple users

2 Answers 2

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related