Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a web application that is accessed by multiple users from different locations worldwide. Let's say I have an "edit" form for a specific document and two (or more) users are editing the document at the same time. I know that both of the users can edit the document and submit it, but user1 will not see that user2 made changes to the document and might submit an older version of it.

I was thinking about adding some kind of a lock, but then, if user1 only opened the document for edit, but never changed anything (and kept it open) user2 will never be able to edit it. Therefore, I was thinking to add a Timeout for the editor, but then user1 might time out before he finished doing his changes to the document (let's say, went out for launch).

The question is, how would one prevent the document from being edited from one user while the other changes it?

share|improve this question

1 Answer 1

What you are looking for is called Optimistic Concurrency Control.

Add an int column to your table called version. Start at 0.

create table mytable (
  id bigserial primary key,
  whatever text,
  version int not null default 0
);

Add a row:

insert into mytable (whatever) values ('something');

Read a row:

select id, whatever, version from mytable where id=1

Results:

1, 'something', 0

Update a row:

update mytable set whatever = 'something new', version = version + 1 where id = 1 and version = 0

If that row was not found, throw an exception. If you have a REST API on top of your database, you should use HTTP 409 Conflict.

If you use an ORM like Hibernate, you can just add a field with a @Version annotation and it will handle this for you.

share|improve this answer
1  
+1 for optimistic concurrency. Note that if you happen to be using SQL Server, there is a native data type called rowversion which automatically assigns a new value each time the record is written to. This same data type was previously known as timestamp, but that syntax is deprecated. –  Joel Brown Aug 12 '13 at 1:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.