#Error_handling

[technicaltester]

Specielt fejlsektionen af den linkede artiker en en super god måde håndtere fejlsituationer i HTTP baserede API baseret på en fejltype (application/problem-json) hvori man både kan komme med en egentlig fejlbesked til brugeren samt give gode meta informationer om den fejl der er opstået og hvad der skal til på klientsiden for at fikse fejlen inden næste forsøg.

Jeg er fan!

[technoscripts1]

Exploring STM32 UART Programming at TechnoScripts Institute 

Sure, here are the steps to write a simple UART program for STM32 microcontrollers:

Step 1: Set up your development environment

Install STM32CubeIDE or any other suitable IDE for STM32 development.

Create a new project and configure it for your specific STM32 microcontroller model.

Step 2: Configure UART peripheral

In your project, configure the UART peripheral for communication. Determine the UART port (e.g., USART1, USART2) and the pins used for communication (TX and RX).

Set the baud rate, data format (e.g., number of data bits, parity, stop bits), and other parameters according to your application requirements.

Step 3: Initialize UART

Initialize the UART peripheral in your code. Enable the clock for the UART peripheral, configure the GPIO pins for UART communication, and set up the UART registers with the desired settings.

Step 4: Implement UART transmission

Write code to transmit data over UART. Use functions provided by the HAL (Hardware Abstraction Layer) or low-level register access to send data.

You can transmit single characters, strings, or formatted data depending on your application needs.

Step 5: Implement UART reception

Write code to receive data over UART. Set up interrupt or polling-based methods to detect incoming data and read it from the UART receive buffer.

Process the received data as needed for your application.

Step 6: Test the UART communication

Compile your code and flash it onto your STM32 microcontroller.

Use a serial terminal program (e.g., PuTTY, Tera Term) on your computer to communicate with the STM32 microcontroller via UART.

Verify that data can be transmitted and received correctly between the microcontroller and the computer.

Step 7: Add application logic

Integrate UART communication into your application logic. Use UART to exchange data with other devices or peripherals connected to the STM32 microcontroller.

Implement error handling and robustness features as needed to ensure reliable communication.

Step 8: Test and debug

Test your UART program under various conditions to ensure its reliability and stability.

Use debugging tools and techniques to troubleshoot any issues or unexpected behavior in your UART code.

Step 9: Optimize and refine

Optimize your UART code for efficiency and performance, considering factors such as interrupt handling, buffer management, and data transmission speed.

Refine your UART implementation based on feedback and testing results to improve its functionality and usability.

Here's a simple program for STM32 UART programming using the STM32Cube HAL (Hardware Abstraction Layer) library:

#include "stm32f4xx_hal.h"

#include <string.h>

UART_HandleTypeDef huart2;

void SystemClock_Config(void);

static void MX_GPIO_Init(void);

static void MX_USART2_UART_Init(void);

int main(void) {

  HAL_Init();

  SystemClock_Config();

  MX_GPIO_Init();

  MX_USART2_UART_Init();

  char *message = "Hello, UART!\r\n";

  while (1) {

    HAL_UART_Transmit(&huart2, (uint8_t*)message, strlen(message), HAL_MAX_DELAY);

    HAL_Delay(1000);

  }

}

void SystemClock_Config(void) {

  RCC_OscInitTypeDef RCC_OscInitStruct = {0};

  RCC_ClkInitTypeDef RCC_ClkInitStruct = {0};

  __HAL_RCC_PWR_CLK_ENABLE();

  __HAL_PWR_VOLTAGESCALING_CONFIG(PWR_REGULATOR_VOLTAGE_SCALE1);

  RCC_OscInitStruct.OscillatorType = RCC_OSCILLATORTYPE_HSE;

  RCC_OscInitStruct.HSEState = RCC_HSE_ON;

  RCC_OscInitStruct.PLL.PLLState = RCC_PLL_ON;

  RCC_OscInitStruct.PLL.PLLSource = RCC_PLLSOURCE_HSE;

  RCC_OscInitStruct.PLL.PLLM = 8;

  RCC_OscInitStruct.PLL.PLLN = 336;

  RCC_OscInitStruct.PLL.PLLP = RCC_PLLP_DIV2;

  RCC_OscInitStruct.PLL.PLLQ = 7;

  if (HAL_RCC_OscConfig(&RCC_OscInitStruct) != HAL_OK) {

    Error_Handler();

  }

  RCC_ClkInitStruct.ClockType = RCC_CLOCKTYPE_HCLK | RCC_CLOCKTYPE_SYSCLK |

                                RCC_CLOCKTYPE_PCLK1 | RCC_CLOCKTYPE_PCLK2;

  RCC_ClkInitStruct.SYSCLKSource = RCC_SYSCLKSOURCE_PLLCLK;

  RCC_ClkInitStruct.AHBCLKDivider = RCC_SYSCLK_DIV1;

  RCC_ClkInitStruct.APB1 CLOCK Divider = RCC_HCLK_DIV4;

  RCC_ClkInitStruct.APB2 CLOCK Divider = RCC_HCLK_DIV2;

  if (HAL_RCC_ClockConfig(&RCC_ClkInitStruct, FLASH_LATENCY_5) != HAL_OK) {

    Error_Handler();

  }

  HAL_SYSTICK_Config(HAL_RCC_GetHCLKFreq() / 1000);

  HAL_SYSTICK_CLKSourceConfig(SYSTICK_CLKSOURCE_HCLK);

  HAL_NVIC_SetPriority(SysTick_IRQn, 0, 0);

}

static void MX_USART2_UART_Init(void) {

  huart2.Instance = USART2;

  huart2.Init.BaudRate = 115200;

  huart2.Init.WordLength = UART_WORDLENGTH_8B;

  huart2.Init.StopBits = UART_STOPBITS_1;

  huart2.Init.Parity = UART_PARITY_NONE;

  huart2.Init.Mode = UART_MODE_TX_RX;

  huart2.Init.HwFlowCtl = UART_HWCONTROL_NONE;

  huart2.Init.OverSampling = UART_OVERSAMPLING_16;

  huart2.Init.OneBitSampling = UART_ONE_BIT_SAMPLE_DISABLE;

  huart2.AdvancedInit.AdvFeatureInit = UART_ADVFEATURE_NO_INIT;

  if (HAL_UART_Init(&huart2) != HAL_OK) {

    Error_Handler();

  }

}

static void MX_GPIO_Init(void) {

  GPIO_InitTypeDef GPIO_InitStruct = {0};

  __HAL_RCC_GPIOA_CLK_ENABLE();

  __HAL_RCC_GPIOB_CLK_ENABLE();

  HAL_GPIO_WritePin(GPIOA, GPIO_PIN_5, GPIO_PIN_RESET);

  GPIO_InitStruct.Pin = GPIO_PIN_5;

  GPIO_InitStruct.Mode = GPIO_MODE_OUTPUT_PP;

  GPIO_InitStruct.Pull = GPIO_NOPULL;

  GPIO_InitStruct.Speed = GPIO_SPEED_FREQ_LOW;

  HAL_GPIO_Init(GPIOA, &GPIO_InitStruct);

}

Explanation:

Includes the necessary header files for the STM32F4xx HAL library and string manipulation.

Initializes the UART peripheral handle huart2.

Defines the main() function where the program execution begins.

Calls HAL_Init() to initialize the HAL library.

Calls SystemClock_Config() to configure the system clock.

Calls MX_GPIO_Init() and MX_USART2_UART_Init() to initialize GPIO and UART peripherals, respectively.

Defines a message to be transmitted over UART.

Enters an infinite loop where the message is transmitted via UART every second using HAL_UART_Transmit().

Defines SystemClock_Config(), MX_USART2_UART_Init(), and MX_GPIO_Init() functions to configure the system clock, UART, and GPIO peripherals, respectively.

Initializes GPIOA pin 5 for LED control in MX_GPIO_Init() function.

[devsnews]

HandleException is the solution to cumbersome try-catch blocks in Python. So say goodbye to messy error-handling code and hello to clean and efficient exception handling with HandleException. Read this article to learn more about this approach.

[mehrdadsalahi]

‌ در پست قبلی، از انواع خطاهایی که در حین برنامه‌نویسی ممکنه رخ بده صحبت کردم. در این پست، در مورد مدیریتوخطا در VBA صحبت می‌کنم. لطفاً پیج ما رو به دوستان‌تون معرفی کنید و یا اونها رو زیر این پست تگ کنید. @_computer_skill_ #ماکرو #کامپیوتر #برنامه_نویسی #آموزش #آموزش_برنامه_نویسی #خطا #مدیریت_خطا #نرم_افزار #ویژوال_بیسیک #computer #computerscience #vba #visual_basic #visual_basic_application #error #error_handling #computer_skill_vba #computer_skill_vba_episod_21 (at Tehran, Iran) https://www.instagram.com/p/CFyoQDyna3G/?igshid=e449dv9pjk7x

[commentsense888]

Error handling by VBA_Scrub https://www.reddit.com/r/ProgrammerHumor/comments/9v2lut/error_handling/?utm_source=ifttt

[shizzymcfizzy]

rray(b),d=0;d<b;d++)c[d]=arguments[d];if(c.length<2)throw new Error("Default value is required");var e=c[0],f=c[1];return g(a,e,f)}}}),null);

__d("sdk.feature",["JSSDKConfig","sdk.FeatureFunctor"],(function(a,b,c,d,e,f){a=b("sdk.FeatureFunctor").create(b("JSSDKConfig"));e.exports=a}),null);

__d("sdk.Scribe",["QueryString","UrlMap","sdk.Runtime","sdk.feature"],(function(a,b,c,d,e,f){f.log=a;function a(a,c,d){d===void 0&&(d=!1);if(c.extra!=null&&typeof c.extra==="object"){var e=c.extra;e.revision=b("sdk.Runtime").getRevision()}e=new Image();var f=b("UrlMap").resolve("www")+"/common/scribe_endpoint.php";b("sdk.feature")("epd_endpoint_migration",!1)&&(f=b("UrlMap").resolve("www")+"/platform/scribe_endpoint.php/",b("sdk.feature")("epd_omit_cookies",!1)&&!d&&(e.crossOrigin="anonymous"));e.src=b("QueryString").appendToUrl(f,{c:a,m:ES("JSON","stringify",!1,c)})}}),null);

__d("sdk.ErrorHandling",["sdk.ErrorHandler","sdk.Runtime","sdk.Scribe","sdk.feature"],(function(a,b,c,d,e,f){a=b("sdk.feature")("error_handling",!1);c=b("sdk.ErrorHandler").create(a,function(a){b("sdk.Scribe").log("jssdk_error",{appId:b("sdk.Runtime").getClientID(),error:a.name||a.message,extra:a})});e.exports=c}),null);

__d("FB",["DOMWrapper","GlobalCallback","JSSDKCssConfig","Log","dotAccess","sdk.Content","sdk.DOM","sdk.ErrorHandling","sdk.domReady"],(function(a,b,c,d,e,f){var g=window.FB={};c={};b("Log").setLevel(0);b("GlobalCallback").setPrefix("FB.__globalCallbacks");var h=document.createElement("div");b("DOMWrapper").setRoot(h);b("sdk.domReady")(function(){b("Log").info("domReady"),b("sdk.Content").appendHidden(h),b("JSSDKCssConfig").rules&&b("sdk.DOM").addCssRules(b("JSSDKCssConfig").rules,b("JSSDKCssConfig").components)});function i(a,c,d,e){return b("sdk.ErrorHandling").guard(function(){function f(a){if(Array.isArray(a))return a.map(f);return a&&typeof a==="object"&&a.__wrapped?a.__wrapped:typeof a==="function"&&/^function/.test(a.toString())?b("sdk.ErrorHandling").unguard(a):a}var g=Array.prototype.slice.call(arguments).map(f),h=a.apply(e,g),j,k=!0;if(h&&typeof h==="object"){j=Object.create(h);j.__wrapped=h;for(var d in h){var l=h[d];if(typeof l!=="function"||d==="constructor")continue;k=!1;j[d]=i(l,c+":"+d,d,h)}}return!k?j:k?h:j},c)}function a(a,c){var d=a?b("dotAccess")(g,a,!0):g;Object.keys(c).forEach(function(b){var e=c[b];if(typeof e==="function"){var f=(a?a+".":"")+b;f=i(e,f,b,c);f&&(d[b]=f)}else(typeof e==="object"||typeof e==="number")&&(d[b]=e)})}ES("Object","assign",!1,c,{provide:a});e.exports=c}),null);

__d("ArgumentError",["ManagedError"],(function(a,b,c,d,e,f){a=function(a){babelHelpers.inheritsLoose(b,a);function b(b,c){return a.call(this,b,c)||this}return b}(b("ManagedError"));e.exports=a}),null);

__d("flattenObject",[],(function(a,b,c,d,e,f){"use strict";e.exports=a;function a(a){var b={};for(var c in a)if(Object.prototype.hasOwnProperty.call(a,c)){var d=a[c];if(null===d||void 0===d)continue;else typeof d==="string"?b[c]=d:b[c]=ES("JSON","stringify",!1,d)}return b}}),null);

__d("URIRFC3986",[],(function(a,b,c,d,e,f){f.parse=a;var g=new RegExp("^([^:/?#]+:)?(//([^\\\\/?#@]*@)?(\\[[A-Fa-f0-9:.]+\\]|[^\\/?#:]*)(:[0-9]*)?)?([^?#]*)(\\?[^#]*)?(#.*)?");function a(a){if(a.trim()==="")return null;a=a.match(g);if(a==null)return null;var b={};b.uri=a[0]?a[0]:null;b.scheme=a[1]?a[1].substr(0,a[1].length-1):null;b.authority=a[2]?a[2].substr(2):null;b.userinfo=a[3]?a[3].substr(0,a[3].length-1):null;b.host=a[2]?a[4]:null;b.port=a[5]?a[5].substr(1)?parseInt(a[5].substr(1),10):null:null;b.path=a[6]?a[6]:null;b.query=a[7]?a[7].substr(1):null;b.fragment=a[8]?a[8].substr(1):null;b.isGenericURI=b.authority===null&&!!b.scheme;return b}}),null);

__d("createObjectFrom",[],(function(a,b,c,d,e,f){e.exports=g;function g(a,b){if(b===void 0)return g(a,!0);var c={};if(Array.isArray(b))for(var d=a.length-1;d>=0;d--)c[a[d]]=b[d];else for(var d=a.length-1;d>=0;d--)c[a[d]]=b;return c}}),null);

__d("URISchemes",["createObjectFrom"],(function(a,b,c,d,e,f){f.isAllowed=a;var g=b("createObjectFrom")(["aidemos","aistudio","blob","cmms","fb","fba","fbatwork","fb-ama","fb-workchat","fb-workchat-secure","fb-messenger","fb-messenger-public","fb-messenger-group-thread","fb-page-messages","fb-pma","fbcf","fbconnect","fbinternal","fbmobilehome","fbrpc","file","ftp","gtalk","http","https","mailto","wss","ms-app","intent","itms","itms-apps","lasso","market","svn+ssh","fbstaging","tel","sms","pebblejs","sftp","whatsapp","moments","flash","fblite","chrome-extension","webcal","fb124024574287414","fb124024574287414rc","fb124024574287414master","fb1576585912599779","fb929757330408142","designpack","fbpixelcloud","fbapi20130214","fb1196383223757595","oculus","oculus.store","oculus.feed","oculusstore","odh","skype","callto","workchat","fb236786383180508","fb1775440806014337","data","fb-mk","munki","kirigami","origami-file","fb-nimble-vrsrecorder","fb-nimble-monohandtrackingvis","together","togetherbl","venues","whatsapp-consumer","whatsapp-smb","fb-ide-opener","spark-studio"]);function a(a){return a==null||a===""?!0:Object.prototype.hasOwnProperty.call(g,a.toLowerCase())}}),null);

__d("setHostSubdomain",[],(function(a,b,c,d,e,f){e.exports=a;function a(a,b){a=a.split(".");a.length<3?a.unshift(b):a[0]=b;return a.join(".")}}),null);

__d("URIAbstractBase",["invariant","URIRFC3986","URISchemes","setHostSubdomain"],(function(a,b,c,d,e,f,g){var h,i,j=new RegExp("[\\x00-\\x2c\\x2f\\x3b-\\x40\\x5c\\x5e\\x60\\x7b-\\x7f\\uFDD0-\\uFDEF\\uFFF0-\\uFFFF\\u2047\\u2048\\uFE56\\uFE5F\\uFF03\\uFF0F\\uFF1F]"),k=new RegExp("^(?:[^/]*:|[\\x00-\\x1f]*/[\\x00-\\x1f]*/)"),l=[];a=function(){"use strict";a.parse=function(c,d,e,f){if(!d)return!0;if(d instanceof a){c.setProtocol(d.getProtocol());c.setDomain(d.getDomain());c.setPort(d.getPort());c.setPath(d.getPath());c.setQueryData(f.deserialize(f.serialize(d.getQueryData())));c.setFragment(d.getFragment());c.setIsGeneric(d.getIsGeneric());c.setForceFragmentSeparator(d.getForceFragmentSeparator());c.setOriginalRawQuery(d.getOriginalRawQuery());c.setQueryParamModified(!1);return!0}d=d.toString().trim();var g=(h||(h=b("URIRFC3986"))).parse(d)||{fragment:null,scheme:null,query:null};if(!e&&!(i||(i=b("URISchemes"))).isAllowed(g.scheme))return!1;c.setProtocol(g.scheme||"");if(!e&&j.test(g.host||""))return!1;c.setDomain(g.host||"");c.setPort(g.port||"");c.setPath(g.path||"");if(e)c.setQueryData(f.deserialize(g.query||"")||{});else try{c.setQueryData(f.deserialize(g.query||"")||{})}catch(a){return!1}c.setFragment(g.fragment||"");g.fragment===""&&c.setForceFragmentSeparator(!0);c.setIsGeneric(g.isGenericURI||!1);c.setOriginalRawQuery(g.query);c.setQueryParamModified(!1);if(g.userinfo!==null){if(e)throw new Error("URI.parse: invalid URI (userinfo is not allowed in a URI): "+d);return!1}if(!c.getDomain()&&c.getPath().indexOf("\\")!==-1){if(e)throw new Error("URI.parse: invalid URI (no domain but multiple back-slashes): "+d);return!1}if(!c.getProtocol()&&k.test(d)){if(e)throw new Error("URI.parse: invalid URI (unsafe protocol-relative URLs): "+d+"'");return!1}if(c.getDomain()&&c.getPath()&&!ES(c.getPath(),"startsWith",!0,"/")){if(e)throw new Error("URI.parse: invalid URI (domain and path where path lacks leading slash): "+d);return!1}return!0};a.tryParse=function(b,c){var d=new a(null,c);return a.parse(d,b,!1,c)?d:null};a.isValid=function(b,c){return!!a.tryParse(b,c)};function a(b,c){c||g(0,2966),this.$9=c,this.$7="",this.$1="",this.$6="",this.$5="",this.$3="",this.$4=!1,this.$8={},this.$2=!1,a.parse(this,b,!0,c),this.$11=!1}var c=a.prototype;c.setProtocol=function(a){(i||(i=b("URISchemes"))).isAllowed(a)||g(0,11793,a);this.$7=a;return this};c.getProtocol=function(){return(this.$7||"").toLowerCase()};c.setSecure=function(a){return this.setProtocol(a?"https":"http")};c.isSecure=function(){return this.getProtocol()==="https"};c.setDomain=function(a){if(j.test(a))throw new Error("URI.setDomain: unsafe domain specified: "+a+" for url "+this.toString());this.$1=a;return this};c.getDomain=function(){return this.$1};c.setPort=function(a){this.$6=a;return this};c.getPort=function(){return this.$6};c.setPath=function(a){this.$5=a;return this};c.getPath=function(){return this.$5};c.addQueryData=function(a,b){Object.prototype.toString.call(a)===

[scnotes]

Day 6 - /dev/crash memory module

I find it annoying that even though we can read from /proc/kcore we will never be able to write to it. That’s why I decided to give /dev/crash a chance out of curiosity.

I was able to read from it through my sample program from the addendum of Day 5. The actual offset I got again from using gdb on crash:

crash> rd zfs_flags ffffffffc096d6e8: 0000000300000000 crash> sym zfs_flags ffffffffc096d6e8 (b) zfs_flags [zfs] Breakpoint 2, read_memory_device (fd=4, bufptr=0x7fffffffd558, cnt=8, addr=18446744072645695208, paddr=9138570984) at memory.c:2528 2528 if (pc->curcmd_flags & XEN_MACHINE_ADDR) (gdb) p/x 18446744072645695208 $1 = 0xffffffffc096d6e8 (gdb) p/x 9138570984 $2 = 0x220b386e8 // <---- the paddr was used for the lseek()

As for how we got the paddr, I just stepped down one frame:

#0 read_memory_device (fd=4, bufptr=0x7fffffffd558, cnt=8, addr=<optimized out>, paddr=9138570984) at memory.c:2537 #1 0x0000555555675121 in readmem (addr=18446744072645695208, memtype=1, buffer=<optimized out>, size=8, type=0x7fffffffd570 "64-bit KVADDR", error_handle=1) at memory.c:2254 #2 0x0000555555692e49 in display_memory (addr=<optimized out>, count=1, flag=1168, memtype=1, opt=<optimized out>) at memory.c:1570

the code:

2201 while (size > 0) { 2202 switch (memtype) 2203 { 2204 case UVADDR: 2205 if (!uvtop(CURRENT_CONTEXT(), addr, &paddr, 0)) { 2206 if (PRINT_ERROR_MESSAGE) 2207 error(INFO, INVALID_UVADDR, addr, type); 2208 goto readmem_error; 2209 } 2210 break; 2211 2212 case KVADDR: // <------ I think this is memtype == 1 2213 if (!kvtop(CURRENT_CONTEXT(), addr, &paddr, 0)) { 2214 if (PRINT_ERROR_MESSAGE) 2215 error(INFO, INVALID_KVADDR, addr, type); 2216 goto readmem_error; 2217 } 2218 break;

so that kvtop()[kernel virtual to physical] did the translation.

I was able to read zfs_flags from it:

$ sudo ./sample attempting to read from /dev/crash ... read 8 bytes - value: 300000000

Then I looked at the source code of /dev/crash and even though I could find code for reading memory I couldn't find any code for writing memory (which is weird because there is a function called write_memory_device in crash's code that seems to do that, but I'm not sure how it would plug in to the /dev/crash module if there is no write callback implemented:

static struct file_operations crash_fops = { .owner = THIS_MODULE, .llseek = crash_llseek, .read = crash_read, .unlocked_ioctl = crash_ioctl, .open = crash_open, .release = crash_release, };

Just for kicks I decided to change the module myself and add a write callback (it looked very similar to crash_read() but did the opposite):

static ssize_t crash_write(struct file *file, const char *buf, size_t count, loff_t *poff) { void *vaddr; struct page *page; u64 offset; ssize_t written; char *buffer = file->private_data; offset = *poff; if (offset >> PAGE_SHIFT != (offset+count-1) >> PAGE_SHIFT) return -EINVAL; vaddr = map_virtual(offset, &page); if (!vaddr) return -EFAULT; /* * Use bounce buffer to bypass the CONFIG_HARDENED_USERCOPY * kernel text restriction. */ if (copy_from_user(buffer, buf, count)) { unmap_virtual(page); return -EFAULT; } if (probe_kernel_write(buffer, vaddr, count)) { unmap_virtual(page); return -EFAULT; } unmap_virtual(page); written = count; *poff += written; return written; } ... static struct file_operations crash_fops = { .owner = THIS_MODULE, .llseek = crash_llseek, .read = crash_read, .write = crash_write, // <--- Makis in da house! .unlocked_ioctl = crash_ioctl, .open = crash_open, .release = crash_release, };

It actually compiled in the first try, which was surprising. So I went ahead and loaded that module and then changed my sample program to read zfs_flags then change one bit in it and then read it back. Sadly this is what I got back:

$ sudo ./sample attempting to read from /dev/crash ... read 8 bytes - value: 300000000 wrote 8 bytes - value: 310000000 read 8 bytes - value: 300000000

The value didn't change and I have no idea why. I decided to trace my new function in the crash module with bpftrace to see if it is even run and it seems like we do get there:

$ sudo trace-bpfcc '::crash_write "%x %x %d", arg1, arg2, arg3' PID TID COMM FUNC - 11483 11483 sample crash_write e2796300 7ee58 8

Then I decided to ensure that it returns what I expect (it should be 8 - written):

$ sudo trace-bpfcc 'r::crash_write "%d", retval' PID TID COMM FUNC - 12388 12388 sample crash_write 8

... OK OK OK ... while writing this I realized that I am an idiot .. I changed this simple thing in the code of my crash_write() function:

188 // if (probe_kernel_write(buffer, vaddr, count)) { 189 if (probe_kernel_write(vaddr, buffer, count)) {

and tried again:

$ sudo ./sample attempting to read from /dev/crash ... read 8 bytes - value: 300000000 wrote 8 bytes - value: 310000000 read 8 bytes - value: 310000000

Now we are talking!

So the next step would be to write a proper ReaderWriter interface for kernel memory around the crash module. Within that the main question would be to do a proper translation of virtual to physical addresses. Then once this is done, the next step would be to create an interface that given a namelist we return the address of that symbol in memory.

[benny-wang]

InnoDB行锁分析

前言

理解InnoDB行锁，分析一条SQL语句会加什么样的行锁，会锁住哪些数据范围对业务SQL设计和分析线上死锁问题都会有很大帮助。对于InnoDB的行锁，已经有多篇月报进行了介绍，这里笔者借鉴前面月报的内容，综合自己的理解，对源码的基础实现做一个介绍（会包含部分表锁介绍），然后结合具体SQL语句分析加锁类型和加锁范围。

InnoDB锁类型的表示

如在月报MySQL · 引擎特性 · Innodb 锁子系统浅析所述，在InnoDB内部用uint32类型数据表示锁的类型, 最低的 4 个 bit 表示 lock_mode, 5-8 bit 表示 lock_type(目前只用了 5 和 6 位，大小为 16 和 32 ，表示 LOCK_TABLE 和 LOCK_REC), 剩下的高位 bit 表示行锁的类型record_lock_type。下面会结合源码介绍lock_mode和record_lock_type。

lock mode

/* Basic lock modes */ enum lock_mode {    LOCK_IS = 0, /* intention shared */    LOCK_IX, /* intention exclusive */    LOCK_S, /* shared */    LOCK_X, /* exclusive */    LOCK_AUTO_INC, /* locks the auto-inc counter of a table            in an exclusive mode */    LOCK_NONE, /* this is used elsewhere to note consistent read */    LOCK_NUM = LOCK_NONE, /* number of lock modes */    LOCK_NONE_UNSET = 255 };

LOCK_IS/LOCK_IX

LOCK_IS: 表级锁，意向共享锁。表示将要在表上加共享锁。

LOCK_IX：表级锁，意向排他锁。表示是将要在表上加排他锁。

当对记录加LOCK_S或LOCK_X锁的时候，要确保在表上加了LOCK_IS或LOCK_IX锁。

lock_rec_lock( {  ...  ut_ad((LOCK_MODE_MASK & mode) != LOCK_S        || lock_table_has(thr_get_trx(thr), index->table, LOCK_IS));  ut_ad((LOCK_MODE_MASK & mode) != LOCK_X        || lock_table_has(thr_get_trx(thr), index->table, LOCK_IX));  ... }

LOCK_S

表共享锁、行共享锁

表共享锁用在:

ALTER语句第一阶段，当ALTER语句不能ONLINE执行的时间添加

storage/innobase/handler/handler0alter.cc prepare_inplace_alter_table_dict( {   if (ctx->online) {     error = DB_SUCCESS;   } else {     error = row_merge_lock_table(       ctx->prebuilt->trx, ctx->new_table, LOCK_S);     if (error != DB_SUCCESS) {       goto error_handling;     }   }     }

autocommit=0, innodb_table_locks=1时, lock table t read添加

storage/innobase/handler/ha_innodb.cc ha_innobase::external_lock( { /* Starting from 4.1.9, no InnoDB table lock is taken in LOCK TABLES if AUTOCOMMIT=1. It does not make much sense to acquire an InnoDB table lock if it is released immediately at the end of LOCK TABLES, and InnoDB's table locks in that case cause VERY easily deadlocks. We do not set InnoDB table locks if user has not explicitly requested a table lock. Note that thd_in_lock_tables(thd) can hold in some cases, e.g., at the start of a stored procedure call (SQLCOM_CALL). */ if (m_prebuilt->select_lock_type != LOCK_NONE) {   if (thd_sql_command(thd) == SQLCOM_LOCK_TABLES       && THDVAR(thd, table_locks)       && thd_test_options(thd, OPTION_NOT_AUTOCOMMIT)       && thd_in_lock_tables(thd)) {     dberr_t error = row_lock_table_for_mysql(       m_prebuilt, NULL, 0); }

行共享锁应用场景这里结合源码做一个介绍，后面在各种场景分析中也会涉及。

事务读在隔离级别为SERIALIZABLE时会给记录加 LOCK_S 锁。

mysql_lock_tables->lock_external->handler::ha_external_lock->ha_innobase::external_lock storage/innobase/handler/ha_innodb.cc ha_innobase::external_lock { if (trx->isolation_level == TRX_ISO_SERIALIZABLE     && m_prebuilt->select_lock_type == LOCK_NONE     && thd_test_options(       thd, OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)) {   /* To get serializable execution, we let InnoDB   conceptually add 'LOCK IN SHARE MODE' to all SELECTs   which otherwise would have been consistent reads. An   exception is consistent reads in the AUTOCOMMIT=1 mode:   we know that they are read-only transactions, and they   can be serialized also if performed as consistent   reads. */   m_prebuilt->select_lock_type = LOCK_S;   m_prebuilt->stored_select_lock_type = LOCK_S; } } ha_innobase::general_fetch->row_search_mvcc storage/innobase/row/row0sel.cc row_search_mvcc { err = sel_set_rec_lock(pcur,            rec, index, offsets,            prebuilt->select_lock_type,            lock_type, thr, &mtr); }

SELECT … IN SHARE MODE

mysql_lock_tables->get_lock_data->ha_innobase::store_lock file:storage/innobase/handler/ha_innodb.cc function:store_lock else if ((lock_type == TL_READ && in_lock_tables)       || (lock_type == TL_READ_HIGH_PRIORITY && in_lock_tables)       || lock_type == TL_READ_WITH_SHARED_LOCKS       || lock_type == TL_READ_NO_INSERT      || (lock_type != TL_IGNORE          && sql_command != SQLCOM_SELECT)) {   /* The OR cases above are in this order:   1) MySQL is doing LOCK TABLES ... READ LOCAL, or we    are processing a stored procedure or function, or   2) (we do not know when TL_READ_HIGH_PRIORITY is used), or   3) this is a SELECT ... IN SHARE MODE, or   4) we are doing a complex SQL statement like   INSERT INTO ... SELECT ... and the logical logging (MySQL   binlog) requires the use of a locking read, or   MySQL is doing LOCK TABLES ... READ.   5) we let InnoDB do locking reads for all SQL statements that   are not simple SELECTs; note that select_lock_type in this   case may get strengthened in ::external_lock() to LOCK_X.   Note that we MUST use a locking read in all data modifying   SQL statements, because otherwise the execution would not be   serializable, and also the results from the update could be   unexpected if an obsolete consistent read view would be   used. */   /* Use consistent read for checksum table */     if (sql_command == SQLCOM_CHECKSUM         || ((srv_locks_unsafe_for_binlog       || trx->isolation_level <= TRX_ISO_READ_COMMITTED)                  && trx->isolation_level != TRX_ISO_SERIALIZABLE        && (lock_type == TL_READ          || lock_type == TL_READ_NO_INSERT)        && (sql_command == SQLCOM_INSERT_SELECT          || sql_command == SQLCOM_REPLACE_SELECT          || sql_command == SQLCOM_UPDATE          || sql_command == SQLCOM_CREATE_TABLE))) {       //这里对于INSERT ... SELECT等语句如果没有UPDATE/IN SHARE MODE不需要当前读,       //所以不需要LOCK_S锁       /* If we either have innobase_locks_unsafe_for_binlog       option set or this session is using READ COMMITTED       isolation level and isolation level of the transaction       is not set to serializable and MySQL is doing       INSERT INTO...SELECT or REPLACE INTO...SELECT       or UPDATE ... = (SELECT ...) or CREATE  ...       SELECT... without FOR UPDATE or IN SHARE       MODE in select, then we use consistent read         for select. */       m_prebuilt->select_lock_type = LOCK_NONE;       m_prebuilt->stored_select_lock_type = LOCK_NONE;     } else {       m_prebuilt->select_lock_type = LOCK_S;       m_prebuilt->stored_select_lock_type = LOCK_S;     } }

普通insert语句遇到duplicate key。

普通INSERT语句如果没有duplicate key是不用加行锁的，当遇到duplicate key就需要加LOCK_S锁。

row_ins_duplicate_error_in_clust {   if (flags & BTR_NO_LOCKING_FLAG) {   /* Do nothing if no-locking is set */   err = DB_SUCCESS; } else if (trx->duplicates) {   /* If the SQL-query will update or replace   duplicate key we will take X-lock for   duplicates ( REPLACE, LOAD DATAFILE REPLACE,   INSERT ON DUPLICATE KEY UPDATE). */   err = row_ins_set_exclusive_rec_lock(     lock_type,     btr_cur_get_block(cursor),     rec, cursor->index, offsets, thr); //对于REPLACE、INSERT ON DUPLICATE KEY要更新的语句，加排他锁。 } else {   err = row_ins_set_shared_rec_lock(     lock_type,     btr_cur_get_block(cursor), rec,     cursor->index, offsets, thr); } }

外键检查���引用行时对引用行添加

row_ins_check_foreign_constraint { if (rec_get_deleted_flag(rec,      rec_offs_comp(offsets))) { err = row_ins_set_shared_rec_lock(   lock_type, block,   rec, check_index, offsets, thr); switch (err) { case DB_SUCCESS_LOCKED_REC: case DB_SUCCESS:   break; default:   goto end_scan; } } else { /* Found a matching record. Lock only a record because we can allow inserts into gaps */ err = row_ins_set_shared_rec_lock(   LOCK_REC_NOT_GAP, block,   rec, check_index, offsets, thr); } }

LOCK_X锁

表排他锁，行排他锁

表排他锁

ALTER语句最后一个阶段。

在源码注释中也解释了原因，用来确保没有别的事务在修改表定义的时候持有表锁。因为外键检查和crash recovery过程是仅InnoDB持有锁，所以这里无法仅靠Server层的Meta-Data Lock。

storage/innobase/handler/handler0alter.cc commit_inplace_alter_table() { for (inplace_alter_handler_ctx** pctx = ctx_array; *pctx; pctx++) {   ha_innobase_inplace_ctx*  ctx    = static_cast<ha_innobase_inplace_ctx*>(*pctx);   DBUG_ASSERT(ctx->prebuilt->trx == m_prebuilt->trx);   /* Exclusively lock the table, to ensure that no other   transaction is holding locks on the table while we   change the table definition. The MySQL meta-data lock   should normally guarantee that no conflicting locks   exist. However, FOREIGN KEY constraints checks and any   transactions collected during crash recovery could be   holding InnoDB locks only, not MySQL locks. */   error = row_merge_lock_table(     m_prebuilt->trx, ctx->old_table, LOCK_X);   if (error != DB_SUCCESS) {     my_error_innodb(       error, table_share->table_name.str, 0);     DBUG_RETURN(true);   } } }

autocommit=0, innodb_table_locks=1时, lock table t write语句添加

这里源码的位置和表级锁LOCK_S添加一致。

IMPORT/DISCARD TABLESPACE 语句的执行

storage/innobase/handler/ha_innodb.cc discard_or_import_tablespace() {   /* Obtain an exclusive lock on the table. */ dberr_t err = row_mysql_lock_table(   m_prebuilt->trx, dict_table, LOCK_X,   discard ? "setting table lock for DISCARD TABLESPACE"     : "setting table lock for IMPORT TABLESPACE"); }

行排他锁

UPDATE/DELETE需要阻止并发对同一行数据进行修改语句的执行

storage/innobase/handler/ha_innodb.cc ha_innobase::external_lock( if (lock_type == F_WRLCK) {   /* If this is a SELECT, then it is in UPDATE TABLE ...   or SELECT ... FOR UPDATE */   m_prebuilt->select_lock_type = LOCK_X;   m_prebuilt->stored_select_lock_type = LOCK_X; }

LOCK_AUTO_INC

表级锁，用来用来保护自增列的值，这里不再展开叙述，可以参考之前月报MySQL · 引擎特性 · InnoDB 事务锁系统简介。

lock_mode兼容性

 static const byte lock_compatibility_matrix[5][5] = {    /**         IS     IX       S     X       AI */    /* IS */ {  TRUE,  TRUE,  TRUE,  FALSE,  TRUE},    /* IX */ {  TRUE,  TRUE,  FALSE, FALSE,  TRUE},    /* S  */ {  TRUE,  FALSE, TRUE,  FALSE,  FALSE},    /* X  */ {  FALSE, FALSE, FALSE, FALSE,  FALSE},    /* AI */ {  TRUE,  TRUE,  FALSE, FALSE,  FALSE}  };

record_lock_type

LOCK_WAIT 256 表示正在等待锁

LOCK_ORDINARY 0 表示next-key lock ，锁住记录本身和记录之前的 gap，区别LOCK_GAP和LOCK_REC_NOT_GAP。

当用RR隔离级别的时候，为了防止当前读语句的幻读使用。

 storage/innobase/row/row0sel.cc  row_search_mvcc  if (set_also_gap_locks      && !(srv_locks_unsafe_for_binlog        || trx->isolation_level <= TRX_ISO_READ_COMMITTED)        && prebuilt->select_lock_type != LOCK_NONE        && !dict_index_is_spatial(index)) {   /* Try to place a lock on the index record */   /* If innodb_locks_unsafe_for_binlog option is used   or this session is using a READ COMMITTED isolation   level we do not lock gaps. Supremum record is really   a gap and therefore we do not set locks there. */   offsets = rec_get_offsets(rec, index, offsets,           ULINT_UNDEFINED, &heap);   err = sel_set_rec_lock(pcur,              rec, index, offsets,              prebuilt->select_lock_type,              LOCK_ORDINARY, thr, &mtr); ... /* We are ready to look at a possible new index entry in the result set: the cursor is now placed on a user record */ if (prebuilt->select_lock_type != LOCK_NONE) {   /* Try to place a lock on the index record; note that delete   marked records are a special case in a unique search. If there   is a non-delete marked record, then it is enough to lock its   existence with LOCK_REC_NOT_GAP. */   /* If innodb_locks_unsafe_for_binlog option is used   or this session is using a READ COMMITED isolation   level we lock only the record, i.e., next-key locking is   not used. */   ulint lock_type;   if (!set_also_gap_locks       || srv_locks_unsafe_for_binlog       || trx->isolation_level <= TRX_ISO_READ_COMMITTED       || (unique_search && !rec_get_deleted_flag(rec, comp))       || dict_index_is_spatial(index)) {     goto no_gap_lock;   } else {     lock_type = LOCK_ORDINARY;   }   ...   no_gap_lock:         lock_type = LOCK_REC_NOT_GAP;   }

从这里源码可以看到当参数innodb_locks_unsafe_for_binlog为ON时，只会对行加锁，不会锁范围。这个时候实际RR隔离级别对于当前读已经退化为RC隔离级别。

LOCK_GAP 512

只锁住索引记录之间或者第一条索引记录前或者最后一条索引记录之后的范围，并不锁住记录本身。

例如在RR隔离级别下，非唯一索引条件上的等值当前读，会在等值记录上加NEXT-KEY LOCK同时锁住行和前面范围的记录，同时会在后面一个值上加LOCK_GAP锁住下一个值前面的范围。下面的例子就会在索引i_c2上给c2 = 5上NEXT-KEY LOCK(LOCK_ORDINARY|LOCK_X)，同时给c2 = 10加上LOCK_GAP|LOCK_X锁。这里是因为非唯一索引，对同一个值可以多次插入，为确保当前读的可重复读，需要锁住前后的范围，确保不会有相同等值插入。

create table t1(c1 int primary key, c2 int, c3 int, index i_c2(c2)); insert into t1 values(1, 2, 3), (2, 5, 7), (3, 10, 9); set tx_isolation='repeatable-read'; select * from t1 where c2 = 5 for update;

源码: ha_innobase::index_next_same(读下一行)-> ha_innobase::general_fetch->row_search_mvcc storage/innobase/row/row0sel.cc row_search_mvcc /* fputs("Comparing rec and search tuple\n", stderr); */ if (0 != cmp_dtuple_rec(search_tuple, rec, offsets)) {    if (set_also_gap_locks       && !(srv_locks_unsafe_for_binlog      || trx->isolation_level      <= TRX_ISO_READ_COMMITTED)       && prebuilt->select_lock_type != LOCK_NONE       && !dict_index_is_spatial(index)) {     /* Try to place a gap lock on the index     record only if innodb_locks_unsafe_for_binlog     option is not set or this session is not     using a READ COMMITTED isolation level. */     err = sel_set_rec_lock(       pcur,       rec, index, offsets,       prebuilt->select_lock_type, LOCK_GAP,       thr, &mtr);

LOCK_REC_NOT_GAP 1024

仅锁住记录行，不锁范围。

RC隔离级别下的当前读大多是该方式。相关源码可以���LOCK_ORDINARY源码分析中no_gap_lock跳转。同时在上述例子中，RR隔离级别下，非唯一索引上的等值当前读，也会给主键上对应行加LOCK_X|LOCK_REC_NOT_GAP锁。

LOCK_INSERT_INTENTION 2048

插入意向锁，当插入索引记录的时候用来判断是否有其他事务的范围锁冲突，如果有就需要等待。

例如上面LOCK_GAP中的例子，如果此时另一个session执行insert into t1 values(11, 9, 0);就会调用lock_rec_insert_check_and_lock函数，用插入意向锁来检查是否需要等待。同时插入意向锁之间并不冲突，在一个GAP锁上可以有多个意向锁等待。

file:lock0lock.cc function:lock_rec_insert_check_and_lock /* If another transaction has an explicit lock request which locks the gap, waiting or granted, on the successor, the insert has to wait. An exception is the case where the lock by the another transaction is a gap type lock which it placed to wait for its turn to insert. We do not consider that kind of a lock conflicting with our insert. This eliminates an unnecessary deadlock which resulted when 2 transactions had to wait for their insert. Both had waiting gap type lock requests on the successor, which produced an unnecessary deadlock. */ const ulint type_mode = LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION; const lock_t* wait_for = lock_rec_other_has_conflicting(      type_mode, block, heap_no, trx);

LOCK_PREDICATE 8192

谓词锁 用于支持GIS索引

LOCK_PRDT_PAGE 16384

用在page上的谓词锁 用于支持GIS索引

LOCK_PREDICATE LOCK_PRDT_PAGE 用于支持GIS的锁，这里不做更多介绍，感兴趣的可以查看WL #6968， #WL 6609， #WL 6745。

事务隔离级别与行锁

快照读和当前读。

快照读使用MVCC读取数据记录某一个版本数据，不需要加锁。当前读读取最新数据，需要对记录或者某一个查询范围加锁。

InnoDB支持的隔离级别有：

Read Uncommited

可以读未提交记录

Read Committed (RC)

读取已提交数据。会存在幻读。

Repeatable Read (RR)

可重复读。当前读的时候，部分语句会加范围锁，保证当前读的可重复。

Serializable

可串行化。不存在快照读，所有读操作都会加锁。

对于普通的插入INSERT语句，在没有冲突key情况下，InnoDB并不会对记录上锁，所以这里不再分析简单插入的情况，只分析当前读需要加锁的语句。

分析使用的表Schema和数据如下：

create table t(c1 int primary key, c2 int, c3 int, c4 int, unique index i_c2(c2), index i_c3(c3)); insert into t values (10, 11, 12, 13), (20, 21, 22, 23), (30, 31, 32, 33), (40, 41, 42, 43);

Read-Uncommited/RC级别加锁分析

查询条件为主键等值

SELECT … WHERE PK = XX FOR UPDATE;

select * from t where c1 = 20 for update;

只需要在c1 = 20的主键记录上加X锁即可，加锁为LOCK_X|LOCK_REC_NOT_GAP。

SELECT … WHERE PK = XX LOCK IN SHARE MODE;

select * from t where c1 = 20 lock in share mode;

只需要在c1 = 20的主键记录上加S锁即可，加锁为LOCK_S|LOCK_REC_NOT_GAP。

UPDATE … WHERE PK = XX;

未更新索引列。

update t set c4 = 12 where c1 = 20;

只需要在c1 = 20的主键记录上加X锁即可，加锁为LOCK_X|LOCK_REC_NOT_GAP。

更新包括索引列。

update t set c2 = 12 where c1 = 20;

除了主键记录加X锁，还需要在c2的索引上加LOCK_X|LOCK_REC_NOT_GAP，同理c3索引列。

DELETE … WHERE PK = XX;

delete from t where c1 = 20;

对主键、各个索引对应的记录都要加X锁，LOCK_X|LOCK_REC_NOT_GAP。

查询条件为主键范围

对满足条件的行依次加上述等值分析中需要的锁。

例如：

select * from t where c1 >= 20 for update; 会分别对c1 in (20, 30, 40)加锁LOCK_X|LOCK_REC_NOT_GAP。 update t set c2 = c2 + 1 where c1 >= 20; 会分别对c1 in (20, 30, 40)依次对主键行加X锁，对应的索引行做加X锁操作。

查询条件为唯一索引等值

SELECT … WHERE UK = XX FOR UPDATE;

select * from t where c2 = 21 for update;

需要在c2 = 21的索引记录上加X锁:LOCK_X|LOCK_REC_NOT_GAP，同时还要在对应主键行上加X锁。

SELECT … WHERE UK = XX LOCK IN SHARE MODE;

select * from t where c2 = 21 lock in share mode;

需要在c2 = 21的索引记录上加S锁:LOCK_S|LOCK_REC_NOT_GAP，同时还要在对应主键行上加S锁。

UPDATE … WHERE UK = XX;

未更新其他索引列

update t set c4 = 12 where c2 = 21;

对唯一索引上数据加X锁（LOCK_X|LOCK_REC_NOT_GAP），然后对应的主键行也需要加X锁。

更新其他索引���

update t set c3 = 12 where c2 = 21;

依次对唯一索引数据、主键行、索引数据加X锁。

DELETE … WHERE UK = XX;

delete from t where c2 = 21;

会对唯一索引数据加X锁，根据唯一索引找到主键行后，会再依次对主键行、唯一索引、索引数据加X锁。

查询条件为唯一索引范围

SELECT … WHERE UK >= XX FOR UPDATE;

select * from t where c2 >= 21 for update;

这条语句执行的时候会对主键行c1 in (10, 20, 30, 40)依次加X锁， 同时在对c1=10加锁后，分析发现不满足条件会立即释放该行上的X锁。

file:sql/sql_executor.cc function:evaluate_join_record if (qep_tab->not_null_compl)  qep_tab->read_record.unlock_row(qep_tab); evaluate_join_record->rr_unlock_row->ha_innobase::unlock_row->row_unlock_for_mysql->lock_rec_unlock

Note:这里为什么没有对唯一索引加锁？上面的语句优化器判断走主键更优，就走了主键，只对主键加对应X锁。后面还会对选择不同路径的加锁区别做叙述。对于当前读的不同条件的查询，本质上我们都是在分析不同查询路径时加锁的不同。同时对于Read Uncommited和Read Committed隔离级别，会对不满足条件的行立即释放锁。

如果我们改为select * from t force index(i_c2) where c2 >= 21 for update;强制走唯一索引就会发现，引擎依次对满足条件的唯一索引、主键记录加X锁。

其他语句形式分析同上，这里不再赘述。

查询条件为非唯一索引

实际这里通过上面的分析，你也一定已经知道在<= RC隔离级别下非唯一索引的加锁情况。

SELECT … WHERE IDX = XX FOR UPDATE;

select * from t where c3 = 22 for update;

对c3 = 22的索引行加X锁，对主键行加X锁。

上面分析过不同路径对加锁的影响，如果这里执行select * from t force index (primary) where c3 = 22 for update;会是什么样的加锁、释放锁顺序呢？

实际非唯一索引情况与前面唯一索引情况加锁情况一致，这里不再展开叙述。

查询条件上无索引

先分析如下例子：

SELECT … WHERE COL = XX FOR UPDATE;

select * from t where c4 = 23 for update;

会依次对c1 in (10, 20, 30, 40)依次加X锁，分析是否满足条件，不满足即释放。为c1 = 10行加锁，不满足条件释放锁；c1=20加锁，满足条件，保留锁；c1=30加锁，不满足条件，释放；c1=40行加锁，不满足条件，释放。

其他语句情况类似，由于路径选择主键，会依次对主键行加锁，分析条件，不满足条件释放锁，满足条件持有锁，不再赘述。

多条件查询

当存在多个条件的时候，除了主键行上的锁，其他的加锁情况取决于选择的路径。如下例子：

select * from t where c2 = 21 and c3 = 22 for update;

这里选择了走唯一索引，就会对满足条件的唯一索引行加X锁，然后对主键行加X锁。

select * from t where c2 = 21 or c3 = 22 for update;

选择主键路径，就会对所有行一次加X锁，分析���件，最终持有主键上c1 = 20的X锁。

Repeatable-Read隔离级别加锁分析

查询条件为主键等值

SELECT … WHERE PK = XX FOR UPDATE;

select * from t where c1 = 20 for update;

由于主键具有唯一性，等值查询这里加锁与RC级别一致，对c1=20加X锁(LOCK_X|LOCK_REC_NOT_GAP)。

其他情况也与RC一致，只对满足条件的记录加锁。

查询条件为主键范围

SELECT … WHERE PK >= XX FOR UPDATE;

select * from t where c1 >= 20 for update;

这里会对c1=20加X锁(LOCK_X|LOCK_REC_NOT_GAP)，对c1=30, c1=40对应的行加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)，同时会对表示记录上界的’supremum’加exclusive next-key lock。这样做到阻塞其他事务对c1>=20的加锁操作。

SELECT … WHERE PK >= XX LOCK IN SHARE MODE;

select * from t where c1 >= 20 LOCK IN SHARE MODE;

这里会对c1=20加S锁(LOCK_S|LOCK_REC_NOT_GAP)，对c1=30, c1=40对应的行加share next-key lock(LOCK_S|LOCK_ORDINARY)，同时会对表示记录上界的’supremum’加share next-key lock。

UPDATE … WHERE PK >= XX;

未更新其他索引列。

update t set c4 = 1 where c1 >= 20;

加锁与上面SELECT… WHERE PK >= XX FOR UPDATE;一致。

更新包含索引列。

update t set c2 = c2 + 1 where c1 >= 20;

对主键c1=20加X锁，i_c2索引行加X锁，然后对c1=30,c1=40的主键行加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)，同时对应的i_c2索引行加X锁，最后对表示记录上界的’supremum’加exclusive next-key lock。

DELETE … WHERE PK >= XX;

会对c1=20加X锁，对c1=20对应的i_c2索引，i_c3索引加X锁，然后依次对c1=30, c1=40加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)，同时i_c2和i_c3对应的索引行加X锁，最后对’supremum’加LOCK_X|LOCK_ORDINARY。

查询条件为唯一索引等值

由于唯一索引中非NULL值具有唯一性，所以这里的加锁和RC会一致。但由于唯一索引可以有多个null值，对于col is null的条件加锁是不一样的。

SELECT … WHERE UK = XX FOR UPDATE;

select * from t where c2 = 21 for update;

这里与RR下主键等值加锁一致，对c2=21的值加X锁，对应主键行加X锁。

SELECT … WHERE UK IS NULL FOR UPDATE;

select * from t where c2 is null for update;

这里由于c2上没有为null值的record，所以这里对c2=11的record上加GAP LOCK(LOCK_X|LOCK_GAP)。

其他等值语句的执行与唯一索引等值在RC下一致。

如果再在table t中插入(50, null, 52, 53);为NULL的值，那么update t set c4 = 1 where c2 is null会对c2为NULL的行加NEXT-KEY LOCK(LOCK_X|LOCK_ORDINARY)，对应主键加X锁，并在c2=11上加GAP LOCK(LOCK_X|LOCK_GAP)。实际上唯一索引is null的加锁和非唯一索引等值加锁类似，后面会对非唯一索引情况做进一步描述。

查询条件为唯一索引范围

SELECT … WHERE UK >= XX FOR UPDATE;

select * from t where c2 >= 21 for update;

对于该语句执行，默认会选择主键路径，对c1 in (10, 20, 30, 40)分别加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)，同时对上界’supremum’加exclusive next-key lock，锁住全部数据范围。

select * from t force index(i_c2) where c2 >= 21 for update;

如果指定走i_c2索引，那么会对c2 in (21, 31, 41)分别加exclusive next-key lock，对应主键行加X锁，同时对i_c2上’supremum’ record加exclusive next-key lock。

UPDATE … WHERE UK >= XX;

未包含索引列。

update t force index (i_c2) set c4 = 1 where c2 >= 21;

等同上面指定走唯一索引的SELECT…FOR UPDATE语句加锁。

包含索引列。

update t force index (i_c2) set c3 = 1 where c2 >= 21;

除了上述语句的加锁外，还会对c1 in (10, 20, 30, 40)对应索引i_c3上的行加X锁。

DELETE … WHERE UK >= XX;

delete from t where c2 >= 41;

上述语句选择了i_c2索引，会对c2 = 41加exclusive next-key lock，对应主键行加X锁，i_c2，i_c3上数据行进行加X锁操作，对i_c2上’supremum’ record加exclusive next-key lock。

查询条件为非唯一索引等值

SELECT … WHERE INDEX = XX FOR UPDATE;

select * from t where c3 = 22 for update;

会对c3 =22在i_c3索引上加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)，对应主键加X锁(LOCK_X|LOCK_REC_NOT_GAP)，然后在下一条记录上加exclusive gap lock(LOCK_X|LOCK_GAP)。即该语句会锁定范围(11, 31)。

SELECT … WHERE INDEX = XX LOCK IN SHARE MODE;

加锁为：将上述FOR UPDATE语句的exclusive(LOCK_X)改为share(LOCK_S)。

UPDATE … WHERE INDEX = XX;

未包含索引列。

update t set c4 = 2 where c3 = 22;

加锁与上述FOR UPDATE一致。

包含索引列。

update t set c2 = 2 where c3 = 22;

除了上述锁，对c1 = 20对应的唯一索引(i_c2)行加X锁。

DELETE … WHERE INDEX = XX;

除了SELECT … WHERE INDEX = XX FOR UPDATE的锁，添加对唯一索引、索引做加X锁操作。

查询条件为非唯一索引范围

这里加锁与唯一索引的当前读范围查询一致，不在赘述。

Serializable 级别加锁分析

Serializable的加锁与RR隔离级别下一致，不同点是Serializable下普通SELECT语句查询也是当前读。例如下面语句：

select * from t where c1 = 20就会对c1=20的主键行加S锁(LOCK_S|LOCK_REC_NOT_GAP)。

总结

本文学习了InnoDB行锁相关源码，并对不同事务隔离级别下加锁进行了分析，对应知识点可以用于帮助分析SQL语句加锁情况。上面分析过程也可以发现，在RR隔离级别���Serializable隔离级别下，不同的路径选择不仅影响本语句执行效率，还会影响锁定的数据范围，严重影响并发。

[devsnews]

Arrow is a functional programming library for Kotlin designed to enhance the language with useful functional programming features such as type classes, data types, and more. It helps developers to write concise, type-safe, and easy-to-understand code. Arrow is also a great way to learn functional programming concepts, providing a concise and easy-to-use API to apply functional programming techniques. In this article, you can learn more about Arrow through a practical example of handling errors in functional way.