mysql - Why is there a deadlock with these two queries? -


isolation level read committed

the 1 query looking records service_id 0 ... other looking service_id not in (0, ... others ... );

i'd think locking different rows?

    ------------------------     latest detected deadlock     ------------------------     2017-08-18 09:01:24 7f2d05641700     *** (1) transaction:     transaction 201694975, active 1 sec starting index read     mysql tables in use 2, locked 2     lock wait 46 lock struct(s), heap size 6544, 194 row lock(s)     mysql thread id 33600289, os thread handle 0x7f2d0812b700, query id 3703173090 inf-rtpctllb02-prd.rtp.netapp.com 10.60.56.150 ctl copying tmp table     select                         re.*,                         r.config_id,                         r.reserve_all_or_nothing,                         r.owner,                         r.charges                                             `job_charge` re,                         `job` r                         re.job_id = r.id , ((re.status ='dispatched') or (re.status= 'running') or (re.status= 'held') or (re.status= 'reserved')) ,  ((re.service_id ='0')) , r.disable = 0  order r.priority,r.id limit 10000  update     *** (1) waiting lock granted:     record locks space id 1485 page no 987 n bits 104 index `primary` of table `ctl`.`job_charge` trx table locks 2 total table locks 2  trx id 201694975 lock_mode x locks rec not gap waiting lock hold time 1 wait time before grant 0      *** (2) transaction:     transaction 201691925, active 185 sec fetching rows     mysql tables in use 4, locked 2     1164 lock struct(s), heap size 128552, 2 row lock(s)     mysql thread id 33599597, os thread handle 0x7f2d05641700, query id 3703158120 inf-rtpctllb02-prd.rtp.netapp.com 10.60.56.150 ctl updating     update                 `job_charge`             set                 service_id = '0'                              service_id not in ('0','ctl5-staging_command-launcher.674d8c96-7c76-11e7-bc6c-ee0cf095fd00','inf-mesos-slave001.ctl.gdl.englab.netapp.com:mesos-6b256982-4ef1-4a84-ba60-58245ee7406d-s63.3987fd54-ee31-4c81-add4-4be53a6ed363:80','ctl5-staging_scheduler.912d008f-7c76-11e7-bc6c-ee0cf095fd00','ctl5-production_capacity-manager.6a869ee7-7919-11e7-bc6c-ee0cf095fd00','ctl5-production_scheduler.91de7d76-7919-11e7-bc6c-ee0cf095fd00','mysql','inf-mesos-slave001.ctl.gdl.englab.netapp.com:mesos-6b256982-4ef1-4a84-ba60-58245ee7406d-s63.48fe0555-83e9-4811-bcbc-f301da498fa6:80','ctl5-production_cleaner.6a86c5fa-7919-11e7-bc6c-ee0cf095fd00','ctl5-production_command-launcher.9f97a534-8413-11e7-bc6c-ee0cf095fd00','ctl5-production_reservation-manager.7ac1771d-7a9e-11e7-bc6c-ee0cf095fd00','ctl5-s     *** (2) holds lock(s):     record locks space id 1485 page no 987 n bits 104 index `primary` of table `ctl`.`job_charge` trx table locks 1 total table locks 2  trx id 201691925 lock_mode x locks rec not gap lock hold time 13 wait time before grant 12      *** (2) waiting lock granted:     record locks space id 1485 page no 1606 n bits 88 index `primary` of table `ctl`.`job_charge` trx table locks 1 total table locks 2  trx id 201691925 lock_mode x locks rec not gap waiting lock hold time 0 wait time before grant 0      *** roll transaction (1)

yes. both must different rows. can see page no 987 , 1606 indicated in statement

here transaction 1 is

select                   re.*,                   r.config_id,                   r.reserve_all_or_nothing,                   r.owner,                   r.charges                                 `job_charge` re,                   `job` r                   re.job_id = r.id , ((re.status ='dispatched') or (re.status= 'running') or (re.status= 'held') or (re.status= 'reserved')) ,  ((re.service_id ='0')) , r.disable = 0  order r.priority,r.id limit 10000  update

transaction 2 

update           `job_charge`       set           service_id = '0'                  service_id not in ('0','ctl5-staging_command-launcher.674d8c96-7c76-11e7-bc6c-ee0cf095fd00','inf-mesos-slave001.ctl.gdl.englab.netapp.com:mesos-6b256982-4ef1-4a84-ba60-58245ee7406d-s63.3987fd54-ee31-4c81-add4-4be53a6ed363:80','ctl5-staging_scheduler.912d008f-7c76-11e7-bc6c-ee0cf095fd00','ctl5-production_capacity-manager.6a869ee7-7919-11e7-bc6c-ee0cf095fd00','ctl5-production_scheduler.91de7d76-7919-11e7-bc6c-ee0cf095fd00','mysql','inf-mesos-slave001.ctl.gdl.englab.netapp.com:mesos-6b256982-4ef1-4a84-ba60-58245ee7406d-s63.48fe0555-83e9-4811-bcbc-f301da498fa6:80','ctl5-production_cleaner.6a86c5fa-7919-11e7-bc6c-ee0cf095fd00','ctl5-production_command-launcher.9f97a534-8413-11e7-bc6c-ee0cf095fd00','ctl5-production_reservation-manager.7ac1771d-7a9e-11e7-bc6c-ee0cf095fd00','ctl5-s

from given message, can see transaction 1 waiting exclusive lock (denoted x lock, lock needed writing value table) on table 'ctl' primary key.

but @ same time, transaction 2 came picture holding x lock on 'ctl' table primary key (page no 987). so, transaction 2 having x lock on 'ctl', transaction 1 can't x lock , hence waiting.

but transaction 2 waiting x lock on 'ctl' (different row above, page no 1606). think row being held transaction 1.

so, transaction 1 holding lock on row in page no 1606 transaction 2 waiting , transaction 2 holding lock on row in page no 987 transaction 1 waiting

so, both waiting each other , hence deadlock occurred.


Comments

Post a Comment

Popular posts from this blog

Is there a better way to structure post methods in Class Based Views -

i'm using class based views in django , never specify url in ajax calls , omit action parameter in form because know these requests go through post method of class based view. notice view - profile page make many requests depending on user updating on page - post method become convoluted , if/else mess example: def post(self, request, *args, **kwargs): # handle user changing available date if request.post.get('availabledate') != none: self.updateavailabledate(request) return jsonresponse({'result':'success'}) if request.post.get('newprojectname') != none: creative_user = creativeuserprofile.objects.get(id = request.user.id) project = project.create_project(creative_user, request.post['newprojectname']) project.save() return jsonresponse({'projectid': project.id }) if request.post.get('projectimage') != none , request.files['file']: proj...
Read more

performance - Why is XCHG reg, reg a 3 micro-op instruction on modern Intel architectures? -

i'm doing micro-optimization on performance critical part of code , came across sequence of instructions (in at&t syntax): add %rax, %rbx mov %rdx, %rax mov %rbx, %rdx i thought had use case xchg allow me shave instruction , write: add %rbx, %rax xchg %rax, %rdx however, dimay found agner fog's instruction tables , xchg 3 micro-op instruction 2 cycle latency on sandy bridge, ivy bridge, broadwell, haswell , skylake. 3 whole micro-ops , 2 cycles of latency! 3 micro-ops throws off 4-1-1-1 cadence , 2 cycle latency makes worse original in best case since last 2 instructions in original might execute in parallel. now... cpu might breaking instruction micro-ops equivalent to: mov %rax, %tmp mov %rdx, %rax mov %tmp, %rdx where tmp anonymous internal register , suppose last 2 micro-ops run in parallel latency 2 cycles. given register renaming occurs on these micro-architectures, though, doesn't make sense me done way. why wouldn't register renamer ...
Read more

c# - Asp.net web api : redirect unauthorized requst to forbidden page -

im trying redirect unauthorized request forbidden page instead i'm getting forbidden page in response body , how can fix ? here's startup class : app.createperowincontext(storecontext.create); app.createperowincontext<applicationusermanager>(applicationusermanager.create); app.createperowincontext<applicationsigninmanager>(applicationsigninmanager.create); app.usecookieauthentication(new cookieauthenticationoptions { authenticationtype = defaultauthenticationtypes.applicationcookie, expiretimespan = timespan.fromdays(30), }); app.useexternalsignincookie(defaultauthenticationtypes.externalcookie); app.usetwofactorsignincookie(defaultauthenticationtypes.twofactorcookie, timespan.fromminutes(5)); app.usetwofactorrememberbrowsercookie(defaultauthenticationtypes.twofactorrememberbrowsercookie); the method im trying reach : [httpget] [authorize(roles = "admin")] public string getcurrentusername() { return userman...
Read more