Harpreet Dhaliwal wrote:
> About the concurrency control, if i have both Select Max(id) and insert
> (id)
You know, if you don't top-post you don't need to tell me what you're
talking about ;)
> in the same function, then would it be
> a nice idea to put both these statements in the same function or differenct
> functions and then put the insert in a transaction and lock the table for
> any further query till insert commits.
I just realized you can do this in one statement - that wouldn't solve
the concurrency problem, though.
You can do this:
INSERT INTO tbl_email (option_public, agency, id)
SELECT $1, $2, MAX(id) + 1
FROM xyz;
> Also, should i go with a table level lock or a row level lock in this
> scenario?
I just realize you don't so much need a lock, you need a serialized
transaction. I can't say I know a lot about locks, I usually prevent
needing them.
Thinking of which... If there is _any_ relation between the MAX(id)
you're selecting from xyz and the record you insert into tbl_email, how
do you guarantee that you're actually looking at the correct record in xyz?
Basically the problem occurs when a new record was inserted and
committed into xyz while you were processing the insert of the previous
one. MAX(id) would return the id of the newly inserted record, _after_
you started processing.
I can't imagine what purpose the MAX(id) from a different table would
serve. If there isn't a relation between those fields, what benefit does
your method have over a sequence?
Regards,
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
