Showing posts with label transactions. Show all posts
Showing posts with label transactions. Show all posts

Thursday, August 3, 2017

How to Make a DBA Salty

If you want to upset you DBA, and possibly get privileges revoked or at best not be able to ask for any favors, then here's how you can go about that.

Open a SQL editor (SSMS, Toad, Dbeaver, whatever you use to connect to and execute SQL). Connect to the highest level database you have access to. Make sure any kind of autocommit settings are off if this applies.

Now you want to make sure your not going to do anything really damaging like drop tables or something crazy like that - probably you won't have access to do that anyway if the security policies are set up correctly. But you should be able to update some data.

Now open a transaction:


or whatever your specific DB uses to start a transaction.

Now update some tables from within the transaction. It's best to do a whole range of updates and a few inserts just to ensure that table level locks are taken. Do this on a few tables just to be sure.

From within that same transaction, select from all those tables you've altered to verify that the data has changed. And that's it...don't commit the transaction, just leave it open. Before you know it, warnings will go off, alarms will sound, alerts will get sent. Anything using the DB will grind to a halt.

Eventually, a DBA will find that you've caused this and will lose trust and faith in your abilities. Lock the database like this a few times and you'll be put in the corner. Would you lose your job over it? Perhaps....

If you've left a transaction open like this and closed the window with the query, the connection and the transaction might even be left open. Then its up to the DBA to kill the connection and rollback the transaction.

BTW, you should probably ROLLBACK the transaction eventually, perhaps the DBA will ask you to close the trans...that means to roll back the changes you've made so that they go away without actually updating the DB or to COMMIT them which would apply them to the database. In this case, you were just messing with the data so you probably don't really want to commit the changes - they'd be saved permanently!

Tuesday, September 6, 2016

Coordinator Pattern for Distributed Enterprise Applications

When building software for the enterprise, particularly where data access to one or more source will be shared across multiple application domains, and where distribution and reuse through distribution is a virtue, it would be helpful to use a Coordinator as the authority for the unit of work. A Coordinator is a service which accepts a single incoming command and issues one or many commands to resources (data access services). The resources are separated into read and write resources for each source. The read resources may be used by the presentation layer or the Coordination Layer, but the write layer (which specializes in executing commands) may only be used by the Coordination Layer.
A Coordinator is similar to a Unit of Work pattern. The key difference is that it must be hosted as a service and use data access as services.
The Coordinator owns the transaction. It must begin and either rollback or commit the transaction. The transaction may be distributed across many data sources (throughout the writer resources). The writer resources must be designed in a way that handles concurrency. For example, if using an ORM you may need to load then update or insert any specific data entities involved in the transaction. These writer resources should handle the entire transaction for the data source which it wraps (typically one database). The Coordinator should send commands to any resources and raise any events necessary upon executing the commands.

The Coordinator should listen for events (as in event based architecture) and respond to those events by issuing those specific commands to the resources which it consumes. Those resources may be shared amongst other Coordinators. Resource commands should be synchronous so that transactions can be used appropriately. The Coordinator is an architectural pattern which can be used to implement a Unit of Work pattern in a distributed, service-based architecture. Coordinators should be consumed asynchronously using event-driven mechanisms. Coordinators should respond to events raised by other Coordinators or by users via the presentation layer.

Through the use of Coordinators in conjunction with the other patterns expressed in this article, a distributed system can be built such that it is loosely-coupled, highly-cohesive and easily-maintainable. The system will be loosely-coupled in that the communication between Coordinators occurs via events. Zero, one, or many Coordinators can respond to an event. Any Coordinator can be changed and (if hosted independently) redeployed without impacting any other Coordinator. The level of cohesiveness depends on the scope built into each Coordinator. Each Coordinator could handle one Unit Of Work. In this case each one would have a single service method exposed. This method may perhaps share a contract and differ only by location. In this case the method definition would need to describe an event and the Coordinator would need to load the Event details it needs via resources. In this case, an Event Store would be needed. Else, the payload would be a serialized packet of all of the data relevant to the event. This is a deeper topic and will require more in-depth exploration in order to weigh in completely on the interface mechanisms involved. The maintainability is affected by the number of moving pieces, but also by having a standard mechanism of control over the various state changes involved in a complex system.