Re: multiple user question

ccliu 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. As indicated by Nils Myklebust's response, you misunderstand the meaning of "multi-thread". > 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? Using COMMITTED READ solve all the issues here. > 4, At the transaction level, use "set transaction isolation level" or > use "set isolation level"? > Dirty read, Repeatable read or committed read? I prefer using "set transaction" because it will return you an error if you forget to close (rollback or commit) an open transaction. "set isolation" has the advantage to switch among different isolation levels within a single transaction. But, be careful here. None of Informix's manuals mentioned the "flexibility" of set isolation. I believe somewhere in the manual, it says (or implies) the result of switching isolation level is unpredicable. Furthermore, if you find yourself switching isolation levels within a single transaction, you probably misunderstand the definition of transaction. As to when to use which isolation level, it is too much to ask on the news group. There is a good book you may want to check out on this topic: Jim Gray and Andreas Reuter, "Transaction Processing: Concepts and Technologies," Morgan Kaufmann Publishers, Inc., 1993. > > 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? It really surprised me to see you question. The question should have been taught in your school. > > 6, How to avoid deadlock? > Check out Informix's Administrator's manuals and you will see Informix has handled most of the problems (if not all) for you already. Chao Din