Hei All, I am new here (my name is Gábor Lénárt and I am from Hungary), and I am also quite new to develop more complex applications using RDBMS, MySQL in our case. I hope it's the right place to ask general questions too. I have experience to create simple applications without transaction handling or so, and also with administrating MySQL server at least.
Now I have to develop a SOAP interface which is about querying data from MySQL database, and also to modify. The interface may be used by multiple clients in parallel, and it's also must be a secure solution to do that, so of course using transactions are must for here. However I am not so well experienced in this area. What can I do to be sure, that starting transaction guarantees that I see a consistent snapshot of my database (even if other requests modify it meanwhile) with mixed SELECT/UPDATE/INSERT with also be able to read back my changes done within my transaction. MySQL documentation is a bit mystical for me, since it refers for Oracle, which I have no experience at all. I guessed this will work: SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET AUTOCOMMIT = 0; START TRANSACTION WITH CONSISTENT SNAPSHOT; [... my queries with lots of SELECT/INSERT/UPDATE statements ...] COMMIT; (or ROLLBACK if there was some error during the processing) Is it a good solution for me then? I am not so sure about isolation levels and so. The other problem with this is error handling,. What kind of error codes can I expect to signal there is some kind of conflict between parallel transactions? Since the SQL error handler must be prepared to handle other kind of SQL problems (which shouldn't occur, but who can be sure there is no bug in the application!), and the error codes caused by the conflicting transactions, dead locks, and so on. Or is it enough to check the error code after issuing COMMIT, so all errors there are about only these, and I can be sure that errors at other SQL statements are caused by bugs in my application? The second problem of mine maybe even more complex (well, for me at least). If there is a SOAP request to modify the database, basically it describes multiple modifications in the database through multiple steps. But I also have constraints and rules to check, but it cannot be done in SQL level. It's because these checks are quite complex ones, not possible to explain as simple rules like 'UNIQUE' fields and so on, and also, the constraints and other relation rules of my interface can be broken during the transaction steps, just they must be checked at the end, before the COMMIT. I've already implemented this, however I have a worrisome thought. Since my transaction sees a consistent snapshot of the DB which was the state at the time of issuing "START TRANSACTION ...". I will not see if other threads of my interface got another SOAP requests resulting breaking some constraints I want to check within the transaction. Ok maybe my English knowledge is a bit terrible to explain myself right, also maybe my problems are quiter beginner types, but I would be grateful if someone can help me with the answers. Thanks a lot in advance, -- Gábor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
