|
For the record, transactions are a mixed bag.
All interactions with the data store take place in a transaction; it can either be an implicit one, or an explicit one. If you cancel a delete or update query before it finishes executing, the work that's already been done is rolled back. The difference isn't whether transactions are being used, it's whether they span multiple commands.
Sometimes that's a good thing. If you have a series of five inserts, updates, and deletes, it can be more efficient for them to all happen in the scope of a single transaction than in five individual ones, but that depends on the db engine you're using, and the way it's used.
On the other hand, rolling back is more expensive than most operations. There's extra begin transaction and commit or rollback code that needs to happen when you create explicit transactions. You increase the risk of a deadlock, or just slower performance due to contention - several different transactions all lining up to take a lock on a particular row or even just a table. You can do a lot to mitigate the deadlock problem, but if you use an isolation level that demands locks, those are a bad thing to hold onto longer than necessary. Locks can escalate on their own when you use too many of them or the server comes under memory pressure. Basically, like Mike is suggesting, if you can write your application in a way that doesn't need transactions, you're probably better off.
Now that's all for the record ... for NW Source, if you don't have a specific reason in mind why you need to use explicit transactions, then you don't need them. If you find your app doing a lot of inserts, updates, and deletes, you might want to reconsider your options. But MySQL lets you assign a storage engine to individual tables, so you can change your mind later with a minimum of effort.
|
