Difference between Passimistic and Optimistic locking

Optimistic Locking is a strategy where you read a record, take note of a version number and check that the version hasn't changed before you write the record back.

When you write the record back you filter the update on the version to make sure it's atomic. (i.e. hasn't been updated between when you check the version and write the record to the disk) and update the version in one hit.
If the record is dirty (i.e. different version to yours) you abort the transaction and the user can re-start it.

This strategy is most applicable to high-volume systems and three-tier architectures where you do not necessarily maintain a connection to the database for your session. In this situation the client cannot actually maintain database locks as the connections are taken from a pool and you may not be using the same connection from one access to the next.


Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks.

To use pessimistic locking you need either a direct connection to the database (as would typically be the case in a two tier client server application) or an externally available transaction ID that can be used independently of the connection.

In the latter case you open the transaction with the TxID and then reconnect using that ID. The DBMS maintains the locks and allows you to pick the session back up through the TxID.

Creating an SSIS Data Source Connection to MySQL

Its a big problem for SSIS beginners, working with MySQL database, just think that you want to connect with MySQL db and you dont get its provider anywhere in the list of providers. (even i trapped in this situation )


Here's how to create an SSIS data source connection to MySQL:

1. Download and install the MySQL Connector ODBC 6.0.
2. Create a new DSN through Windows Data Sources (ODBC)
3. Create a new SSIS ADO.NET connection that uses the .NET Providers\Odbc Data
Provider based on the DSN name created in step-2



4. In your Data Flow, use a DataReader Source that uses the ADO.NET connection
created in step-3.





5. Add a select statement to the SqlCommand property of the DataReader source that
specifies the columns to extract from the table you need to read data from.




if this was helpful then please donate as much you want




Cheers

How to declare a variable in MySQL Stored Procedure

Hi Friends,

I was very disappointed when i was stuck for almost half an hour just to declare a variable inside the stored procedure in MySQL db.

I searched so many blogs stating many solutions to the problem, but no one suggested the basic solution.

Ultimately i managed to find it by myself, so thought i must share it with biginners of MySQL.

The trick is that the DECLARE statement must follow the BEGIN statement i.e. variables must be declared immediately after BEGIN statement of the stored procedure.




Thats all..
Cheers