Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 5

Displaying posts with tag: Locks (reset)

Introduction to Transaction Locks in InnoDB Storage Engine
Employee_Team +5 Vote Up -0Vote Down

Introduction

Transaction locks are an important feature of any transactional storage engine. There are two types of transaction locks – table locks and row locks. Table locks are used to avoid a table being altered or dropped by one transaction when another transaction is using the table. It is also used to prohibit a transaction from accessing a table, when it is being altered. InnoDB supports multiple granularity locking (MGL). So to access rows in a table, intention locks must be taken on the tables.

Row locks are at finer granularity than table level locks, different threads can work on different parts of the table without interfering with each other. This is in contrast with MyISAM where the entire table has to be locked when updating even unrelated rows. Having

  [Read more...]
MariaDB/MySQL: GET_LOCK, RELEASE_LOCK, etc
+0 Vote Up -0Vote Down

InnoDB and some 3rd parties Storage Engines support transactions. But there are many places where concurrency can cause conflicts:

  • MEMORY, Aria and MyISAM only support table-level locks, which prevent all write statements (and maybe even reads) on a whole table. When concurrency is too high, this is a problem.
  • You may want to lock a VIEW, which is very different from locking a table: a VIEW can be a subset of a table, or a JOIN between table subsets.
  • DDL statements are not affected by locks. This is even true on tables which support transactions.

An alternative is using some SQL functions which acquire, check and release global named “locks”. The reason why I quoted “locks” is that they don’t lock anything. All sessions are

  [Read more...]
Avoid locks when storing counters in MySQL
+0 Vote Up -0Vote Down
A common problem with storing counters in a table is that every time your application update your counter, a row lock needs to be set on the row the table. If your application has a need for storing counters you can use this package which contains the scripts for a table and some stored procedures to handle managing the counters.
Instrumentation and the cost of Foreign Keys
+1 Vote Up -0Vote Down

I occasionally get in to light arguments healthy discussions with students about whether or not to use Foreign Key constraints on InnoDB tables.  My standard response has always been: “it depends on how much of a tradeoff you are willing to make for performance. In some situations the cost can be considerable”.

.. that’s when they expect me to “come up with some real proof” to show them. I do not disagree with their logic or proof being on their list-of-demands.  I support the use of data to make decisions.

The problem is that MySQL has (traditionally) been lacking the instrumentation required to make these decisions easy.  This is getting better  – here is an example we recently added to our

  [Read more...]
Variable's Day Out #7: innodb_autoinc_lock_mode
+0 Vote Up -0Vote Down

Properties:

Applicable To InnoDB Introduced In 5.1.22 Server Startup Option --innodb-autoinc-lock-mode=<value> Scope Global Dynamic No Possible Values enum(0,1,2)
Interpretation:
Value Meaning 0 Traditional 1 Consecutive 2 Interleaved Default Value 1 (consecutive) Categories Scalability, Performance

  [Read more...]
Showing entries 1 to 5

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.