w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
MySQL duplicate key error causes a shared lock set on the duplicate index record?

.1. It requires a lock on the existing entry so that subsequent attempts to insert a duplicate record fail consistently:

-- Transaction A
INSERT INTO mytable VALUE(1); -- fails as "duplicate"

-- Transaction B
DELETE FROM mytable WHERE field = 1; -- must be put on hold, see below

-- Transaction A
-- transaction is still in progress
INSERT INTO mytable VALUE(1); -- must fail to stay consistent with the
previous attempt

.2. Yes, and yes:

The intention locking protocol is as follows:

  • Before a transaction can acquire an S lock on a row in table t, it must first acquire an IS or stronger lock on t.
  • Before a transaction can acquire an X lock on a row, it must first acquire an IX lock on t.

© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.