Concurrency/Atomicity in ZSQL/ZOracleDA ?
Hello, I have a probably simple question, but got different opinions on the matter, and I'm seriously in need of a quick answer. I need to make a ZSQL method that inserts data in a table this way: 1) first, generate a new ID with something like SELECT MAX(ID)+1 AS my_id FROM ... (using a sequence is not an option for me for various reasons) 2) then, INSERT with the ID previously generated ( is INSERT INTO ... VALUES (<dtml-var my_id>.... the right way to do this ?) 3) display my_id to the user for future reference. First, I'm not sure how to do 3) : how do I pass my_id back to the original DTML document (which calls the ZSQL method with dtml-call) ? Also (main question), is there something particular to do to ensure SELECT+INSERT is done in an atomic way that avoids concurrency problems ? I know Zope starts and ends transactions with HTTP requests, but I'm not sure this implies the kind of read locking I need. thanks a lot! PS: please cc any answers.
Renaud Guerin wrote:
Hello,
I have a probably simple question, but got different opinions on the matter, and I'm seriously in need of a quick answer.
I need to make a ZSQL method that inserts data in a table this way:
1) first, generate a new ID with something like SELECT MAX(ID)+1 AS my_id FROM ... (using a sequence is not an option for me for various reasons)
2) then, INSERT with the ID previously generated ( is INSERT INTO ... VALUES (<dtml-var my_id>.... the right way to do this ?)
3) display my_id to the user for future reference.
First, I'm not sure how to do 3) : how do I pass my_id back to the original DTML document (which calls the ZSQL method with dtml-call) ?
Also (main question), is there something particular to do to ensure SELECT+INSERT is done in an atomic way that avoids concurrency problems ?
I know Zope starts and ends transactions with HTTP requests, but I'm not sure this implies the kind of read locking I need.
thanks a lot! PS: please cc any answers.
Well, the "best" way to do this is with a stored procedure, and using an Oracle sequence. e.g. CREATE SEQUENCE MY_SEQUENCE; CREATE OR REPLACE PROCEDURE MY_INSERT ( VAL1 IN INTEGER, VAL2 IN VARCHAR2, ID OUT INTEGER -- the important bit ) AS BEGIN SELECT NEXTVAL(MY_SEQUENCE) INTO ID FROM DUAL; -- I think INSERT INTO MY_TABLE (VAL1, VAL2, ID) VALUES (VAL1, VAL2, ID); END MY_INSERT; / Be warned that I did that off the top of my head without looking at the references -- I may have goofed the syntax. With something like that, you can use a DCOracle2 Stored Procedure to invoke it, the OUT parameter is the id that was created, and so will be returned from the stored procedure. -- Matt Kromer Zope Corporation http://www.zope.com/
This is a common operation. Most databases support a way to retrieve the last (or next) generated id for a table without resorting to using max(id)+1. I would refer to your database documentation on this. As for concurrency, just make sure you perform both operations in a single database transaction. Many database adapters automatically wrap each method call in a transaction. So by putting both sql statements in one ZSQL method (separated using <dtml-var sql_delimiter>) will do what you want. If the DA doesn't automatically do implicit transactions for you then just wrap the whole thing with "begin" and "end" SQL statements yourself. hth, -Casey Renaud Guerin wrote:
Hello,
I have a probably simple question, but got different opinions on the matter, and I'm seriously in need of a quick answer.
I need to make a ZSQL method that inserts data in a table this way:
1) first, generate a new ID with something like SELECT MAX(ID)+1 AS my_id FROM ... (using a sequence is not an option for me for various reasons)
2) then, INSERT with the ID previously generated ( is INSERT INTO ... VALUES (<dtml-var my_id>.... the right way to do this ?)
3) display my_id to the user for future reference.
First, I'm not sure how to do 3) : how do I pass my_id back to the original DTML document (which calls the ZSQL method with dtml-call) ?
Also (main question), is there something particular to do to ensure SELECT+INSERT is done in an atomic way that avoids concurrency problems ?
I know Zope starts and ends transactions with HTTP requests, but I'm not sure this implies the kind of read locking I need.
thanks a lot! PS: please cc any answers.
En réponse à Casey Duncan <casey@zope.com>:
This is a common operation. Most databases support a way to retrieve the last (or next) generated id for a table without resorting to using max(id)+1. I would refer to your database documentation on this.
The DB is Oracle8 with ZOracleDA. The trouble is, I need to generate this id in the first place, and as I said I'd rather not use a sequence. Thus the max(id)+1
As for concurrency, just make sure you perform both operations in a single database transaction. Many database adapters automatically wrap each method call in a transaction. So by putting both sql statements in one ZSQL method (separated using <dtml-var sql_delimiter>) will do what you want. If the DA doesn't automatically do implicit transactions for you then just wrap the whole thing with "begin" and "end" SQL statements yourself.
thanks. But what I want to make sure is that a transaction implies atomic operation, ie nobody else can perform another select max(id)+1 before the select of the first caller is performed. Is that OK with my current setup ?
Renaud Guérin wrote:
But what I want to make sure is that a transaction implies atomic operation, ie nobody else can perform another select max(id)+1 before the select of the first caller is performed. Is that OK with my current setup ?
No, its not OK -- two threads executing at the same time will generate an identical MAX(ID) + 1. Thats why you have to use an Oracle Sequence. -- Matt Kromer Zope Corporation http://www.zope.com/
En réponse à "Matthew T. Kromer" <matt@zope.com>:
No, its not OK -- two threads executing at the same time will generate an identical MAX(ID) + 1. Thats why you have to use an Oracle Sequence.
Ok thanks. But as I mentioned, using a sequence is not an option for me right now. Someone just pointed out to me that SELECT FOR UPDATE is just what I'm looking for. I understand it may not result in optimum performance, but this is a low traffic page and not an issue. Is it at least semantically correct ?
On Thu, 2002-03-21 at 08:09, Renaud Guérin wrote:
En réponse à "Matthew T. Kromer" <matt@zope.com>:
No, its not OK -- two threads executing at the same time will generate an identical MAX(ID) + 1. Thats why you have to use an Oracle Sequence.
Actually, I do believe that most (all?) databases have a low-level field, often called a rowid, which you can use. I seem to remember that you can get access to, and refer to, the rowid of any particular record in a table. That would solve the problem of not being able to create a sequence, and it would guarantee that you never got an ID collision. I don't remember the semantics of how to access it in Oracle, though. Sorry. -- Colin Fox cfox@crystalcherry.com CF Consulting Inc. GPG Fingerprint: D8F0 84E7 E7CC 5C6C 9982 F1A7 A3EB 6EA3 BC97 572F
Renaud Guérin wrote:
The DB is Oracle8 with ZOracleDA.
The trouble is, I need to generate this id in the first place, and as I said I'd rather not use a sequence. Thus the max(id)+1
I would not do this. It makes life more complicated. Why not use a sequence? Then you are guaranteed atomicy. I am no Oracle expert, but other database I have used let you retreive the next number in the sequence (before the row has been added) while simultaneously locking the table against other inserts for the duration of the transaction.
thanks. But what I want to make sure is that a transaction implies atomic operation, ie nobody else can perform another select max(id)+1 before the select of the first caller is performed. Is that OK with my current setup ?
No, if you must do this then you will need to explicitly lock the table against inserts *before* you look up the next id (you can use the lock SQL statement for this). Then if another thread does the same operation concurrently, one will block the other and they will be executed serially instead of in parallel. good luck! -Casey
The trouble is, I need to generate this id in the first place, and as I said I'd rather not use a sequence. Thus the max(id)+1 I would not do this. It makes life more complicated. Why not use a sequence? Then you are guaranteed atomicy. I am no Oracle expert, but other database I have used let you retreive the next number in the sequence (before the row has been added) while simultaneously locking the table against other inserts for the duration of the transaction.
I have very limited rights on the DB right now, and in particular I can't create sequences or other objects! Asking for this permission would probably smash my deadline :( Hence my looking for a reasonably safe alternative.
But what I want to make sure is that a transaction implies atomic operation, ie nobody else can perform another select max(id)+1 before the select of the first caller is performed. Is that OK with my current setup ? No, if you must do this then you will need to explicitly lock the table against inserts *before* you look up the next id (you can use the lock SQL statement for this). Then if another thread does the same operation concurrently, one will block the other and they will be executed serially instead of in parallel.
I think I need to lock the table against reads, not inserts. What I must ensure is that no more select's max(id)+1 can be done before the subsequent insert is completed. I don't know how to use LOCK to do that, anyone ? thanks -- Software is like sex : it's better when it's free -- Linus Torvalds http://renaudguerin.com --
Renaud Guérin wrote:
ly instead of in parallel.
I think I need to lock the table against reads, not inserts. What I must ensure is that no more select's max(id)+1 can be done before the subsequent insert is completed. I don't know how to use LOCK to do that, anyone ?
thanks
You want LOCK TABLE table IN EXCLUSIVE MODE; most likely. A second lock statement will block until the first one clears. -- Matt Kromer Zope Corporation http://www.zope.com/
I think I need to lock the table against reads, not inserts. What I must ensure is that no more select's max(id)+1 can be done before the subsequent insert is completed. I don't know how to use LOCK to do that, anyone ? You want LOCK TABLE table IN EXCLUSIVE MODE; most likely. A second lock statement will block until the first one clears.
damn, just read this: "A lock never prevents other users from querying the table. A query never places a lock on a table. Readers never block writers and writers never block readers. " (http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/st...) So I guess that leaves me for good with only the sequence option, unless there's something we didn't think of... thanks anyway!
Renaud Guérin wrote:
I think I need to lock the table against reads, not inserts. What I must ensure is that no more select's max(id)+1 can be done
before the
subsequent insert is completed. I don't know how to use LOCK to do that, anyone ?
You want LOCK TABLE table IN EXCLUSIVE MODE; most likely. A second lock statement will block until the first one clears.
damn, just read this: "A lock never prevents other users from querying the table. A query never places a lock on a table. Readers never block writers and writers never block readers. "
(http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/st...)
So I guess that leaves me for good with only the sequence option, unless there's something we didn't think of...
thanks anyway!
But if you always issue a LOCK TABLE before SELECT MAX from... your LOCKs will compete against themselves, so you'll be safe. -- Matt Kromer Zope Corporation http://www.zope.com/
Le Jeudi 21 Mars 2002 17:40, Matthew T. Kromer a écrit :
But if you always issue a LOCK TABLE before SELECT MAX from... your LOCKs will compete against themselves, so you'll be safe.
Exact, this may save my day ! :) One last problem though: How do I pass the id value from the SELECT to the INSERT, and then return that to the caller of the ZSQL method. I tried : SELECT max(id)+1 as new_id ... and then INSERT INTO table VALUES (<dtml-var new_id>,....) but it complains about new_id not declared as an input variable. If I do INSERT INTO table VALUES (SELECT ...,....) , will I have new_id as a returned result ? thanks
But if you always issue a LOCK TABLE before SELECT MAX from... your LOCKs will compete against themselves, so you'll be safe. Exact, this may save my day ! :) One last problem though: How do I pass the id value from the SELECT to the INSERT, and then return that to the caller of the ZSQL method. I tried : SELECT max(id)+1 as new_id ... and then INSERT INTO table VALUES (<dtml-var new_id>,....) but it complains about new_id not declared as an input variable. If I do INSERT INTO table VALUES (SELECT ...,....) , will I have new_id as a returned result ?
I ran out of time, so I've put the LOCK+SELECT in a first ZSQL method, the INSERT in another, and both are called sequentially from a DTML document. I hope the LOCK is still held between the 2 ZSQL methods. I've been told on IRC it is since the transaction is committed only at the end of the http request. anyway, thank you again for your invaluable help!
If it's a low volume page as you have said, the chances of having an id collision are small anyway. You could always use a random number instead of a sequentially numbered id, that would reduce the chances of a collision enormously. Cheers, Tom P [Renaud Guérin]
I think I need to lock the table against reads, not inserts. What I must ensure is that no more select's max(id)+1 can be done before the subsequent insert is completed. I don't know how to use LOCK to do that, anyone ? You want LOCK TABLE table IN EXCLUSIVE MODE; most likely. A second lock statement will block until the first one clears.
damn, just read this: "A lock never prevents other users from querying the table. A query never places a lock on a table. Readers never block writers and writers never block readers. " (http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/s tatements_915a.htm) So I guess that leaves me for good with only the sequence option, unless there's something we didn't think of...
participants (5)
-
Casey Duncan -
Colin Fox -
Matthew T. Kromer -
Renaud Guerin -
Thomas B. Passin