Geek asks and answers

When does sql block only a row in an update statement?

Can race conditions occur in sql under these conditions?

If this SQL update runs on a single thread, call it: 1:

Update items Set flag = B where Flag = A;

And this SQL update, launched in another, invokes its statement 2:

Update items Set flag = C where Flag = A;

Is it possible for each thread to read the same record, where the flag is A and write the record with its values? Such that expression 1 can write it first, and then expression 2 writes it or vice versa?

The answer to this question depends on when the database only blocks the update. Does this happen before he finds records or after he finds records and evaluates the where clause?

+5
source share
4 answers

First, there are three locking contexts:

  • Database Level Lock
  • Table level lock
  • Row level lock

Then you have four lock modes:

  • IX
  • IS
  • X
  • S

IX and IS locks are intent locks. These locks are held before acquiring other types of locks. Lock X is a lock (write), and lock S is a general (read) lock.

(IX, IS, X X) . , X . , SQLlite. S , X . Writes S S X , . .

MySQL . MyISAM X S () . X S X- . X , S-. , LOW_PRIORITY_UPDATES, , - .

MySQL X , "FLUSH TABLES WITH READ LOCK".

InnoDB , . InnoDB . InnoDB , "gap", REPEATABLE-READ. , , UPDATE, . , InnoDB S SELECT. , .

X . :

>connection 1
start transaction;
update T set c = c + 1 order by id asc;

>connection 2
start transaction;
update T set c = c - 1 order by id desc;

. , , , . . InnoDB , . MyISAM , , .

, , X ( , ). , , , X. X. , . , , X. , DELETE UPDATE, , .

UPDATE X, UPDATE X , .

+4

, , INSERT, UPDATE DELETE.

, .

.

, , . , ..

, SQL, .

http://msdn.microsoft.com/en-us/library/aa213026(v=sql.80).aspx

0

, . SQL- , , . , , . , , .

SQL Server ACID, , - . (, ). , , SQL Server , / .

SQL Server

EDIT: ACID. ACID -

0

SQL , . "" , , , , . ​​ .

, , sone , - .

, , where , , . . .

, .

0
source

More articles:


All Articles