Mysql transaction

There's two ways for MYSQL transaction.
1、Use begin,rollback,commit
begin start a transaction
rollback transaction rollback
commit transaction commit
2、Set mysql ini autocommit

In default mysql will commit automatically. It means when you commit a query ,it will excute it directly

set autocommit=0 forbid commit automatically
set autocommit=1 allow commit automatically
Note : In mysql only INNODB and BDB engine support transaction (or other new storage engineer too).

 

Chang type of mysql engine
table table_name type=InnoDB;
alter table table_name engine=InnoDB;

Transaction example

BEGIN;

SELECT book_number FROM book WHERE  book_id = 123 for update;

// ...

UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;

COMMIT;

 

START TRANSACTION;
SELECT balance FROM checking WHERE customer_id = 10233276;
UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
UPDATE savings  SET balance = balance + 200.00 WHERE customer_id = 10233276;
COMMIT;

Some sql engine don't support transaction we can use table lock
support MyISAM & InnoDB ,

LOCK TABLES `user` WRITE;
INSERT INTO `user` (`id`, `username`, `sex`) VALUES (NULL, 'test1', '0');
UNLOCK TABLES;