Oracle SQL Jumpstart with Examples- P8: Review "As a consultant with more than 12 years of experience working with Oracle databases on a daily basis, reviewing this book was a unique and enjoyable experience. The SQL language is without doubt one of the most critical database skills and it is best learned by example. This book addresses that crucial need. Mr. Powell does an excellent job of clarifying the concepts by using meaningful and easy to understand examples. Frankly, I have not come across any other book on SQL that is as good a compilation of SQL concepts in a single. | 320 Transaction Control Figure A Transaction Is Set. your DML command to fail rather than wait if another user is updating the same row you try to update. Another common use is assigning large transactions to very large rollback segments. Figure shows the syntax of the SET TRANSACTION command Let s query an ARTIST row and start a read-only transaction using the following commands. SQL Plus Worksheet displays Transaction set. in the lower pane. The result is shown in Figure . SELECT ARTIST_ID NAME ZIP FROM ARTIST WHERE NAME Puddle of Mudd SET TRANSACTION READ ONLY Now let s try to change the zip code using the following script. UPDATE ARTIST SET ZIP 10099 WHERE NAME Puddle of Mudd Figure shows an error message. No changes can be made to the database inside a read-only transaction. In addition a read-only transaction does not see changes to the database made by other users after the transaction starts. This might be useful when you are generating a set of reports that summarize data and must be consistent from beginning to end. For Please purchase PDF Split-Merge on to remove this watermark. Transaction Control 321 Figure Read-Only Transactions Prevent Database Changes. example you run a summary of sales from the beginning of the year up to today and then in the same transaction run a detail report of sales activity. If users are updating the SALES table between your first and second reports the two reports will not match. Use a read-only transaction to preserve the state of the database when you begin the first report. Note Setting read-only transactions can cause serious concurrency issues for other users. Applications will not be able to respond properly when other users preserve data for exclusive use. This type of activity is inadvisable because it could upset end users your clients . The default transaction setting is READ WRITE which allows changes and sees other users changes immediately after being committed.
