Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 6

Displaying posts with tag: Locks (reset)

When your query is blocked, but there is no blocking query - Part 3
+0 Vote Up -0Vote Down
In the previous blog posts I've talked about transactions which block other transactions but don't do anything and about some possible solutions.

In this post I will show you how to get even more information about what is locked by a transaction.

As you might have noticed the information_schema.innodb_locks table doesn't show all locks. This is what the documentation says:
"The INNODB_LOCKS table contains information about each lock that an InnoDB transaction has requested but not yet acquired, and each lock that a transaction




  [Read more...]
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 6

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.