JDBC (2)

CREATE TABLE Account (
ID int primary KEY auto_increment,
Name varchar (20),
Money double
);
Insert into account values (NULL, ' a ', +), (null, ' B ', 1000);

First, the business
The concept of a transaction: a transaction is a logical set of operations that either completes at the same time or is not completed at the same time.

Management of transactions: By default, the database automatically manages transactions, which is managed by a single statement that is exclusive of a transaction.
If you need to control the transaction yourself, you can also open/commit/rollback the transaction with the following command
Start transaction;
Commit
Rollback

Managing Transactions in JDBC:
Conn.setautocommit (FALSE);
Conn.commit ();
Conn.rollback ();
SavePoint sp = Conn.setsavepoint ();
Conn.rollback (SP);

Four characteristics of a transaction: the most basic feature of a transaction, a well-designed database can help us ensure that transactions have these four characteristics (ACID)
Atomicity: atomicity means that a transaction is an inseparable unit of work, and the operations in the transaction either occur or do not occur.
Consistency: If the database is a state of integrity before the transaction is executed, the database remains an integrity state after the transaction finishes regardless of whether the transaction succeeds.
Integrity State of the database: when all the data in a database conforms to all the constraints defined in the database, it can be said that the database is an integrity state.
Isolation: Transaction isolation is when multiple users concurrently access the database, the transaction of one user cannot be disturbed by other users ' transactions, and data between multiple concurrent transactions is isolated from each other.
Persistence: Persistence refers to the fact that once a transaction is committed, it changes the data in the database to be permanent, and then it should not have any effect even if the database fails.


Isolation:
The design of the database as a single-threaded database prevents all thread-safety problems and naturally ensures isolation. But if the database is designed like this, then efficiency can be extremely low.

Locking mechanisms in the database:
Shared locks: Queries that do not have any locks at the non-serializable isolation level, and queries that are made under the Serializable isolation level, share locks,
Shared locks feature: shared locks and shared locks can coexist, but shared and exclusive locks cannot coexist
Exclusive locks: Add and subtract at all isolation levels, plus an exclusive lock.
Exclusive lock Features: cannot coexist with any other lock

If two threads are modified concurrently, they will be disruptive to each other, and the lock mechanism must be used to prevent concurrent modification of multiple threads.
If two threads are queried concurrently, there is no thread safety issue

If two threads a modification, a query ...
Four isolation levels:
READ UNCOMMITTED--does not prevent any isolation problems, with dirty read/non-repeatable/virtual Read (phantom reading) issues
Read COMMITTED-prevents dirty reads, but does not prevent non-repetition/virtual reading (phantom reading) problems
REPEATABLE READ-prevents dirty read/non-repeatable read problems, but does not prevent virtual read (phantom reading) problems
Serializable-database is designed as a single-threaded database to prevent all of the above issues

Security considerations: Serializable>repeatable read>read Committed>read Uncommitted
Efficiency considerations: Read Uncommitted>read committed>repeatable read>serializable

When you really use data, based on the requirements of your own database, analyze security and efficiency requirements, and select an isolation level to run the database at this isolation level.
MySQL By default is the REPEATABLE read isolation level
Oracle By default is the Read Committed isolation level

Query the isolation level of the current database: SELECT @ @tx_isolation;
Set isolation levels: Set [Global/session] transaction ISOLATION level xxxx; If you do not write by default the session refers to the isolation levels when you modify the current client and database interactions, and if you use Golbal, The default isolation level for the database is modified


Dirty reads: One transaction reads uncommitted data to another transaction
A 1000
B 1000

----------
A:
Start transaction;
Update account set money=money-100 where name=a;
Update account set money=money+100 where name=b;
----------
B:
Start transaction;
SELECT * from Account;

a:900
b:1100
----------
A:
Rollback
----------
B:
Start transaction;
select* from Account;
a:1000
b:1000



Non-REPEATABLE READ: Reads a row of data from a table within a transaction, with multiple read results---row level issues
a:1000 1000 1000
B: Bank clerk

---------
B:start transaction;
Select demand deposit from the account where name= ' a '; ----Demand Deposit: 1000
Select term deposit from the account where name= ' a '; ----Term deposits: 1000
Select fixed assets from account where name= ' a '; ----Fixed Assets: 1000
-------
A:
Start transaction;
Update Accounset Set current = current -1000 where name= ' a ';
Commit
-------
Select Current + regular + fixed from account where name= ' a '; Total Assets---: 2000
Commit
----------

Virtual Read (Phantom Read): Refers to the data that is inserted into a transaction that reads from another transaction, causing inconsistencies---table-level problems
a:1000
b:1000
D: Bank business personnel

-----------
D:
Start transaction;
Select SUM (Money) from account; ---2000 USD
Select count (name) from account; ---of 2

------
C:
Start transaction;
Insert into account values (c,4000);
Commit
------

Select SUM (Money)/count (name) from account; ---Average: 2000 RMB/each
Commit
------------


Update loss issues:
Two threads are modified based on the same query result, and the modified person overwrites the modification first.

Pessimistic lock: Pessimistic lock pessimistic thinking that each operation will cause update loss problem, in each query with an exclusive lock

Optimistic Lock: Optimistic lock will be optimistic that each query will not cause the loss of updates. Control with one version field


Very many queries, very few modifications, use optimistic lock
Very many changes, very few queries, use pessimistic lock

=================================================================================
Second, database connection pool

Handwritten connection pool:
The Close method of transforming Conn

Inherited
Decoration
! Dynamic Agent

Open source data sources:
DBCP:
Mode 1:

New Basicdatasource (); Source.setdriverclassname ("com.mysql.jdbc.Driver"); Source.seturl ("jdbc:mysql:///day11"); Source.setusername (" Root " ); Source.setpassword ("root");

Mode 2:

New Properties ();p rop.load (new FileReader ("dbcp.properties"  New  = Factory.createdatasource (prop);

In the configuration file:

Driverclassname=com.mysql.jdbc.Driverurl=jdbc:mysql://day11username=  Rootpassword=root

#<!--Initialize the connection--
initialsize=10

#最大连接数量
Maxactive=50

#<!--Maximum idle connection--
Maxidle=20

#<!--Minimum idle connection--
Minidle=5

#<!--Timeout Wait time in milliseconds of 6000 milliseconds/1000 equals 60 seconds--
maxwait=60000

C3P0 Data Source:
Mode 1:

New Combopooleddatasource (); Source.setdriverclass ("com.mysql.jdbc.Driver") ; Source.setjdbcurl ("jdbc:mysql:///day11"); Source.setuser (" Root " ); Source.setpassword ("root");

Mode 2:

New Combopooleddatasource ();

In the class load directory, configure the name C3p0-config.xml in the configuration file:

<c3p0-config><default-config><property name="driverclass" >com.mysql.jdbc.driver</property><property name="jdbcurl">jdbc:mysql: /// Day11 </property><property name="user">root</property> <property name="password">root</property></default- Config></c3p0-config>

Tomcat built-in data source (DBCP):
To. How to configure a data source for Tomcat
~tomcat/conf/context.xml file Configuration <Context> configuration information in this location will be shared by all Web apps
The ~tomcat/conf/[engin]/[host]/context.xml file can be configured with <Context> tags, and the information configured here will be shared by all Web apps in this virtual host

The configuration <Context> tag in the <Host> tab in the ~tomcat/conf/server.xml file is the first configuration of the Web app, and the information configured in this tab will only work for the current web App
~tomcat/conf/[engin]/[host]/create an. xml file yourself, use the <Context> tag in this file to configure a Web application, which is the second configuration of the Web application, in this <Context> The information configured in the tag will only work for the current web App
The ~web app also has a third configuration: Place the Web app directly into the directory managed by the virtual host. You can create a context.xml file under the Meta-inf folder of your Web app, where you can write <Context> tag to configure it. This configuration information will only work for the current web App

<resource name="MySource"Auth="Container"type="Javax.sql.DataSource"username="Root"Password="Root"Driverclassname="Com.mysql.jdbc.Driver"URL="Jdbc:mysql:///day11"maxactive="8"Maxidle="4"/>

If you get this data source in your program
To access Jndi, you must be in the servlet to execute the following code:

Context initctx = new InitialContext ();
Context Jndi = (context) initctx.lookup ("java:comp/env");
DataSource Source = Jndi.lookup ("MySource");

JDBC (2)