Learning Mysql
-> WHERE album.artist_id=artist.artist_id
Download 4.24 Mb. View original pdf
|
- Navigate this page:
- SELECT aid, @aid:=artist.artist_id, aid FROM artist,album -> WHERE album.artist_id=@aid;
- Transactions and Locking
- 266 | Chapter 7: Advanced Querying
| -> WHERE album.artist_id=artist.artist_id; +------+------------------------+------+ | aid | @aid:=artist.artist_id | aid | +------+------------------------+------+ | | 1 | 1 | | 1 | 1 | 1 | | 1 | 1 | 1 | | 1 | 1 | 1 | | 1 | 1 | 1 | | 1 | 1 | 1 | | 1 | 1 | 1 | | 1 | 2 | 2 | | 2 | 3 | 3 | | 3 | 3 | 3 | | 3 | 4 | 4 | | 4 | 5 | 5 | | 5 | 6 | 6 | +------+------------------------+------+ 13 rows inset sec) Now that aid isn’t empty, the initial query will produce some results: mysql> SELECT aid, @aid:=artist.artist_id, aid FROM artist,album -> WHERE album.artist_id=@aid; +------+------------------------+------+ | aid | @aid:=artist.artist_id | aid | +------+------------------------+------+ | 6 | 1 | 1 | | 1 | 1 | 1 | | 1 | 2 | 2 | +------+------------------------+------+ 3 rows inset sec) It’s best to avoid such circumstances where the behavior is not guaranteed and is hence unpredictable. Transactions and Locking When a database is concurrently accessed by several users, you have to consider how you maybe affected if other users change the data that you’re accessing, and how changes you make may affect other users. For example, you might get the wrong value for the total sales so far this year if new sales are being added to the database while you’re adding up the sales figures. 266 | Chapter 7: Advanced Querying Locks can be applied to prevent concurrent users from interacting destructively with one other’s data. A read lock allows you to prevent other users from changing data while you’re reading and processing the data, while a write lock tells other users that the data is being changed and that they should not read or modify it. For example, you need locks to avoid problems with reports when one user is trying to produce a report while another user changes the data the report is derived from. In some cases, you want all or none of a series of operations to succeed. For example, if you want to travel from Melbourne to Seattle via Los Angeles, you need to have a seat on the flight from Melbourne to Los Angeles, and a seat on the connecting flight from Los Angeles to Seattle. Having a confirmed seat on just one leg of the route is no use to you you can’t fly without confirmed seats on both legs. Transactions allow you to batch together SQL statements as an indivisible set that either succeeds or has no effect on the database. This means you can start a transaction and then issue a series of SQL statements. At the conclusion, you have the option of com- mitting (saving) the transaction to the database or rolling back (canceling) the transaction. By default, MySQL operates in AUTOCOMMIT mode, where each update is treated as anatomic transaction of its own, and changes are automatically committed. If this mode is disabled, or a transaction is explicitly started, changes aren’t commited to the database unless you execute a COMMIT or ROLLBACK instruction. Locking and transaction support is complex, and you need to make choices about the degree of isolation needed between users and the trade-offs involved in implementing them for your application. This is a difficult and advanced topic that’s mostly outside the scope of this book, but in the next section we discuss how the main table types supported by MySQL allow locking and transactions. We also include a simple transaction example in Transaction examples and we describe how simple locking can be used—and avoided—for our PHP wedding-registry application in Selecting and Deselecting Gifts in Chapter 15. Download 4.24 Mb. Share with your friends:
|