Geek Answers Handbook

Table / row locking mySQL

can someone explain the need to lock tables and / or rows in mysql?

I assume that to prevent multiple writing to the same field, is this the best practice?

+5
source share
4 answers

First, let's see a good document. This is not mysql related documentation, it is about postgreSQl, but it is one of the simpler and clearer documents I read on a transaction. You better understand the MySQl transaction after reading this link http://www.postgresql.org/docs/8.4/static/mvcc.html

When performing a transaction, 4 rules (ACID) apply:

  • Atomicity: all or nothing (rollback)
  • Coherence: coherent before, sequential after
  • : ?
  • : , ,

, , . . , phantom -reads . , Row, , , (, , ), . ...

, , , , ( while).

Edit: ------------

InnoDb , . , , , , .

MySQL 3 4 , PosgtreSQL ( postgreSQL 2 ). . , Dirty reads, Non-repeatable reads. -, , , LOCK (, ).

. MySQL, 5.0, , , 2, , "FOR UPDATE"!

, , 4- ( LOCK IN SHARE MODE ), , . , , . , : http://www.postgresql.org/docs/8.4/static/transaction-iso.html#MVCC-SERIALIZABILITY

:

  • , , INSIDE ( , BEGIN)
  • , ( -)
  • ( ), ( , MySQL)
  • (LOCK IN SHARE MODE), , , ( , , )
+3

. MySQL . .

, , , - , , , .

, isolation .

+2

, -, - , , , .

-, .

  • select max(invoice_id)+1 from invoice, , - ( -) - -, re .

  • , "auto_increment", ( , , "" ),

, (a) (b) select max(invoice_id)+1 from invoice (c) (d) commit + .

, MySQL InnoDB . lock table , .

, .

+1

, / .

, , , , .

0

More articles:


All Articles