Locking a Table Within an INSERT
From: Elliott <shevine_at_aol.com>
Date: 6 Jul 2004 13:10:39 -0700
Message-ID: <149413ab.0407061210.38cc8b7d_at_posting.google.com>
I'm trying to perform this insert in a non-procedural environment:
Date: 6 Jul 2004 13:10:39 -0700
Message-ID: <149413ab.0407061210.38cc8b7d_at_posting.google.com>
I'm trying to perform this insert in a non-procedural environment:
INSERT INTO table
(column1,
column2)
SELECT
:col1value,
MAX(column2) + :count
FROM table
WHERE column1 = :col1value
;
My problem is that two or more processes might be executing this statement concurrently. If so, they could get the same value for
MAX(column2). But I want the second process to get the updated value for that aggregate that is set by the first process, and insert an incremented value based on THAT.Is there a way I can write this statement so one instance locks out the second one? In my environment, one statement is all I'm allowed.
Thanks,
Elliott
Received on Tue Jul 06 2004 - 22:10:39 CEST