RE: multiple user question

Oh boy, we have a problem here, don't we? I don't want to sermonize here, but it seems to me that concurrent access consideration should be done while designing your db (if not before) and not after most of the code has been written, as they affect the schema as well as many coding choices. Also consider that adding the code later is a potential source of errors and data integrity holes. Having said that, there's not much that can be added, as there are so many variables involved that you don't give info about. Bear in mind that concurrent access & data integrity considerations could get us discussing forever, as the subject is *vast*. Summarizing, I think your best approach is to go thru the manuals (start with the guide to sql, tutorial), to see what concurrent access is all about and what tools are available, next for each group of related tables decide what is the data availability you require, and the minimal locking strategy. From there decide the isolation level you should use and whether to use db, table or record level locks, next surround your code with lock/unlock statements that might be required. Bear in mind that using an eccessive number of locks is a good way to bring your engine to its knees, so try to lock tables when you have to have to manipulate a large number of rows. Indiscriminate/eccessive lock usage is also a good way to create deadlocks. Also different combinations of locks/isolation levels give very different results, and that table locks behave differenly in online and SE, and that shared/exclusive table locks in online might not be what you think they are. As for some of your points 1) See above 2) I prefer row locks 3) It really depends, if you need to complete a process, then wait, if you don't want you user to wait forever, then don't wait 4) See above 5) why use a SP when you could use a serial field? 6) FIIK Prepare youself for a very hard time, Marco ____________________________________________________________________________ rem radioterapia, which I immeritately manage, seldom agrees with what I say marco greco (Catania, Italy) Work: marcog@ctonline.it rem radioterapia 39 95 447828 fax 446558 (was mar.greco@agora.stm.it) Achea 39 95 503117 --- On 28 Nov 1996 14:18:09 GMT ccliu <ccliu@csie.nctu.edu.tw> wrote: Hi, Currently, our project is using single thread connect to DB. And we are going to use 9 current connections . I know current users will cause some problem the single connection won't has. Can someong give me some advices when switch from single connection to multiple connections. What important things I should be aware of. Fro example, the Locking strategy, Application logical problem.... Table INSERT/UPDATE/DELETE problem...... We are using INFORMIX-OnLine Version 7.13.UC1 on HP-UX hag B.10.01 D 9000/856. All codes are in SPL on Server side.. The users are using VB on client side. My few notices: 1, I want user get share lock when SELECT. exclusive lock when UPDATE/INSER/DELETE. I know SELECT.....FOR UPDATE, but only for ESQL/C, the SPL can only use UPDATE CURSOR, and I don't know how to use it for UPDATE/INSERT/DELETE? 2, Set table to Row lock is better than Page lock? 3, Set Lock Mode to wait or not wait? 4, At the transaction level, use "set transaction isolation level" or use "set isolation level"? Dirty read, Repeatable read or committed read? 5, Some cases, For Example, if one stored procedure use "new = MAX(fieldname) + 1;" statement, if two users access this stored procedure at the same time, then one user of them will get the wrong new value. How can I avoid this kind of things? 6, How to avoid deadlock? Thanks. please reply to sss@bart.sdit.com.tw Scott sss@bart.sdit.com.tw -----------------End of Original Message-----------------