Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Innodb data base and Row Level Locking

    • 0
    • 1
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 181
    Comment on it

    MySQL provides a storage engine called InnoDB. It is a default storage engine for MySQL 5.5 and later. InnoDB supports ACID-compliant transaction features, along with foreign key support. InnoDB is a general-purpose storage engine that is used for high performance/reliability.

    Some important advantages of tables created in InnoDB are as follows:

    1. InnoDB's DML operations supports the ACID model, with transactions such as rollback, commit, and crash-recovery for the protection of user data.

    2. Row-level locking is used to increase multi-user concurrency and performance.

    3. To optimize the queries based on primary keys, InnoDB table arrange the data on disk.

    4. InnoDB supports FOREIGN KEY constraints, to maintain data integrity. Updates, deletes, and Inserts all are checked to make sure that the results are consistent across different tables.

    5. The InnoDB tables can be mixed with tables in MySQL storage engines, in the same query. For example: In a join operation we can combine data from InnoDB and MEMORY tables in a single query.

    6. While designing InnoDB the main point that were of big concern was efficiency of CPU and performance of maximum level while processing big database.

    7. Caching data and indexing in main memory is done in InnoDB via buffer pool.

    For more information see the below link:
    http://dev.mysql.com/doc/refman/5.6/en/innodb-introduction.html


    Row level locking:

    It is a technique used in MySql, where a row is locked for writing from preventing other users by accessing data while it is being updated.

    InnoDB implements standard row-level locking which are of two types ,

    1. -shared (S) locks
    2. -exclusive (X) locks.

    A shared (S) lock allows the process that is holding the lock to read a row.

    An exclusive (X) lock allows the process that is holding the lock to update or delete a row.

    Say a process P1 holds a shared (S) lock on row r, then requests from some distinct process say P2 for a lock on row r are handled as follows:

    A request by P2 for an S lock can be granted immediately. As a result, both P1 and P2 hold an S lock on r.
    
    A request by P2 for an X lock cannot be granted immediately. 
    

    If a process P1 holds an exclusive (X) lock on row r, a request from some distinct process P2 for a lock of either type on r cannot be granted immediately. Rather, process P2 has to wait for process P1 to release its lock on row r.

    A lock is assigned to a requesting process if it shows compatibility with existing locks, but not if it conflicts with the lock one that is already there. A process waits until the conflicted existing lock is released. If a lock request conflicts with an existing lock and cannot be assigned because it would cause deadlock error.

    For more information on row lock see the link below:

    http://dev.mysql.com/doc/refman/5.6/en/innodb-lock-modes.html

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: