Help with multi-user question

[martinj]

I am developing a PHP/Postgres application. When writing records to the database I first do a SELECT to get the highest ID number, increment, and then write the new ID number for my record. I use this method rather than auto-id or sequences because I need to use an int8 for my ID.

When mutiple process are hitting this function it is common for one of the processes to fail because of the race condition for the new ID number. Postgres transactions insure that the database retains integrity, but I still have a question.

Does Postgres have a feature to lock a table so that competing processes have to wait until I can write out the newly retreived ID number?

Alternatively I can simply have the failed processes simply re-submit their request, but I think this method sounds like a waste of processing effort and may result in thrashing.

[[deleted]]

"I use this method rather than auto-id or sequences because I need to use an int8 for my ID."

an int8... my god man, what are you planning to do? :-)

"Does Postgres have a feature to lock a table so that competing processes have to wait until I can write out the newly retreived ID number?"

Postgres supports the way-cool

SELECT FOR UPDATE

which let's you select a row from a table with the intent to update it later.
This will put an exclusive lock on that row untill you are finished with it.

http://www.postgresql.org/idocs/index.php?sql-select.html

[martinj]

Using int8 to plan for a big future!

Great SELECT FOR UPDATE is the answer.

One more thing. Is the record lock created a wait lock causing subsequent database transactions to wait until it is released OR do subsequent transactions exit early with an error?

Also is the locked record released at the end of the transaction in which it is created, when the locked record is updated, or some other time?

Thanks.

[[deleted]]

You're planning to run this database for several millenia then?

INT8 is 8 bytes, thats 256⁸ = 18446744073709551616 values.
A millennium has 1000365246060 = 31536000000 seconds, so you need to insert
18446744073709551616 / 31536000000 = 584942417 (584 Million) records per second to fill this database in 1000 years.

an Int4 is 256⁴ = 4294967296 values.
You can just fill that if you do 100 inserts per second for one year straight. A more realistical 10 inserts/sec gives you 10 years worth of inserts.

"OR do subsequent transactions exit early with an error"

All locks cause other queries to wait unless you specify that you want your query to abort when it encounters a lock. You woulnd't get very far in databases if all queries aborted by default. :-)

"Also is the locked record released at the end of the transaction in which it is created, when the locked record is updated, or some other time?"

The manual is not very clear, I suggest you try that out using the commandline :-)

[Anon]

QUOTE

All locks cause other queries to wait unless you specify that you want your query to abort when it encounters a lock.

END QUOTE

Actually, in postgres, only transactions that rely on changing the row selected for update will wait or fail. A transaction that simply queries that row will be able to read the old value while the other transaction is uncommitted. If the other transaction commits, it still doesn't affect the transaction since it never sees the new data that was created while it was in process.

[[deleted]]

"A transaction that simply queries that row will be able to read the old value while the other transaction is uncommitted"

which is why this function should always use the 'select for update', so that each 'update' will be done when the previous update is finished.